4 Oracle Application Server Adapter for Databases

Relational Types to XML Schema Types

Table 4-4 shows how database datatypes are converted to XML primitive types when you import tables from a database.

Essentially, NUMBER goes to DECIMAL, the most versatile XML datatype for numbers, VARCHAR2 and CLOB to string, BLOB to base64Binary (to meet the plain-text requirement), and date types to dateTime.

Any type not mentioned in this discussion defaults to java.lang.String and xs:string. Timestamp support is basic, because only the xs:dateTime format is supported. The BFILE, USER DEFINED, OBJECT, STRUCT, VARRAY, and REF types are specifically not supported.

Because XML is plain text, BLOB and byte values are base 64/MIME encoded so that they can be passed as character data.

Mapping Any Relational Schema to Any XML Schema

The database adapter supports mapping any relational schema on any relational database to an XML schema, although not any XML schema of your choice, because the wizard generates the XML schema with no explicit user control over the layout of the elements. You can control how you map the schema in both the Adapter Configuration Wizard and later in OracleAS TopLink Mapping Workbench. By pairing the database adapter with a transformation step, you can map any relational schema to any XML schema.

DML Operations

Data manipulation language (DML) operations align with basic SQL INSERT, UPDATE, and SELECT operations. SQL INSERT, UPDATE, DELETE, and SELECT are all mapped to Web service operations of the same name. The WRITE is either an INSERT or UPDATE, based on the results of an existence check. A distinction is made between the data manipulation operations—called outbound writes—and the SELECT operations—called outbound reads. The connection between the Web service and the SQL for merge (the default for outbound write) and queryByExample are not as obvious as for basic SQL INSERT, UPDATE, and SELECT.

Input xmlRecord:
<Employee>
      <id/>
      <lastName>Smith</lastName>
</Employee>

<EmployeeCollection>
      <Employee>
         <id>5</id>
         <lastName>Smith</lastName>
         ....
      </Employee>
      <Employee>
         <id>456</id>
         <lastName>Smith</lastName>
         ....
      </Employee>
</EmployeeCollection>

Oracle_Home\integration\orabpel\samples\tutorials\122.DBAdapter

Polling Strategies

The inbound receive allows you to listen to and detect events and changes in the database, which in turn can be the initiators of a business process. This is not a one-time action, but rather an activation. A polling thread is started, which polls a database table for new rows or events.

Whenever a new row is inserted into the MOVIES table, the polling operation raises it to Oracle BPEL Process Manager. The stratagem is to poll every record once. The initial SELECT has to be repeated over time, to receive the rows that exist at the start and all new rows as they are inserted over time. However, a new row once read is not likely to be deleted, and therefore can possibly be read repeatedly with each polling.

The various ways to poll for events—called polling strategies, also known as after-read strategies or publish strategies—range from simple and intrusive to sophisticated and nonintrusive. Each strategy employs a different solution for the problem of what to do after reading a row or event so as not to pick it up again in the next polling interval. The simplest (and most intrusive) solution is to delete the row so that you do not query it again.

This section discusses the following polling strategies and factors to help you determine which strategy to employ for a particular situation:

• Physical Delete

• Logical Delete

• Sequencing Table: Last-Read Id

• Sequencing Table: Last Updated

• Control Tables

<database-mapping>
   <attribute-name>orders</attribute-name>
   <reference-class>taxonomy.Order</reference-class>
   <is-private-owned>true</is-private-owned>

<operation name="receive">
   <jca:operation
      ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
         …      PollingStrategyName="DeletePollingStrategy"
      DeleteDetailRows="true"

<operation name="receive">
   <jca:operation
      ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
         …
      PollingStrategyName="LogicalDeletePollingStrategy"
      MarkReadField="STATUS"
      MarkReadValue="PROCESSED"

AND (STATUS IS NULL) OR (STATUS <> 'PROCESSED')

create trigger Employee_modified
before update on Employee
for each row
begin
   :new.STATUS := 'MODIFIED';
end;

<operation name="receive">
   <jca:operation
      ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
         …
      PollingStrategyName="LogicalDeletePollingStrategy"
      MarkReadField="STATUS"
      MarkUnreadValue="UNPROCESSED"
      MarkReservedValue="RESERVED-1"
      MarkReadValue="PROCESSED"

Update EMPLOYE set STATUS = 'RESERVED-1' where (CRITERIA) AND (STATUS = 'UNPROCESSED');

Select … from EMPLOYEE where (CRITERIA) AND (STATUS = 'RESERVED-1');

Update EMPLOYEE set STATUS = 'PROCESSED' where (CRITERIA) AND (STATUS = 'RESERVED-1');

<operation name="receive">
<jca:operation
ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
…
PollingStrategyName="SequencingPollingStrategy"
SequencingFieldName="MODIFIED_DATE"
SequencingFieldType="java.sql.Date"
SequencingTableNameFieldValue="EMPLOYEE"
SequencingTableName="SEQUENCING_HELPER"
SequencingTableNameFieldName="TABLE_NAME"
SequencingTableValueFieldName="LAST_READ_DATE"

CREATE TABLE SEQUENCING_HELPER 
(
TABLE_NAME VARCHAR2(32) NOT NULL,
LAST_READ_DATE DATE
)
;

create trigger Employee_modified
before insert or update on Employee
for each row
begin
  :new.modified_date := sysdate;
end;

CREATE OR REPLACE TRIGGER EVENT_ON_DEPT 
   AFTER INSERT OR UPDATE ON DEPARTMENT 
   REFERENCING NEW AS newRow 
   FOR EACH ROW 
   DECLARE X NUMBER;
BEGIN
   SELECT COUNT(*) INTO X FROM DEPT_CONTROL WHERE (DEPTNO = :newRow.DEPTNO);
   IF X = 0 then
   insert into DEPT_CONTROL values (:newRow. DEPTNO);
   END IF;
END;
CREATE OR REPLACE TRIGGER EVENT_ON_EMPLOYEE
   AFTER INSERT OR UPDATE ON EMPLOYEE
   REFERENCING OLD AS oldRow NEW AS newRow
   FOR EACH ROW
   DECLARE X NUMBER;
BEGIN
   SELECT COUNT(*) INTO X FROM DEPT_CONTROL WHERE (DEPTNO = :newRow.DEPTNO);
   IF X = 0 then
   INSERT INTO DEPT_CONTROL VALUES (:newRow.DEPTNO);
   END IF;
   IF (:oldRow.DEPTNO <> :newRow.DEPTNO) THEN
      SELECT COUNT(*) INTO X FROM DEPT_CONTROL WHERE (DEPTNO = :oldRow.DEPTNO);
      IF (X = 0) THEN
         INSERT INTO DEPT_CONTROL VALUES (:oldRow.DEPTNO);
      END IF;
   END IF;
END;

Oracle_Home\integration\orabpel\samples\tutorials\122.DBAdapter

What Happens When Relationships Are Created or Removed

When tables are initially imported into the wizard, a TopLink direct-to-field mapping corresponding to each field in the database is created. Consider the schemas shown in Figure 4-8 and Figure 4-9:

Figure 4-8 EMPLOYEE Schema

Description of "Figure 4-8 EMPLOYEE Schema"

Figure 4-9 ADDRESS Schema

Description of "Figure 4-9 ADDRESS Schema"

Immediately after importing these two tables, the following mappings in the Employee descriptor are created:

Employee:

• id (direct mapping to the ID field, for example, 151)

• name (direct mapping to the NAME field, for example, Stephen King)

• addrId (direct mapping to the ADDR_ID field, for example, 345)

When creating a relationship mapping, the direct-to-field mappings to the foreign key fields are removed and replaced with a single relationship (one-to-one, one-to-many) mapping. Therefore, after creating a one-to-one relationship between Employee and Address called homeAddress, the Employee descriptor looks like this:

Employee:

• id

• name

• homeAddress (one-to-one mapping to the ADDRESS table; this attribute now represents the entire Address object.)

When a relationship is removed, the direct mappings for the foreign keys are restored.

Different Types of One-to-One Mappings

The following ways of specifying one-to-one relationships are supported:

• The foreign keys exist on the parent table, as shown in Figure 4-10 and Figure 4-11.

• The foreign keys exist on the child table, as shown in Figure 4-12 and Figure 4-13.

Figure 4-10 Foreign Keys on the Parent Table EMPLOYEE

Description of "Figure 4-10 Foreign Keys on the Parent Table EMPLOYEE"

Figure 4-11 Foreign Keys on the Parent Table ADDRESS

Description of "Figure 4-11 Foreign Keys on the Parent Table ADDRESS"

Figure 4-12 Foreign Keys on the Child Table EMPLOYEE

Description of "Figure 4-12 Foreign Keys on the Child Table EMPLOYEE"

Figure 4-13 Foreign Keys on the Child Table ADDRESS

Description of "Figure 4-13 Foreign Keys on the Child Table ADDRESS"

Delete the Rows that Were Read

With this option, the rows are deleted from the database after they have been read and processed by the adapter service.

Update a Field in the Table (Logical Delete)

With this option, you update a field in the root database table to indicate that the rows have been read. The WHERE clause of the query is updated automatically after you complete the configuration, as shown in Figure 4-18.

Figure 4-18 Adapter Configuration Wizard: Logical Delete

Description of "Figure 4-18 Adapter Configuration Wizard: Logical Delete"

Using this approach, your database table looks something like Figure 4-19.

Figure 4-19 Updating Fields in a Table

Description of "Figure 4-19 Updating Fields in a Table"

Note the following:

• Rows 150 and 153 have been previously read and processed.

• At the next polling event, row 152 will be read and processed because it contains UNPROCESSED in the Status column. Because an explicit Unread Value was provided, row 151 will not be read.

• Row 154 has been flagged as LOCKED and will not be read. You can use this reserved value if your table is in use by other processes.

Update a Sequencing Table

With this option, you are keeping track of the last-read rows in a separate sequence table. Figure 4-20 shows the information you provide. The WHERE clause of your query is updated automatically after you complete the configuration.

Figure 4-20 Adapter Configuration Wizard: Last Read IDs Table

Description of "Figure 4-20 Adapter Configuration Wizard: Last Read IDs Table"

Using these settings, your sequence table looks something like Figure 4-21.

Figure 4-21 Updating a Sequence Table

Description of "Figure 4-21 Updating a Sequence Table"

Whenever a row is read, this table is updated with the ID that was just read. Then when the next polling event occurs, it will search for rows that have an ID greater than the last-read ID (154).

Typical columns used are event_id, transaction_id, scn (system change number), id, or last_updated. These columns typically have (monotonically) increasing values, populated from a sequence number or sysdate.

Importing Tables

When you import a table, the offline table support of JDeveloper BPEL Designer creates an offline snapshot of the database table. You can modify this offline version of the table (for example, you can add a foreign key constraint) without affecting the real database table. This creates a TopLink descriptor and associated Java source file for the table, and all the attributes in the descriptor are automapped to their corresponding database columns. The TopLink descriptor maps the Java class to the offline database table.

Most typical data columns are mapped as direct-to-field mappings, meaning that the value in the database column is directly mapped to the attribute. For example, a SALARY column in the database is mapped to a salary attribute in the object model, and that attribute contains the value of that column.

If foreign key constraints are already present in the imported tables, then relationship mappings are autogenerated between the tables. To cover as many scenarios as possible, two mappings are generated for every foreign key constraint encountered: a one-to-one mapping from the source table to the target table, and a one-to-many mapping in the opposite direction. After this is done, you are left with an OracleAS TopLink Mapping Workbench project in your BPEL project.

When you have finished importing tables, you must select a root database table. In doing so, you are actually selecting which TopLink descriptor stores the autogenerated query.

Creating Relationships

When you create or remove a relationship, you are actually modifying the TopLink descriptor mappings. Creating a new relationship does the following:

• Creates a foreign key constraint in the offline database table

• Adds a one-to-one or one-to-many mapping to the descriptor

• Removes the direct-to-field mappings to the foreign key fields

Removing a relationship mapping does the following:

• Removes the one-to-one or one-to-many mapping from the descriptor

• Removes the foreign key constraint from the offline database table

• Adds direct-to-field mappings for each foreign key field involved in the relationship

Generating Design-Time Artifacts

The following files are generated:

• service_name.wsdl—contains the database adapter service definition

• RootTable.xsd—the XML type definition of the root object

• toplink_mappings.xml—contains the TopLink mapping metadata for your BPEL project. It is the only Toplink artifact that is deployed to the server.

Deleting a Descriptor

You cannot remove TopLink descriptors from your project from within the wizard because removing descriptors can potentially affect other partner links that are sharing that descriptor. To explicitly remove a descriptor, do the following:

• Click the TopLink Mappings node under Application Sources under your project in the Applications - Navigator.

• Select the descriptor from the tree in the TopLink Mappings - Structure pane.

• Right-click and select Remove.

  
  Description of the illustration adapter_wizard4.gif
  
  

Returning Partial Objects When Querying

Currently, the Adapter Configuration Wizard does not have built-in support for partial object reading, that is, returning only specific fields from a table. To achieve this functionality, you can manually unmap any attributes that you do not want to include in the result set. Relationship mappings can be unmapped by removing them in the Relationships window, but direct mappings must be explicitly unmapped on the TopLink descriptor:

1. Click the TopLink Mappings node under Application Sources under your project in the Applications - Navigator.

2. Select the descriptor containing the attribute you want to unmap from the tree in the TopLink Mappings - Structure pane.

3. Right-click the attribute you want to unmap and select Map As > Unmapped.

   
   Description of the illustration adapter_wizard11.gif
   
   

To remap the attribute, you can do the following:

1. Click the TopLink Mappings node under Application Sources under your project in the Applications - Navigator.

2. Select the descriptor containing the attribute you want to remap from the tree in the TopLink Mappings - Structure pane.

3. Right-click the attribute you want to remap and select Map As > Direct to Field.

   The TopLink Mappings Editor automatically opens in the JDeveloper BPEL Designer window.

4. From Database Field, select the column to which the attribute should be mapped.

   
   Description of the illustration adapter_wizard12.gif
   
   

Renaming a Mapping

Open the corresponding Java source file and change the name. Then go to the structure/Mappings pane, and the newly named attribute will appear unmapped. Right-click it and select Map As to remap it. Then save and regenerate BPEL artifacts.

Keep in mind there are four views, the project view, the table/descriptor view, and the individual attribute/column view you can access from the TopLink Mappings structure window. The Java source view is not exactly a TopLink view, but can be treated as such (when renaming a mapping).

Configuring Offline Database Tables

Offline database tables are internal to the OracleAS TopLink Mapping Workbench project. When you run the wizard, a TopLink project is created. When you import tables, they are saved as offline table definitions.

You can use the offline database tables to control the micromapping from database datatype to XML datatype. If you are using a third-party database, you may need to edit these objects as a workaround. For instance, a serial field on a third-party database may need to be mapped as Integer so that it is recognized by the wizard and mapped to xs:integer.

Run the wizard once. Then add the following to your JDeveloper BPEL Designer project: database/schemaName/schemaName.schema

Click the table name (see Figure 4-22) after it is added to your project and change the types of any of the columns. When you run the wizard again (in edit mode) and click Finish, the toplink_mappings.xml and XSD file are remapped based on the new database datatypes.

Figure 4-22 Configuring Offline Tables

Description of "Figure 4-22 Configuring Offline Tables"

Edit the offline table in your JDeveloper BPEL Designer project (see Figure 4-23), not the table reachable from the ConnectionManager. If you try the latter, the column types will not be editable, because you are editing the table itself, not an offline representation of it.

Figure 4-23 Editing Offline Tables

Description of "Figure 4-23 Editing Offline Tables"

DBWriteInteractionSpec

The following code example shows the binding section corresponding to the movie service to write to the Movies table:

    <binding name="movie_binding" type="tns:movie_ptt">
        <jca:binding />
        <operation name="merge">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                DmlType="merge"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
        <operation name="insert">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                DmlType="insert"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
        <operation name="update">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                DmlType="update"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
        <operation name="write">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                DmlType="write"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
        <operation name="delete">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                DmlType="delete"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
    </binding>

Table 4-12 describes the DBWriteInteractionSpec parameters:

DBReadInteractionSpec

The following code example corresponds to the movie service to query the Movies table:

 <binding name="movie_binding" type="tns:movie_ptt">
        <jca:binding />
        <operation name="movieSelect">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBReadInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                QueryName="movieSelect"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
        <operation name="queryByExample">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBReadInteractionSpec"
                DescriptorName="BPELProcess1.Movies"
                IsQueryByExample="true"
                MappingsMetaDataURL="toplink_mappings.xml" />
            <input/>
        </operation>
    </binding>

Table 4-13 describes the DBReadInteractionSpec parameters:

DBActivationSpec

The following code example shows the binding section corresponding to the MovieFetch service to poll the Movies table using DeletePollingStrategy:

    <binding name="MovieFetch_binding" type="tns:MovieFetch_ptt">
        <pc:inbound_binding/>
        <operation name="receive">
            <jca:operation
                ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
                DescriptorName="BPELProcess1.Movies"
                QueryName="MovieFetch"
                PollingStrategyName="DeletePollingStrategy"
                MaxRaiseSize="1"
                MaxTransactionSize="unlimited"
                PollingInterval="5"
                MappingsMetaDataURL="toplink_mappings.xml" />
        <input/>
        </operation>
    </binding>

Table 4-14 describes the DBActivationSpec parameters:

The following code example is the binding section corresponding to the MovieFetch service to poll the Movies table using LogicalDeletePollingStrategy:

    <binding name="PollingLogicalDeleteService_binding"
             type="tns:PollingLogicalDeleteService_ptt">
        <pc:inbound_binding/>
        <operation name="receive">
            <jca:operation
                ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
                DescriptorName="PollingLogicalDeleteStrategy.Movies"
                QueryName="PollingLogicalDeleteService"
                PollingStrategyName="LogicalDeletePollingStrategy"
                MarkReadFieldName="DELETED"
                MarkReadValue="TRUE"
                MarkReservedValue="MINE"
                MarkUnreadValue="FALSE"
                MaxRaiseSize="1"
                MaxTransactionSize="unlimited"
                PollingInterval="10"
                MappingsMetaDataURL="toplink_mappings.xml" />
        <input/>
        </operation>
    </binding>

Table 4-15 describes all of the additional DBActivationSpec parameters for LogicalDeletePollingStrategy:

The following code example shows the binding section corresponding to the MovieFetch service to poll the Movies table using SequencingPollingStrategy:

    <binding name="PollingLastReadIdStrategyService_binding"
             type="tns:PollingLastReadIdStrategyService_ptt">
        <pc:inbound_binding/>
        <operation name="receive">
            <jca:operation
                ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
                DescriptorName="PollingLastReadIdStrategy.Movies"
                QueryName="PollingLastReadIdStrategyService"
                PollingStrategyName="SequencingPollingStrategy"
                SequencingFieldName="SEQUENCENO"
                SequencingTableNameFieldValue="MOVIES"
                SequencingTableName="PC_SEQUENCING"
                SequencingTableNameFieldName="TABLE_NAME"
                SequencingTableValueFieldName="LAST_READ_ID"
                MaxRaiseSize="1"
                MaxTransactionSize="unlimited"
                PollingInterval="10"
                MappingsMetaDataURL="toplink_mappings.xml" />
        <input/>
        </operation>
    </binding>

Table 4-16 describes all of the additional DBActivationSpec parameters for SequencingPollingStrategy:

See "Deployment" for details about the service section of the WSDL.

Location of the oc4j-ra.xml File

For the Oracle BPEL Process Manager for Developers installation, oc4j-ra.xml is at

Oracle_Home\integration\orabpel\system\appserver\oc4j\j2ee\home\
application-deployments\default\DbAdapter\oc4j-ra.xml

For the Oracle BPEL Process Manager for OracleAS Middle Tier installation, oc4j-ra.xml is at

Oracle_Home\j2ee\OC4J_BPEL\application-deployments\default\DBAdapter\oc4j-ra.xml

A sample entry from the oc4j-ra.xml file follows:

<connector-factory location="eis/DB/DBConnection1" connector-name="Database Adapter">
<config-property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<config-property name="connectionString"
                 value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<config-property name="userName" value="scott"/>
<config-property name="password" value="tiger"/>
<config-property name="minConnections" value="5"/>
<config-property name="maxConnections" value="5"/>
<config-property name="minReadConnections" value="1"/>
<config-property name="maxReadConnections" value="1"/>
<config-property name="usesExternalConnectionPooling" value="false"/>
<config-property name="dataSourceName" value=""/>
<config-property name="usesExternalTransactionController" value="false"/>
<config-property name="platformClassName"
                 value="oracle.toplink.internal.databaseaccess.Oracle9Platform"/>
<config-property name="usesNativeSequencing" value="true"/>
<config-property name="sequencePreallocationSize" value="50"/>
<config-property name="tableQualifier" value=""/>
</connector-factory>

The properties specified in the connection factory use OracleAS TopLink by translating to roughly 40 properties of an underlying TopLink login object. This is the equivalent of the OracleAS TopLink sessions.xml file.

Table 4-17, Table 4-18, and Table 4-19 show the configuration properties available to configure the run-time connection.

If the application server connection pool is used, then most connection properties in this class are managed elsewhere.

Table 4-20 shows the advanced properties, which are database platform variables. Set the DatabasePlatform name to one of the following variables.

Advanced Properties

The following properties are configurable by using the managed connection factory entry in the oc4j-ra.xml file:

String connectionString
String userName
String password
String encryptionClassName
Integer minConnections
Integer maxConnections
Boolean useReadConnectionPool
Integer minReadConnections
Integer maxReadConnections
String dataSourceName
String driverClassName
Integer cursorCode
String databaseName
String driverURLHeader
Integer maxBatchWritingSize
String platformClassName
String sequenceCounterFieldName
String sequenceNameFieldName
Integer sequencePreallocationSize
String sequenceTableName
String serverName
Boolean shouldBindAllParameters
Boolean shouldCacheAllStatements
Boolean shouldIgnoreCaseOnFieldComparisons
Boolean shouldForceFieldNamesToUpperCase
Boolean shouldOptimizeDataConversion
Boolean shouldTrimStrings
Integer statementCacheSize
Integer stringBindingSize
String tableQualifier
Integer transactionIsolation
Boolean usesBatchWriting
Boolean usesByteArrayBinding
Boolean usesDirectDriverConnect
Boolean usesExternalConnectionPooling
Boolean usesExternalTransactionController
Boolean usesJDBCBatchWriting
Boolean usesNativeSequencing
Boolean usesNativeSQL
Boolean usesStreamsForBinding
Boolean usesStringBinding

The following properties appear in the oracle.toplink.sessions.DatabaseLogin object.

See OracleAS TopLink API reference information on DBConnectionFactory Javadoc and DatabaseLogin Javadoc at

http://download-east.oracle.com/docs/cd/B10464_02/web.904/b10491/index.html

To configure any of the preceding properties:

1. Add the following to the ra.xml file:

   <config-property>
       <config-property-name>usesJDBCBatchWriting</config-property-name>
       <config-property-type>java.lang.Boolean</config-property-type>
       <config-property-value>true</config-property-value>
     </config-property>
   
   

   For Oracle BPEL Process Manager for Developers, ra.xml is at

   Oracle_Home\integration\orabpel\system\appserver\oc4j\j2ee\home\connectors\
   DbAdapter\DbAdapter\META-INF\ra.xml
   
   

   For Oracle BPEL Process Manager for OracleAS Middle Tier, ra.xml is at

   Oracle_Home\j2ee\OC4J_BPEL\connectors\DbAdapter\DbAdapter\META-INF\ra.xml
   
   

2. Add the following to the oc4j-ra.xml file:

   <config-property name="usesJDBCBatchWriting" value="true"/>

3. Restart Oracle BPEL Server for the changes to take effect.

You can also update the factory default oc4j-ra.xml and ra.xml files before you deploy the database adapter. This way, you need only deploy once and do not need to restart the application server.

Outbound Write: Should You Use Merge, Write, or Insert?

If you run through the Adapter Configuration Wizard and select Insert or Update, you get a WSDL with the following operations: merge (default), insert, update, and write. The latter three call TopLink queries of the same name, avoiding advanced functionality that you may not need for straightforward scenarios. You can make the change by double-clicking an invoke activity and selecting a different operation. The merge is the most expensive, followed by the write and then the insert.

The merge first does a read of every element and calculates what has changed. If a row has not changed, it is not updated. The extra reads (for existence) and the complex change calculation add considerable overhead. For simple cases, this can be safely avoided; that is, if you changed only two columns, it does not matter if you update all five anyway. For complex cases, however, the opposite is true. If you have a master record with 100 details, but you changed only two columns on the master and two on one detail, the merge updates those four columns on two rows. A write does a write of every column in all 101 rows. Also, the merge may appear slower, but can actually relieve pressure on the database by minimizing the number of writes.

The insert operation is the most performant because it uses no existence check and has no extra overhead. You have no reads, only writes. If you know that you will do an insert most of the time, try an insert, and catch a Unique Key Constraint SQL exception inside your BPEL process, which can then perform a merge or update instead. For simple schemas this makes sense, but a merge is good if you have a mix of new and existing objects (for instance, a master row containing several new details). The update is similar to the insert.

To monitor performance, you can enable debug logging and then watch the SQL for various inputs.

The OracleAS TopLink Cache: When Should You Use It?

Caching is an important performance feature of OracleAS TopLink. However, issues with stale data can be difficult to manage. By default, the database adapter uses a WeakIdentityMap, meaning a cache is used only to resolve cyclical references, and entries are quickly reclaimed by the Java virtual machine. If you have no cycles (and you ideally should not for XML), you can switch to a NoIdentityMap. The TopLink default is a SoftCacheWeakIdentityMap. This means that the most frequently used rows in the database are more likely to appear already in the cache.

For a knowledge article on caching, go to

http://www.oracle.com/technology/tech/java/newsletter/november04.html

Existence Checking

One method of performance optimization for merge is to eliminate check database existence checking. The existence check is marginally better if the row is new, because only the primary key is returned, not the entire row. But, due to the nature of merge, if the existence check passes, the entire row must be read anyway to calculate what changed. Therefore, for every row to be updated, you see one extra round trip to the database during merge.

It is always safe to use check cache on the root descriptor/table and any child tables if A is master and B is a privately owned child. If A does not exist, B cannot exist. And if A exists, all its Bs are loaded as part of reading A; therefore, check cache works.

Inbound (Polling): maxRaiseSize

On read (inbound) you can set maxRaiseSize = 0 (unbounded), meaning that if you read 1000 rows, you will create one XML with 1000 elements, which is passed through a single Oracle BPEL Process Manager instance. A merge on the outbound side can then take all 1000 in one group and write them all at once with batch writing.

Inbound (Polling): Choosing a Polling Strategy

Your choice of polling strategy matters too. Avoid the delete polling strategy because it must individually delete each row. The sequencing polling strategy can destroy 1000 rows with a single update to a helper table.

Relationship Reading (Batch Attribute and Joined Attribute Reading)

Batch reading of one-to-many and one-to-one relationships is on by default. You can also use joined reading for one-to-one relationships instead, which may offer a slight improvement.

Connection Pooling

You can configure a connection pool if using either the adapter's local connection pool or an application server data source. Creating a database connection is an expensive operation. Ideally you should only exceed the minConnections under heavy loads. If you are consistently using more connections than that at once, then you may spend a lot of time setting up and tearing down connections. The database adapter also has a read connection pool. A read connection is more performant because there is no limit on how many users can use one connection for reading at the same time, a feature that most JDBC drivers support.

Inbound Distributed Polling

The database adapter is designed to scale to the number of unprocessed rows on the database. By default, it is possible to read and process one database row or 10,000 with as little as three round trips to the database. The most expensive operations are limited to a constant number. You can also configure the database adapter for a distributed environment.

Concurrency Control: Pessimistic Locking

You can set a simple option that enables the database adapter to work safely in a distributed environment by making the first polling query acquire locks, as shown in Figure 4-24. In SQL terms, you are making your first SELECT into a SELECT...FOR UPDATE.

Figure 4-24 Acquiring Locks

Description of "Figure 4-24 Acquiring Locks"

The behavior of all polling strategies is as follows:

1. Read all unprocessed rows.

2. Process those rows.

3. Commit.

If any adapter instance performs step 1 while another instance is between steps 1 and 3, then duplicate processing occurs. Acquiring locks on the first operation and releasing them in commit solves this problem, and may naturally order the polling instances.

To enable pessimistic locking, run through the wizard once to create an inbound polling query. In the Applications Navigator window, expand Application Sources, then TopLink, and click TopLink Mappings. In the Structure window, click the table name. In Diagram View, click the following tabs: TopLink Mappings, Queries, Named Queries, Options; then the Advanced… button, and then Pessimistic Locking and Acquire Locks. You see the message, ÒSet Refresh Identity Map Results?" If a query uses pessimistic locking, it must refresh the identity map results. Click OK when you see the message, "Would you like us to set Refresh Identity Map Results and Refresh Remote Identity Map Results to true?Ó Run the wizard again to regenerate everything. In the new toplink_mappings.xml file, you see something like this for the query: <lock-mode>1</lock-mode>.

Note the following:

• The preceding procedure works in conjunction with every polling strategy where the first operation is a read.

• For the sequencing-based polling strategies, the SELECT FOR UPDATE is applied to the SELECT on the helper table only. The SELECT on the polled table does not acquire locks because you do not have write access to those tables.

• If an adapter instance fails while polling records, those records are returned to the unprocessed pool (no commit happens).

• No individual adapter instance is special. In an ideal distributed system, coordination between instances is minimal (here effected with locking). No master acts as a weak link, and every part is identically configured and interchangeable.

• Other than the SELECT FOR UPDATE, no extra reads or writes are performed by the database adapter in a distributed environment.

Notes on Using SQL Server

Note the following tips when connecting to a SQL Server database:

• User name and password

  • SQLServer 2005 installs with Windows authentication as the default. Therefore, you do not log in with a user name and password, but instead your Windows user account either has privilege or does not. JDBC requires you to provide a user name and password.

    According to support.microsoft.com, "Microsoft SQL Server 2000 driver for JDBC does not support connecting by using Windows NT authentication."

    http://support.microsoft.com/default.aspx?scid=kb;en-us;313100
    
    

    However, the data direct driver claims to do so.

    If you use your Windows user name and password, you may see something like:

    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user
    'DOMAIN\USER'. The user is not associated with a trusted SQL Server
    connection.[Microsoft][SQLServer 2000 Driver for JDBC]
    An error occured while attempting to log onto the database.
    
    

    You must select mixed mode authentication on a clean installation.

  • On a SQLExpress 2005 installation, the system username is su and the password is whatever you provide.

• Connect string

  From the sqlcmd login, you can infer your connect string, as in the following examples:

  Example 1:

  sqlcmd
  1>
  jdbc:microsoft:sqlserver://localhost:1433
  

  Example 2:

  sqlcmd -S user.mycompany.com\SQLExpress
  1>
  jdbc:microsoft:sqlserver://user.mycompany.com\SQLExpress:1433
  
  

  Example 3:

  sqlcmd -S user.mycompany.com\SQLExpress -d master
  1>
  jdbc:microsoft:sqlserver://user.mycompany.com\SQLExpress:1433;databasename=master
  
  

  A full URL is as follows:

  jdbc:microsoft:sqlserver://serverName[\instanceName]:tcpPort[;SelectMethod=cursor][;databasename=databaseName]
  
  

• Database name

  If you must explicitly supply the database name, but do not know it, go to

  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
  
  

  If you see a file named master.mdf, then one of the database names is master.

• TCP port

  Make sure that SQLBrowser is running and that your SQLServer service has TCP/IP enabled and is listening on static port 1433. Disable dynamic ports. In SQL Native Client Configuration \ Client Protocols, make sure that TCP\IP is enabled and that the default port is 1433.

• JDBC drivers

  You must download the JDBC drivers separately. From www.microsoft.com, click Downloads and search on jdbc. You may also try using the third-party data direct driver.

Notes on Using Oracle Olite

To create an Oracle Lite database connection, follow the steps for a third-party JDBC driver exactly (because the existing wizard and libraries for the Oracle Lite database are not what you expect and require extra configuration). Table 4-21 provides information for connecting to an Oracle Lite database.

Using Top-Level Standalone APIs

This section describes how to use the wizard with APIs that are not defined in PL/SQL packages. You use the Adapter Configuration Wizard – Stored Procedures to select a procedure or function and generate the XSD. See "The Adapter Configuration Wizard" if you are not familiar with how to start the wizard.

In the wizard, select Database Adapter, as shown in Figure 4-25.

Figure 4-25 Selecting the Database Adapter in the Adapter Configuration Wizard

Description of "Figure 4-25 Selecting the Database Adapter in the Adapter Configuration Wizard"

After entering a service name (for example, ProcedureProc) and an optional description for the service, you associate a connection with the service, as shown in Figure 4-26. You can select an existing connection from the list or create a new connection.

Figure 4-26 Setting the Database Connection in the Adapter Configuration Wizard

Description of "Figure 4-26 Setting the Database Connection in the Adapter Configuration Wizard"

For the Operation Type, select Call a Stored Procedure or Function, as shown in Figure 4-27.

Figure 4-27 Calling for a Stored Procedure or Function in the Adapter Configuration Wizard

Description of "Figure 4-27 Calling for a Stored Procedure or Function in the Adapter Configuration Wizard"

Next you select the schema and procedure or function. You can select a schema from the list or select <Default Schema>, in which case the schema associated with the connection is used. If you know the procedure name, enter it in the Procedure field. If the procedure is defined inside a package, then you must include the package name, as in EMPLOYEE.GET_NAME.

If you do not know the schema and procedure names, click Browse to access the Stored Procedures window, shown in Figure 4-28.

Figure 4-28 Searching for a Procedure or Function

Description of "Figure 4-28 Searching for a Procedure or Function"

Select a schema from the list or select <Default Schema>. The available procedures are displayed in the left window. To search for a particular API in a long list of APIs, enter search criteria in the Search field. For example, to find all APIs that begin with XX, enter XX% and click the Search button. Clicking the Show All button displays all available APIs.

Figure 4-29 shows how you can select the PROC procedure and click the Arguments tab. The Arguments tab displays the parameters of the procedure, including their names, type, mode (IN, IN/OUT or OUT) and the numeric position of the parameter in the definition of the procedure.

Figure 4-29 Viewing the Arguments of a Selected Procedure

Description of "Figure 4-29 Viewing the Arguments of a Selected Procedure"

Figure 4-30 shows how the Source tab displays the code that implements the procedure. Text that matches the name of the procedure is highlighted.

Figure 4-30 Viewing the Source Code of a Selected Procedure

Description of "Figure 4-30 Viewing the Source Code of a Selected Procedure"

After you select a procedure or function and click OK, information about the API is displayed, as shown in Figure 4-31. Use Back or Browse to make revisions, or Next followed by Finish to conclude.

Figure 4-31 Viewing Procedure or Function Details in the Adapter Configuration Wizard

Description of "Figure 4-31 Viewing Procedure or Function Details in the Adapter Configuration Wizard"

When you have finished using the Adapter Configuration Wizard, two files are added to the existing project: servicename.wsdl (for example, ProcedureProc.wsdl) and the generated XSD. The generated XSD file is named schema_package_procedurename.xsd. In this case, SCOTT_PROC.xsd is the name of the generated XSD file.

Using Packaged APIs and Overloading

Using APIs defined in packages is similar to using standalone APIs. The only difference is that you can expand the package name to see a list of all the APIs defined within the package, as shown in Figure 4-32.

APIs that have the same name but different parameters are called overloaded APIs. As shown in Figure 4-32, the package called PACKAGE has two overloaded procedures called OVERLOAD.

Figure 4-32 A Package with Two Overloaded Procedures

Description of "Figure 4-32 A Package with Two Overloaded Procedures"

As Figure 4-33 shows, the code for the entire PL/SQL package is displayed, regardless of which API from the package is selected when you view the Source tab. Text that matches the name of the procedure is highlighted.

Figure 4-33 Viewing the Source Code of an Overloaded Procedure

Description of "Figure 4-33 Viewing the Source Code of an Overloaded Procedure"

After you select a procedure or function and click OK, information about the API is displayed, as shown in Figure 4-34. The schema, procedure name, and parameter list are displayed. Note how the procedure name is qualified with the name of the package (PACKAGE.OVERLOAD). Use Back or Browse to make revisions, or Next followed by Finish to conclude.

Figure 4-34 Viewing Procedure or Function Details in the Adapter Configuration Wizard

Description of "Figure 4-34 Viewing Procedure or Function Details in the Adapter Configuration Wizard"

When you have finished using the Adapter Configuration Wizard, two files are added to the existing project: Overload.wsdl and SCOTT_PACKAGE_OVERLOAD_2.xsd. The _2 appended after the name of the procedure in the XSD filename differentiates the overloaded APIs.

The WSDL–XSD Relationship

In the paragraphs that follow, the operation name, ProcedureProc, and procedure name, PROC, are taken from an example cited previously (see Figure 4-31). The generated WSDL imports the XSD.

<types>
  <schema xmlns=Óhttp://www.w3.org/2001/XMLSchemaÓ>
    <import namespace=Óhttp://xmlns.oracle.com/pcbpel/adapter/db/SCOTT/PROC/Ó
      schemaLocation=ÓSCOTT_PROC.xsdÓ/>
  </schema>
</types>

The namespace is derived from the schema, package, and procedure name, and appears as the targetNamespace in the generated XSD.

A root element called InputParameters is created in the XSD for specifying elements that correspond to the IN and IN/OUT parameters of the stored procedure. Another root element called OutputParameters is also created in the XSD for specifying elements only if there are any IN/OUT or OUT parameters. Note that IN/OUT parameters appear in both root elements.

These root elements are represented in the XSD as an unnamed complexType definition whose sequence includes one element for each parameter. If there are no IN or IN/OUT parameters, the InputParameters root element is still created; however, the complexType is empty. A comment in the XSD indicates that there are no such parameters. An example of one of these root elements follows.

<element name="InputParameters"
  <complexType>
    <sequence>
      <element …>
       …
    </sequence>
  </complexType>
</element>

The WSDL defines message types whose parts are defined in terms of these two root elements.

<message name=Óargs_in_msgÓ
  <part name=ÓInputParametersÓ element=Ódb:InputParametersÓ/>
</message>
<message name=Óargs_out_msgÓ
  <part name=ÓOutputParametersÓ element=Ódb:OutputParametersÓ/>
</message>

The db namespace is the same as the targetNamespace of the generated XSD. Note that the args_in_msg message type always appears in the WSDL while args_out_msg is included only if the OutputParameters root element is generated in the XSD.

An operation is defined in the WSDL whose name is the same as the adapter service and whose input and output messages are defined in terms of these two message types.

<portType name=ÓProcedureProc_pttÓ>
  <operation name=ÓProcedureProcÓ>
    <input message=Ótns:args_in_msgÓ/>
    <output message=Ótns:args_out_msgÓ/>
  </operation>
</portType>

The input message always appears while the output message depends on the existence of the OutputParameters root element in the XSD. The tns namespace is derived from the operation name and is defined in the WSDL as

xmlns:tns=Óhttp://xmlns.oracle.com/pcbpel/adapter/db/ProcedureProc/Ó

The root elements in the XSD define the structure of the parts used in the messages that are passed into and sent out of the Web service encapsulated by the WSDL.

The input message in the WSDL corresponds to the InputParameters root element from the XSD. The instance XML supplies values for the IN and IN/OUT parameters of the stored procedure. The output message corresponds to the OutputParameters root element. This is the XML that gets generated after the stored procedure has executed. It holds the values of any IN/OUT and OUT parameters.

Supported Primitive Datatypes

Many primitive datatypes have well-defined mappings and therefore are supported by both the design-time and run-time components. In addition, you can use user-defined types such as VARRAY, nested tables, and OBJECT. Table 4-22 lists the primitive datatypes supported by the database adapter for stored procedures.

Generated XSD Attributes

Table 4-23 lists the attributes used in the generated XSDs. Attributes prefixed with db: are specific to the database adapter.

The db namespace is used to distinguish attributes used during run time from standard XML schema attributes. The db:type attribute is used to indicate what the database type is so that a suitable JDBC type mapping can be obtained at run time. The db:index attribute is used as an optimization by both the design-time and run-time components to ensure that the parameters are arranged in the proper order. Parameter indices begin at 1 for procedures and 0 for functions. The return value of a function is represented as an OutputParameter element whose name is the name of the function and whose db:index is 0. The db:default attribute is used to indicate whether or not a parameter has a default clause.

The minOccurs value is set to 0 to allow for an IN parameter to be removed from the XML. This is useful when a parameter has a default clause defining a value for the parameter (for example, X IN INTEGER DEFAULT 0). At run time, if no element is specified for the parameter in the XML, the parameter is omitted from the invocation of the stored procedure, thus allowing the default value to be used. Each parameter can appear at most once in the invocation of a stored procedure or function. Therefore, maxOccurs, whose default value is always 1, is always omitted from elements representing parameters.

The nillable attribute is always set to true to allow the corresponding element in the instance XML to have a null value (for example, <X/> or <X></X>). In some cases, however, to pass schema validation, an element such as this, which does have a null value, must state this explicitly (for example, <X xsi:nil="true"/>). The namespace, xsi, used for the nillable attribute, must be declared explicitly in the instance XML (for example, xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance").

User-Defined Types

The wizard can also generate valid definitions for user-defined types such as collections (VARRAY and nested tables) and OBJECT. These are created as complexType definitions in the XSD.

For VARRAY, the complexType definition defines a single element in its sequence, called name_ITEM, where name is the name of the VARRAY element. All array elements in the XML are so named. Given the following VARRAY type definition,

SQL> CREATE TYPE FOO AS VARRAY (5) OF VARCHAR2 (10);

and a VARRAY element, X, whose type is FOO, the following complexType is generated:

<complexType name="FOO">
  <sequence>
    <element name="X_ITEM" db:type="VARCHAR2" minOccurs="0" maxOccurs="5" nillable="true"/>
      <simpleType>
        <restriction base="string">
          <maxLength value="10"/>
        </restriction>
      </simpleType>
  </sequence>
</complexType>

The minOccurs value is 0 to allow for an empty collection. The maxOccurs value is set to the maximum number of items that the collection can hold. Note that the db:index attribute is not used. Having nillable set to true allows individual items in the VARRAY to be null.

Note the use of the restriction specified on the element of the VARRAY, FOO. This is used on types such as CHAR and VARCHAR2, whose length is known from the declaration of the VARRAY (or nested table). It specifies the type and maximum length of the element. An element value that exceeds the specified length causes the instance XML to fail during schema validation.

The attribute values of a parameter declared to be of type FOO look as follows in the generated XSD:

<element name="X" type="db:FOO" db:type="Array" db:index="1" minOccurs="0" nillable="true"/>

The type and db:type values indicate that the parameter is represented as an array defined by the complexType called FOO in the XSD. The value for db:index is whatever the position of that parameter is in the stored procedure.

A nested table is treated almost identically to a VARRAY. The following nested table type definition,

SQL> CREATE TYPE FOO AS TABLE OF VARCHAR2 (10);

is also generated as a complexType with a single element in its sequence, called name_ITEM. The element has the same attributes as in the VARRAY example, except that the maxOccurs value is unbounded because nested tables can be of arbitrary size.

<complexType name="FOO">
  <sequence>
    <element name="X_ITEM" … maxOccurs="unbounded" nillable="true">
      ...
    </element>
  </sequence>
</complexType>

An identical restriction is generated for the X_ITEM element in the VARRAY. The attributes of a parameter, X, declared to be of this type, are the same as in the VARRAY example.

An OBJECT definition is also generated as a complexType. Its sequence holds one element for each attribute in the OBJECT. The following OBJECT,

SQL> CREATE TYPE FOO AS OBJECT (X VARCHAR2 (10), Y NUMBER);

is represented as a complexType called FOO with two sequence elements.

<complexType name="FOO">
  <sequence>
    <element name="X" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      <simpleType>
        <restriction base="string">
          <maxLength value="10"/>
        </restriction>
      </simpleType>
    <element name="Y" type="decimal" db:type="NUMBER" minOccurs="0" nillable="true"/>
  </sequence>
</complexType>

The minOccurs value is 0 to allow for the element to be removed from the XML. This causes the value of the corresponding attribute in the OBJECT to be set to null at run time. The nillable value is true to allow empty elements to appear in the XML, annotated with the xsi:nil attribute, to indicate that the value of the element is null. Again, the db:index attribute is not used.

Note the use of a restriction on the VARCHAR2 attribute. The length is known from the declaration of the attribute in the OBJECT.

Complex User-Defined Types

User-defined types can be defined in arbitrarily complex ways. An OBJECT can contain attributes whose types are defined as any of the aforementioned user-defined types. This means that the type of an attribute in an OBJECT can be another OBJECT, VARRAY or a nested table, and so on. The base type of a VARRAY or a nested table can also be an OBJECT. Allowing the base type of a collection to be another collection supports multidimensional collections.

Object Type Inheritance

The wizard is capable of generating a valid XSD for parameters whose types are defined using OBJECT-type inheritance. Given the following type hierarchy,

SQL> CREATE TYPE A AS OBJECT (A1 NUMBER, A2 VARCHAR2 (10)) NOT FINAL;
SQL> CREATE TYPE B UNDER A (B1 VARCHAR2 (10));

and a procedure containing a parameter, X, whose type is B,

SQL> CREATE PROCEDURE P (X IN B) AS BEGIN … END;

the wizard generates an InputParameters element for parameter X as

<element name="X" type="db:B" db:index="1" db:type="Struct" minOccurs="0" nillable="true"/>

where the definition of OBJECT type B in the XSD is generated as the following complexType.

<complexType name="B">
  <sequence>
    <element name="A1" type="decimal" db:type="NUMBER" minOccurs="0" nillable="true"/>
    <element name="A2" db:type="VARCHAR2" minOccurs="0"  nillable="true">
      ...
    </element>
    <element name="B1" db:type="VARCHAR2" minOccurs="0"  nillable="true">
      ...
    </element>
  </sequence>
</complexType>

Restrictions on the maximum length of attributes A2 and B1 are added appropriately. Notice how the OBJECT type hierarchy is flattened into a single sequence of elements that corresponds to all of the attributes in the entire hierarchy.

Object References

The wizard can also generate a valid XSD for parameters that are references to OBJECT types (for example, object references), or are user-defined types that contain an object reference somewhere in their definition. In this example,

SQL> CREATE TYPE FOO AS OBJECT (…);
SQL> CREATE TYPE BAR AS OBJECT (F REF FOO, …);
SQL> CREATE PROCEDURE PROC (X OUT BAR, Y OUT REF FOO) AS BEGIN … END;

the wizard generates complexType definitions for FOO and BAR as already indicated, except that for BAR, the element for the attribute, F, is generated as

<element name=ÓFÓ type=Ódb:FOOÓ db:type=ÓRefÓ minOccurs=Ó0Ó nillable=ÓtrueÓ/>

where together, the type and db:type attribute values indicate that F is a reference to the OBJECT type FOO.

For a procedure PROC, the following elements are generated in the OutputParameters root element of the XSD:

<element name=ÓXÓ type=Ódb:BARÓ db:index=Ó1Ó db:type=ÓStructÓ minOccurs=Ó0Ó nillable=ÓtrueÓ/>
<element name=ÓYÓ type=Ódb:FOOÓ db:index=Ó2Ó db:type=ÓRefÓ minOccurs=Ó0Ó nillable=ÓtrueÓ/>

For Y, note the value of the db:type attribute, Ref. Together with the type attribute, the element definition indicates that Y is a reference to FOO.

Note that there is a restriction on the use of object references that limits their parameter mode to OUT only. Passing an IN or IN/OUT parameter into an API that is either directly a REF or, if the type of the parameter is user-defined, contains a REF somewhere in the definition of that type, is not permitted.

Referencing Types in Other Schemas

You can refer to types defined in other schemas, provided that the necessary privileges to access them have been granted. For example, suppose type OBJ was declared in SCHEMA1:

SQL> CREATE TYPE OBJ AS OBJECT (…);

The type of a parameter in a stored procedure declared in SCHEMA2 can be type OBJ from SCHEMA1:

CREATE PROCEDURE PROC (O IN SCHEMA1.OBJ) AS BEGIN … END;

This is possible only if SCHEMA1 granted permission to SCHEMA2 to access type OBJ:

SQL> GRANT EXECUTE ON OBJ TO SCHEMA2;

If the required privileges are not granted, an error occurs when trying to create procedure PROC in SCHEMA2:

PLS-00201: identifier ÒSCHEMA1.OBJÓ must be declared

Because the privileges have not been granted, type OBJ from SCHEMA1 is not visible to SCHEMA2; therefore, SCHEMA2 cannot refer to it in the declaration of parameter O.

Value Binding

Consider the extraction of values from the XML and how the run time works given those values. The possible cases for data in the XML corresponding to the value of a parameter whose type is one of the supported primitive datatypes value are as follows:

1. The value of an element is specified (for example, <X>100</X>).

2. The value of an element is not specified (for example, <X/>).

3. The value is explicitly specified as null (for example, <X xsi:nil="true"/>)

4. The element is not specified in the XML at all.

Each case is handled differently.

In the first case, the value is taken from the XML as-is and is converted to the appropriate object according to its type. That object is then bound to its corresponding parameter during preparation of the stored procedure invocation.

In the second and third cases, the actual value extracted from the XML is null. The type converter accepts null and returns it without any conversion. The null value is bound to its corresponding parameter regardless of its type. Essentially, this is the same as passing null for parameter X.

The fourth case has two possibilities. The parameter either has a default clause or it does not. If the parameter has a default clause, then the parameter is completely excluded from the invocation of the stored procedure. This allows the default value to be used for the parameter. On the other hand, if the parameter does not have a default clause, then the parameter is included in the invocation of the procedure. A null value is bound to the parameter by default:

SQL> CREATE PROCEDURE PROC (X IN INTEGER DEFAULT 0) AS BEGIN … END;

Here, no value is bound to the parameter. In fact, the parameter is completely excluded from the invocation of the stored procedure. This allows the value of 0 to default for parameter X.

To summarize, the following PL/SQL is executed in each of the four cases:

1. "BEGIN PROC (X=>?); END;" - X = 100

2. "BEGIN PROC (X=>?); END;" - X = null

3. "BEGIN PROC (X=>?); END;" - X = null

4. There are two possibilities:

5. 1. "BEGIN PROC (); END;" - X = 0 (X has a default clause)

   2. "BEGIN PROC (X=>?); END;" - X = null (X does not have a default clause)

With the exception of default clause handling, these general semantics also apply to item values of a collection or attribute values of an OBJECT whose types are one of the supported primitive datatypes. The semantics of <X/> when the type is user-defined are, however, quite different.

For a collection, whether it is a VARRAY or a nested table, the following behavior can be expected given a type definition such as

SQL> CREATE TYPE ARRAY AS VARRAY (5) OF VARCHAR2 (10);

and XML for a parameter, X, which has type ARRAY, that appears as follows:

<X>
    <X_ITEM xsi:nil="true"/>
    <X_ITEM>Hello</X_ITEM>
    <X_ITEM xsi:nil="true"/>
    <X_ITEM>World</X_ITEM>
</X>

The first and third elements of the VARRAY are set to null. The second and fourth are assigned their respective values. No fifth element is specified in the XML; therefore, the VARRAY instance has only four elements.

Given an OBJECT definition such as

SQL> CREATE TYPE OBJ AS OBJECT (A INTEGER, B INTEGER, C INTEGER);

and XML for a parameter, X, which has type OBJ, that appears as

<X>
    <A>100</A>
    <C xsi:nil="true"/>
</X>

the behavior is that the value of 100 is assigned to attribute A and null is assigned to attributes B and C. Because there is no element in the instance XML for attribute, B, a null value, is assigned.

The second case, <X/>, behaves differently if the type of X is user-defined. Rather than assigning null to X, an initialized instance of the user-defined type is created and bound instead.

In the preceding VARRAY example, if <X/> or <X></X> is specified, the value bound to X is an empty instance of the VARRAY. In PL/SQL, this is equivalent to calling the type constructor and assigning the value to X. For example,

X := ARRAY();

Similarly, in the preceding OBJECT example, an initialized instance of OBJ, whose attribute values have all been null assigned, is bound to X. Like the VARRAY case, this is equivalent to calling the type constructor. For example,

X := OBJ(NULL, NULL, NULL);

To specifically assign a null value to X when the type of X is user-defined, the xsi:nil attribute must be added to the element in the XML, as in <X xsi:nil="true"/>.

Datatype Conversions

This section describes the conversion of datatypes such as CLOB, DATE, TIMESTAMP, and binary datatypes including RAW, LONG RAW and BLOB.

For CLOB parameters, a temporary CLOB is first created. The data extracted from the XML is then written to it before binding the CLOB to its corresponding parameter. The temporary CLOB is freed when the interaction completes. For other character types, such as CHAR and VARCHAR2, the data is simply extracted and bound as necessary. Note that it is possible to bind an XML document to a CLOB (or VARCHAR2 if it is large enough). However, appropriate substitutions for <, >, and so on, must first be made (for example, < for < and > for >).

Note that the XML schema type, dateTime, represents both DATE and TIMESTAMP. This means that the XML values for both datatypes must adhere to the XML schema representation for dateTime. Therefore, a simple DATE string, 01-JAN-05, is invalid. XML schema defines dateTime as YYYY-MM-DDTHH:mm:ss. Therefore, the correct DATE value is 2005-01-01T00:00:00.

Data for binary datatypes must be represented in a human readable manner. The chosen XML schema representation for binary data is base64Binary. The type converter uses the javax.mail.internet.MimeUtility encode and decode APIs to process binary data. The encode API must be used to encode all binary data into base64Binary form so that it can be used in an XML file. The type converter uses the decode API to decode the XML data into a byte array. This is then bound either directly, as is the case with RAW and LONG RAW parameters, or is used to create a temporary BLOB, which is then bound to its associated BLOB parameter. The temporary BLOB is freed when the interaction completes.

Conversions for the remaining datatypes are straightforward and require no additional information.

Datatype Conversions

Conversions of the data retrieved are straightforward. However, BLOB, CLOB (and other character data), as well as RAW and LONG RAW conversions, require special attention.

When a CLOB is retrieved, the entire contents of that CLOB are written to the corresponding element in the generated XML. Standard DOM APIs are used to construct the XML. This means that character data, as for types like CLOB, CHAR, and VARCHAR2, is massaged as needed to make any required substitutions so that the value is valid and can be placed in the XML for subsequent processing. Therefore, substitutions for < and >, for example, in an XML document stored in a CLOB are made so that the value placed in the element within the generated XML for the associated parameter is valid.

Raw data, such as for RAW and LONG RAW types, is retrieved as a byte array. For BLOBs, the BLOB is first retrieved, and then its contents are obtained, also as a byte array. The byte array is then encoded using the javax.mail.internet.MimeUtility encode API into base64Binary form. The encoded value is then placed in its entirety in the XML for the corresponding element. The MimeUtility decode API must be used to decode this value back into a byte array.

Conversions for the remaining datatypes are straightforward and require no additional information.

Null Values

Elements whose values are null appear as empty elements in the generated XML and are annotated with the xsi:nil attribute. This means that the xsi namespace is declared in the XML that is generated. Generated XML for a procedure PROC, which has a single OUT parameter, X, whose value is null, looks as follows:

<db:OutputParameters … xmlns:xsi=Óhttp://www.w3.org/2001/XMLSchema-instanceÓ>
    <X xsi:nil=ÓtrueÓ/>
</db:OutputParameters>

The db namespace is also declared (that is, xmlns:db="..."). Note that XML elements for parameters of any type (including user-defined types) appear this way if their value is null.

Function Return Values

The return value of a function is treated as an OUT parameter at position 0 whose name is the name of the function itself. For example,

CREATE FUNCTION FACTORIAL (X IN INTEGER) RETURN INTEGER AS
BEGIN
    IF (X <= 0) THEN RETURN 1;
    ELSE RETURN FACTORIAL (X - 1);
    END IF;
END;

An invocation of this function with a value of 5, for example, results in a value of 120 and appears as <FACTORIAL>120</FACTORIAL> in the XML generated in OutputParameters.

Support for REF CURSOR

Neither the design-time nor run-time components support REF CURSOR types directly. The solution is to use a collection of an OBJECT type. Because the number of rows returned by a REF CURSOR is usually unknown, it is best to use a nested table as the collection type. This solution involves using a Java stored procedure to convert a ResultSet into an instance of the declared collection type. A sample tutorial illustrating this is provided in the following directory:

Oracle_Home\integration\orabpel\samples\tutorials\122.DBAdapter\ResultSetConverter

Support for PL/SQL Boolean, PL/SQL Record, and PL/SQL Table Types

The wizard provides a mechanism that detects when these types are used and then invokes Oracle JPublisher to generate the necessary wrappers automatically. Oracle JPublisher generates two SQL files, one to create schema objects, and another to drop them. The SQL that creates the schema objects is automatically executed from within the wizard to create the schema objects in the database schema before the XSD is generated. For example, suppose the following package specification is declared:

CREATE PACKAGE PKG AS
  TYPE REC IS RECORD (X NUMBER, Y VARCHAR2 (10));
  TYPE TBL IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  PROCEDURE PROC (R REC, T TBL, B BOOLEAN);
END;

Figure 4-35 shows the step in the wizard that is displayed when procedure PROC from package PKG is selected.

Figure 4-35 Specifying a Stored Procedure in the Adapter Configuration Wizard

Description of "Figure 4-35 Specifying a Stored Procedure in the Adapter Configuration Wizard"

As Figure 4-35 shows, the original procedure name is fully qualified, PKG.PROC. The type of parameter, R, is the name of the RECORD. The type of T is the name of the TABLE. The type of B is BOOLEAN. The name of the wrapper package that is generated is derived from the service name, bpel_ServiceName (for example, bpel_UseJPub). This is the name of the generated package that contains the wrapper procedure. The check box can be used to force the wizard to overwrite an existing package when the schema objects are created.

Clicking Next reveals the Finish page of the wizard, as shown in Figure 4-36.

Figure 4-36 Defining a Database Adapter Service: Finish Page

Description of "Figure 4-36 Defining a Database Adapter Service: Finish Page"

The contents of this page describe what the wizard has detected and what actions are performed when the Finish button is clicked. The following summarizes the contents of this page:

1. The name of the generated WSDL is UseJPub.wsdl.

2. Two SQL scripts are created and added to the BPEL process project:

   1. BPEL_USEJPUB.sql – Creates the schema objects.

   2. BPEL_USEJPUB_drop.sql – Drops the schema objects.

3. The name of the generated XSD is SCOTT_USEJPUB_PKG-24PROC.xsd.

When you click Finish, Oracle JPublisher is invoked to generate the SQL files and load the schema objects into the database. The process of generating wrappers may take quite some time to complete. Processing times for wrappers that are generated in the same package usually require less time after an initial wrapper has been generated for another procedure within the same package.

The following user-defined types are generated to replace the PL/SQL types from the original procedure:

SQL> CREATE TYPE PKG_REC AS OBJECT (X NUMBER, Y VARCHAR2 (10));
SQL> CREATE TYPE PKG_TBL AS TABLE OF NUMBER;

The naming convention for these types is OriginalPackageName_OriginalTypeName. BOOLEAN is replaced by INTEGER in the wrapper procedure.

Acceptable values for the original BOOLEAN parameter now that it is an INTEGER are 1 for true and 0 for false. Any value other than 1 is considered false. The generated wrapper procedure uses APIs from the SYS.SQLJUTL package to convert from INTEGER to BOOLEAN and vice-versa.

A new wrapper package called BPEL_USEJPUB is created that contains the wrapper for procedure PROC, called PKG$PROC, as well as conversion APIs that convert from the PL/SQL types to the user-defined types and vice-versa. If the original procedure is a root-level procedure, the name of the generated wrapper procedure is TOPLEVEL$OriginalProcedureName.

The generated XSD represents the signature of wrapper procedure PKG$PROC and not the original procedure. The name of the XSD file is URL-encoded, which replaces $ with -24.

Note the naming conventions for the generated artifacts:

• The service name is used in the names of the WSDL and SQL files. It is also used as the name of the wrapper package.

• The name of the generated XSD is derived from the schema name, service name, and the original package and procedure names.

• The name of a user-defined type is derived from the original package name and the name of its corresponding PL/SQL type.

• The name of the wrapper procedure is derived from the original package and procedure names. TOPLEVEL$ is used for root-level procedures.

The name of the generated wrapper package is limited to 30 characters. The name of the wrapper procedure is limited to 29 characters. If the names generated by Oracle JPublisher are longer than these limits, they are truncated.

When the PartnerLink that corresponds with the service associated with the procedure is invoked, the generated wrapper procedure is executed instead of the original procedure.

Default Clauses in Wrapper Procedures

If a procedure contains a special type that requires a wrapper to be generated, the default clauses on any of the parameters are not carried over to the wrapper. For example, consider

SQL> CREATE PROCEDURE NEEDSWRAPPER (
        >     B BOOLEAN DEFAULT TRUE, N NUMBER DEFAULT 0) IS BEGIN … END;

Assuming that this is a root-level procedure, the signature of the generated wrapper procedure is

TOPLEVEL$NEEDSWRAPPER (B INTEGER, N NUMBER)

The BOOLEAN type has been replaced by INTEGER. The default clauses on both parameters are missing in the generated wrapper. Parameters of generated wrapper procedures never have a default clause, even if they did in the original procedure.

In this example, if an element for either parameter is not specified in the instance XML, then a null value is bound to that parameter during the invocation of the wrapper procedure. The default value of the parameter that is specified in the original procedure is not used.

To address this, the generated SQL file that creates the wrapper must be edited, restoring the default clauses to the parameters of the wrapper procedure. The wrapper and any additional schema objects must then be reloaded into the database schema. After editing the SQL file, the signature of the wrapper procedure is

TOPLEVEL$NEEDSWRAPPER (B INTEGER DEFAULT 1, N NUMBER DEFAULT 0)

For BOOLEAN parameters, the default value for true is 1 and the default value for false is 0.

As a final step, the XSD generated for the wrapper must be edited. A special attribute must be added to elements representing parameters that now have default clauses. Add db:default="true" to each element representing a parameter that now has a default clause. For example,

<element name="B" … db:default="true" …/>
<element name="N" … db:default="true" …/>

This attribute is used at run time to indicate that, if the element is missing from the instance XML, the corresponding parameter must be omitted from the procedure call. The remaining attributes of these elements remain exactly the same.