The below code shows how a repeating structure can be implemented using a PL/SQL object type;
CREATE OR REPLACE TYPE ITEM
NAME VARCHAR2(255) ,
VALUE VARCHAR2(255) );
CREATE OR REPLACE
TYPE ITEM_ARR AS TABLE OF ITEM
The PL/SQL type ITEM_ARR contains an array of PL/SQL objects; ITEM. The object ITEM contains two fields; NAME and VALUE.
To illustrate the usage of the ITEM_ARR type, a sequence, table, trigger are created and a small TAPI (table API) package. The trigger is optional for this example.
CREATE OR REPLACE TRIGGER ITEMS_TAB_BI
BEFORE INSERT ON ITEMS_TAB
FOR EACH ROW
IF (:NEW.ID IS NULL) THEN
SELECT ITEMS_TAB_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
CREATE OR REPLACE
PACKAGE BODY ITEMS_TAB_UTILS AS
PROCEDURE ADD_ITEMS(P_ITEMS ITEM_ARR) AS
FORALL x in P_ITEMS.First..P_ITEMS.Last
INSERT INTO ITEMS_TAB (ID,NAME,VALUE) VALUES (ITEMS_TAB_SEQ.NEXTVAL,P_ITEMS(x).NAME,P_ITEMS(x).VALUE);
To use an object type from another schema, the connecting schema needs to have execute permission on the object type.
In BPEL the ADD_ITEMS procedure can be called. This procedure will bulk insert all received items in one action. From BPEL, the DbAdapter only has to be called once. This is a good thing considering calling the DbAdapter is relatively expensive. In BPEL only one simple transformation is required and no ForEach or While activities have to be used.
I've used the message type of the itemCollection (see also http://javaoraclesoa.blogspot.nl/2012/03/loops-in-bpel-11-and-20.html). An itemCollection is as the name might suggest, an array of items. An item contains a name and a value. The below image shows the used transformation from the input message to the message used to call the DbAdapter. As can be seen, the repeating elements are present in the target message.
When calling the service from the webservice test page in the Enterprise Manager, it looks as followed;
The result from this call is shown below;
The code can be downloaded here; https://dl.dropbox.com/u/6693935/blog/ItemUtils.zip
PL/SQL object types can be used to get repeated or nested structures to the database in an easy and performing way. There are some things to mind though when using PL/SQL object types.
The datamodel is defined in XML schema's in the BPEL process and also in the database definition of the object type. When adding a field or changing the order of fields, the database code and the BPEL code need changing. You won't get errors in every situation when you don't do it right, since object type fields are passed in order and not by name. This is a thing to mind when making changes.
Object types in tables and queues
Object types can be used as a type in a database table or a queue message type. My suggestion is not to use object types this way. The below error message says much.
ORA-02303: cannot drop or replace a type with type or table dependents
02303. 00000 - "cannot drop or replace a type with type or table dependents"
*Cause: An attempt was made to drop or replace a type that has dependents.
There could be a substitutable column of a supertype of the type
*Action: Drop all type(s) and table(s) depending on the type, then retry
the operation using the VALIDATE option, or use the FORCE option.
If the object type changes, you will have to consider migration strategies of existing tables and queues. This can be troublesome. Sometimes tables need to be dropped and recreated because an alter type might not be sufficient to achieve the desired change. In a production environment, if the PL/SQL code has not been written in a modular way, making sure the queue is empty and will remain empty during the installation, can be a bother.
Also when a complex datamodel is implemented using PL/SQL object types, the dependencies between the types can become difficult to manage in case of changes. This is illustrated in my example by the following; I can't CREATE OR REPLACE the ITEM type because it is used in the ITEM_ARR type.
Object types can be used to increase performance and decrease complexity, however changes are more difficult to implement. My suggestion on this is to only use them in the following cases;
- when using them allows you to avoid complex BPEL code
- when performance is important and you want to minimize DbAdapter calls and transformations
- when changes to the data structure implemented in PL/SQL object types, are rare
Don't use them as a column in a table or queue. You are more flexible by using other methods such as using primitive types and spreading the data structure over several tables or by using XMLType or LOB data types.