When the Oracle Database Adapter is used to poll for database events (usually an INSERT operation on an input table) and initiate a process, it is called an exposed service. In an Oracle BPEL process, it is a partner link tied to a Receive activity. The expression inbound (from database into SOA) is commonly used.

When the Oracle Database Adapter is used to invoke a one-time DML statement, such as INSERT or SELECT, in a Mediator component or a SOA composite, it is called a service reference. In an Oracle BPEL process, it is a partner link tied to an Invoke activity. The expression outbound (from SOA out to the database) is used.

Oracle Database Adapter ROWID support now allows you to choose the ROWID as the primary key for certain supported operations.

When you import a table with no primary key constraint, you are prompted to select the set of columns that can be used to uniquely identify a row (see Figure 9-12). For an integration scenario this might require more knowledge about the target schema than is had, and can introduce errors. For example, picking a non-unique column here can lead to operations updating multiple unintended rows, or Selects returning the some rows twice and not others.

On an Oracle database you can choose to use the ROWID pseudo column as the primary key, which is less error prone. In some case, it is faster than a regular index as ROWID is the direct physical address.

A ROWID is a pseudo-column on Oracle tables. A pseudo-column is a column that cannot be updated directly by the user, is not a column created by the user, and does not appear in any column meta-data views or is returned from a select * query.

Use of ROWID has certain restrictions. In such cases, the option are grayed out on the user interface and unselectable.

ROWID can only be used for operations which either do not need to uniquely identify a row (Insert, Select) or which only need to identify rows it has already read in the same transaction (most polling operations). Because the physical address has no meaning external to the database and is not part of the row (and the value is stable within a transaction), it cannot be used with operations which must uniquely identify a row using the columns of the row itself (Merge, Delete, Select by primary key).

ROWID can only be used in conjunction with a single table, as relationships between tables require rows to have explicit primary keys.

ROWID support is not applicable to Pure SQL or Stored procedures.

When tables are initially imported into the Adapter Configuration Wizard, a TopLink direct-to-field mapping corresponding to each field in the database is created. Consider the schemas shown in Figure 9-15 and Figure 9-16:

Figure 9-15 EMPLOYEE Schema

Description of "Figure 9-15 EMPLOYEE Schema"

Figure 9-16 ADDRESS Schema

Description of "Figure 9-16 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 appears, as shown in the following example:

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.

When relationships are auto created, the one-to-many relationship is from the table without the foreign key. However, you can declare this mapping, which is technically 1-many, as a 1-1. For that, choose 1-1 (foreign key on target).

Not all tables imported are in the third normal form (3NF). In rare cases, you may have two or more tables which share the same primary key but no separate foreign key columns exist. It is recommended to create 1-1 (foreign key on target) relationships from the root table to all related tables. The reason is two fold. First, if you were to declare the primary key on the root as a foreign key (1-1, foreign key on source), then that mapping would be removed, so you would not see the primary key in the root record, only in the child record. Second, a foreign key can only point to a single table. Once you declare a column to be part of a foreign key, it is removed, so it cannot be used again in a new relationship. Creating a 1-1 (foreign key on source) on the root table not only makes the primary key column disappear but prevents you from joining the root table to the remaining tables.

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

In the deployment descriptor (weblogic-ra.xml file), you must set the xADataSourceName parameter. Additionally, the referenced DataSource must be configured for transaction participation by creating a data source in Oracle WebLogic Server Console.

You must create a data source and choose a XA data sources from the list.

For information about the recommended setting for non-XA and XA data sources used by Oracle JCA Adapters, see Recommended Setting for Data Sources Used by Oracle JCA Adapters.

You cannot edit the data-sources.xml file in the Oracle WebLogic Server. You must create a data source by using the Oracle WebLogic Server Administration Console, as mentioned in Creating a Data Source.

Once both the Oracle Database Adapter invokes participate in global transactions, to commit or rollback as a unit, they must be participating in the same global transaction. In BPEL, this requires the understanding of where the transaction boundaries are, at what points does a checkpoint have to write to the dehydration store, commit the current global transaction, and start a new one.

The transaction boundaries in a BPEL process occur either before a Receive activity or Wait activity, or before an onMessage or Pick activity. This can also occur when invoking a synchronous child BPEL process, unless the bpel.config.transaction property is set on the partnerlink, as shown in the following code sample.

<property name="bpel.config.transaction">required</property>

Otherwise, the parent process is broken into two transactions and the child process runs in its own transaction.

Finally, even if both Oracle Database Adapter invokes participate in the same global transaction, the failure of either invoke may not cause the global transaction to rollback.

The only cases where a failure can actually cause a global rollback are:

• A Oracle Database Adapter operation that inserts/updates multiple tables as part of one invoke fails after having succeeded in some writes but not others. In this case, the Oracle Database Adapter marks the global transaction as rollback only, because the invoke operation was not atomic and a commit could cause data corruption.

• The invoke retries multiple times in a database down scenario, until the global transaction times out and is rolled back.

• An explicit bpelx:rollback fault is thrown from within the BPEL process.

To make two Oracle Database Adapter invokes commit or roll back as a unit requires the following: both Oracle Database Adapter invokes must be configured to participate in global transactions, both invokes must participate in the same global transaction, and the failure of either invoke must cause the global transaction to rollback.

You can follow one of the procedures for commit within Stored Procedure when calling from Database Adapter 

Follow either of the three options.

• Commit within the stored procedure.

  1. Create a non-XA datasource.

  2. Deselect Supports Global Transaction.

  3. Refer to the newly created non-XA datasource from DBAdapter JNDI property. For example DataSourceName and not XADataSourceName.

• If your datasource supports XA, remove the commit from stored procedure. The Weblogic transaction manager manages the transaction boundaries for such scenarios.

• Use Pragma AUTONOMOUS_TRANSACTION in stored procedure. When you do that the commits within the stored procedure happen outside of the global transaction. For example, CREATE PROCEDURE MY_STORED_PROC AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN.

The first best practice for multiple Oracle Database Adapter process instances deployed to multiple Oracle BPEL PM or Mediator nodes is to use the Adapter Configuration Wizard to set both the Distributed Polling check box in the Adapter Configuration Wizard and to set MaxTransactionSize.

Increase concurrency by setting the adapter _db.JCA property NumberOfThreads.

On an Oracle database, doing this automatically uses the syntax SELECT FOR UPDATE SKIP LOCKED. With a Microsoft SQLServer database the syntax is WITH (UPDLOCK,READPAST)

With SELECT FOR UPDATE SKIP LOCKED, concurrent threads each try to select and lock the available rows, but the locks are only obtained on fetch. If an about to be fetched row is locked, the next unlocked row are locked and fetched instead. If many threads all execute the same polling query at the same time, they should all relatively quickly obtain a disjoint subset of unprocessed rows.

The next best practice for multiple Oracle Database Adapter process instances deployed to multiple Oracle BPEL PM or Mediator nodes is to tune on a single node first.

For an Oracle Database Adapter intensive process, such as a database-database integration, performance can be improved by a factor 10 or 100 just by tuning on a single Java Virtual Machine (JVM), scaling |NumberOfThreads|, and setting high values for MaxTransactionSize and MaxRaiseSize.

As Distributed Polling First Best Practice: SELECT FOR UPDATE (SKIP LOCKED) describes, there may be times where it is best to improve performance on a single node, and then optionally do fan-out to multiple nodes in a cluster. Relying on concurrency control features of the database such as locking can be great, but these are often designed more for preserving data integrity than for high performance scalability.

Cases where it may be best to do polling on a single node in the cluster include using the non-intrusive Sequencing Polling strategy, polling large un-indexed tables, or using a non-Oracle back-end database that does not provide high concurrency locks like skip locks.

You can also refer to Singleton (Active/Passive) Inbound Endpoint Lifecycle Support Within Adapters.

The Oracle 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 Adapter Configuration 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 TopLink Workbench. By pairing the Oracle Database Adapter with a transformation step, you can map any relational schema to any XML schema.

When executing a SQL select statement against multiple related tables there are the following three methods to build the SQL. These ways relate to how to pull in the detail records when the query is against the master record:

• Using Relationship Queries (TopLink Default)

• Twisting the Original Select (TopLink Batch-Attribute Reading)

• Returning a Single Result Set (TopLink Joined-Attribute Reading)

• Comparison of the Methods Used for Querying over Multiple Tables

The following sections contain an outline of these three methods and their comparison. When selecting rows from a single table there are no issues different from selecting from multiple tables.

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 MERGE 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.

This section includes the following topics:

• Merge

• querybyExample

Merge

Merge first reads the corresponding records in the database, calculates any changes, and then performs a minimal update. INSERT, UPDATE, and MERGE make the most sense when you are thinking about a single row and a single table. However, your XML can contain complex types and map to multiple rows on multiple tables. Imagine a DEPT with many EMPS, each with an ADDRESS. In this case, you must calculate which of possibly many rows have changed and which to insert, update, or delete. If a specific row did not change or only one field changed, then the DML calls is minimal.

querybyExample

Unlike the SELECT operation, queryByExample does not require a selection criteria to be specified at design time. Instead, for each invoke, a selection criteria is inferred from an exemplary input XML record.

For instance, if the output xmlRecord is an employee record, and the input is a sample xmlRecord with lastName = "Smith", then on execution, all employees with a last name of Smith are returned.

A subset of queryByExample is to query by primary key, which can be implemented by passing in sample XML records where only the primary key attributes are set.

Use queryByExample when you do not want to create a query using the visual query builder and want the flexibility of allowing the input record to share the same XML schema as the output records.

The queryByExample operation is slightly less performant because a new SELECT must be prepared for each execution. This is because the attributes that are set in the example XML record can vary each time, and therefore the selection criteria vary.

Input xmlRecord:

<Employee>
      <id/>
      <lastName>Smith</lastName>
</Employee>

Output xmlRecord:

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

The inbound receive enables 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 the SCA runtime. The strategy 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 non-intrusive. Each strategy employs a different solution for the problem of what to do after reading a row or event so it is not picked 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 operations that you can perform after the data is read from the database. This section also discusses the strategies and factors to help you determine which strategy to employ for a particular situation:

• Delete the Row(s) that were Read

• Update a Field in the [Table_Name] Table (Logical Delete)

• Update a Sequencing Table

• Update an External Sequencing Table on a Different Database

• Control Table Strategy

• Update a Sequencing File

Delete the Row(s) that were Read

Choose this operation to employ the physical delete polling strategy. This operation polls the database table for records and deletes them after processing. Use this strategy to capture events related to INSERT operations and cannot capture database events related to DELETE and UPDATE operations on the parent table. This strategy cannot be used to poll child table events. This strategy allows multiple adapter instances to go against the same source table. There is zero data replication.

Preconditions: You must have deletion privileges on the parent and associated child tables to use the delete polling strategy. Table 9-8 describes the requirements for using the delete polling strategy.

Configuration: You can configure the delete polling strategy to delete the top-level row, to cascade all, or to cascade on a case-by-case basis. This strategy enables deleting only the parent rows and not the child rows, cascaded deletes, and optional cascaded deletes, determined on a case-by-case basis. You can configure the polling interval for performing an event publish at design time.

Delete Cascade Policy: The optional advanced configuration is to specify the cascade policy of the DELETE operation. For instance, after polling for an employee with an address and many phone numbers, the phone numbers are deleted because they are privately owned (default for one-to-many), but not the address (default for one-to-one). This can be altered by configuring or_mappings.xml, as in the following example:

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

You can also configure the activation itself to delete only the top level (master row) or to delete everything.

A receive operation appears in an inbound JCA as follows:

Example - Receive Operation Inbound JCA

<connection-factory location="eis/DB/Connection1"
            UIConnectionName="Connection1" adapterRef=""/>
  <endpoint-activation portType="dd_ptt" operation="receive">
    <activation-spec className="oracle.tip.adapter.db.
                      DBActivationSpec">
      <property name="DescriptorName" value="dd.Emp"/>
      <property name="QueryName" value="ddSelect"/>
      <property name="MappingsMetaDataURL" value="dd-or-mappings.xml"/>
      <property name="PollingStrategy" 
                         value="LogicalDeletePollingStrategy"/>
      <property name="MarkReadColumn" value="STATUS"/>
      <property name="MarkReadValue" value="PROCESSED"/>
      <property name="MarkReservedValue" value="RESERVED-1"/>
      <property name="MarkUnreadValue" value="UNPROCESSED"/>
      <property name="PollingInterval" value="5"/>
      <property name="MaxRaiseSize" value="1"/>
      <property name="MaxTransactionSize" value="10"/>
      <property name="ReturnSingleResultSet" value="false"/>
    </activation-spec>
  </endpoint-activation>
</adapter-config>

Update a Field in the [Table_Name] Table (Logical Delete)

Choose this operation to employ the logical delete polling strategy. This strategy involves updating a special field on each row processed and updating the WHERE clause at runtime to filter out processed rows.

It mimics logical delete, wherein applications rows are rarely deleted but instead a status column isDeleted is set to true. The status column and the read value must be provided, but the modified WHERE clause and the post-read update are handled automatically by the Oracle Database Adapter.

Preconditions: You must have the logical delete privilege or a one-time alter schema (add column) privilege on the source table. Table 9-9 describes the requirements for using the logical delete polling strategy.

Configuration: The logical delete polling strategy requires minimal configuration. You must specify the mark read column and the value that indicates a processed record.

A receive operation appears in an inbound WSDL as follows:

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

Given the configuration for logical delete, the Oracle Database Adapter appends the following WHERE clause to every polling query:

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

Database Configuration: A status column on the table being polled must exist. If it does not exist already, you can add one to an existing table.

Support for Polling for Updates: Given that rows are not deleted with each read, it is possible to repetitively read a row multiple times. You must add a trigger to reset the mark read field whenever a record is changed, as follows:

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

Support for Concurrent Access Polling: Just as a single instance should never process an event more than once, the same applies to a collection of instances. Therefore, before processing a record, an instance must reserve that record with a unique value. Again, the status column can be used:

<operation name="receive">
   <jca:operation
      ActivationSpec="oracle.tip.adapter.db.DBActivationSpec"
         …
      PollingStrategyName="LogicalDeletePollingStrategy"
      MarkReadField="STATUS"
      MarkUnreadValue="UNPROCESSED"
      MarkReservedValue="RESERVED${IP-2}-${weblogic.Name-1}-${instance}"
      MarkReadValue="PROCESSED"

The polling query instead appears, as shown in the following example:

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

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

The after-read UPDATE is faster because it can update all:

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

Update a Sequencing Table

Choose this operation to employ the sequencing table: last-read Id strategy. This polling strategy involves using a helper table to remember a sequence value. The source table is not modified; instead, rows that have been read in a separate helper table are recorded. A sequence value of 1000, for example, means that every record with a sequence less than that value have been processed. Because many tables have some counter field that is always increasing and maintained by triggers or the application, this strategy can often be used for noninvasive polling. No field on the processed row must be modified by the Oracle Database Adapter.

Native sequencing with a pre-allocation size of 1 can ensure that rows are inserted with primary keys that are always increasing over time.

This strategy is also called a nondestructive delete because no updates are made to the source rows, and you can use a sequencing strategy such as the sequence field to order the rows in a sequence for processing.

When the rows are ordered in a line, the Oracle Database Adapter knows which rows are processed and which are not with a single unit of information.

Preconditions: You must have a sequencing table or create table privilege on the source schema. The source table has a column that is monotonically increasing with every INSERT (an Oracle native sequenced primary key) or UPDATE (the last-modified timestamp). Table 9-10 describes the requirements for using the sequencing polling strategy.

Configuration: A separate helper table must be defined. On the source table, you must specify which column is ever increasing.

Example - Specifying Sequencing

CREATE TABLE Employee (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    Modified_Date DATE default sysdate,
   );

<adapter-config name="ReadS" adapter="Database Adapter" 
xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">
    <connection-factory location="eis/DB/DBConnection1" 
UIConnectionName="DBConnection1" adapterRef=""/>
  <endpoint-activation portType="ReadS_ptt" operation="receive">
    <activation-spec className="oracle.tip.adapter.db.DBActivationSpec">
      <property name="DescriptorName" value="ReadS.PerfMasterIn"/>
      <property name="QueryName" value="ReadSSelect"/>
      <property name="MappingsMetaDataURL" value="ReadS-or-mappings.xml"/>
      <property name="PollingStrategy" value="SequencingPollingStrategy"/>
      <property name="SequencingTable" value="SEQUENCING_HELPER"/>
      <property name="SequencingColumn" value="MODIFIED_DATE"/>
      <property name="SequencingTableKeyColumn" value="TABLE_NAME"/>
      <property name="SequencingTableValueColumn" value="LAST_READ_DATE"/>
      <property name="SequencingTableKey" value="EMPLOYEE"/>
      <property name="PollingInterval" value="60"/>
      <property name="MaxRaiseSize" value="1"/>
      <property name="MaxTransactionSize" value="10"/>
      <property name="ReturnSingleResultSet" value="false"/>
    </activation-spec>
  </endpoint-activation>
</adapter-config>

Database Configuration:

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

Polling for Updates: In the preceding example, the polling is for new objects or updates, because every time an object is changed, the modified time is updated.

A sample trigger to set the modified time on every insert or update is as follows:

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

Using a Sequence Number: A sequence number can be used for either insert or update polling. Native sequencing returns monotonically increasing primary keys, when an increment by 1 is used. You can also use the sequence number of a materialized view log.

Update an External Sequencing Table on a Different Database

Choose this operation to employ the sequencing table: last updated strategy. This polling strategy involves using a helper table to remember a last_updated value. A last_updated value of 2005-01-01 12:45:01 000, for example, means that every record last updated at that time or earlier have been processed.

Because many tables have rows with a last_updated or creation_time column maintained by triggers or the application, this strategy can often be used for noninvasive polling. Fields on the processed row never require modification by the Oracle Database Adapter.

This strategy is also called a nondestructive delete because no updates are made to the source rows, and you can use a sequencing strategy such as the last_updated field to order the rows in a sequence for processing. When the rows are ordered in a line, the Oracle Database Adapter knows which rows are processed and which are not with a single unit of information.

See Update a Sequencing Table for information about preconditions and configuration.

Update a Sequencing File

This strategy works similarly to Update an External Sequencing Table on a Different Database. The only difference is that the control information is stored in a file instead of a table.

Control Table Strategy

Choose this operation to employ the control table polling strategy. This polling strategy involves using a control table to store the primary key of every row that has yet to be processed. With a natural join between the control table and the source table (by primary key), polling against the control table is practically the same as polling against the source table directly. However, an extra layer of indirection allows the following:

• Destructive polling strategies such as the delete polling strategy can be applied to rows in the control table alone while shielding any rows in the source table.

• Only rows that are meant to be processed have their primary key appear in the control table. You can use information that is not in the rows themselves to control which rows to process (a good WHERE clause may not be enough).

• The entire row is not copied to a control table, and any structure under the source table, such as detail rows, can also be raised without copying.

Streams and materialized view logs make excellent control tables.

Preconditions: You must have the create/alter triggers privilege on the source table. Table 9-11 describes the requirements for using the control table polling strategy.

Using triggers, whenever a row is modified, an entry is added to a control table, containing the name of the master table, and the primary keys. At design time, the control table is defined to be the root table, with a one-to-one mapping to the master table, based on the matching primary keys. The control table can contain extra control information, such as a time stamp, and operation type (INSERT, UPDATE, and so on).

The delete polling strategy is useful with this setup. It is important to keep the control table small, and if the option shouldDeleteDetailRows="false" is used, then only the control rows are deleted, giving you a nondestructive delete (the DELETE is not cascaded to the real tables).

It is possible to reuse the same control table for multiple master tables. In TopLink, you can map the same table to multiple descriptors by mapping the control table as one abstract class with multiple children. Each child has a unique one-to-one mapping to a different master table. The advantage of this approach is that you can specify for each child a class indicator field and value so that you do not require an explicit WHERE clause for each polling query.

The following are sample triggers for polling for changes both to a department table and any of its child employee rows:

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;

1. Proceed to run -> dcomcnfg
2. Then, proceed to component servies->Mycomp->DTC->LocalDTC->Properties ( and right-click Properties.)
3. Then, proceed to Security-> and Select Enable xa transaction
4. Finally, restart all related services (trxn, sqlserver)

This section includes the following topics:

• IBM DB2 Driver

• JT400 Driver (AS400 DB2)

• IBM Universal Driver

1. Drag and drop Database Adapter from the Service Adapters list to the Exposed Services swim lane in the composite.xml page.

   The Adapter Configuration Wizard is displayed.

2. In the Service Name field, enter a service name, and then click Next. The Service Connection page is displayed.

   You associate a connection with the service. A database connection is required to configure the adapter service. Select an existing connection from the list or create a new connection.

   Figure 9-32 Setting the Database Connection in the Adapter Configuration Wizard

   
   Description of "Figure 9-32 Setting the Database Connection in the Adapter Configuration Wizard"
3. Click Next. The Operation Type page is displayed.
4. For the Operation Type, select Call a Stored Procedure or Function.

   Figure 9-33 Calling a Stored Procedure or Function in the Adapter Configuration Wizard

   
   Description of "Figure 9-33 Calling a Stored Procedure or Function in the Adapter Configuration Wizard"
5. Click Next. The Specify Stored Procedure page is displayed. This is where you specify a stored procedure or function.

   Figure 9-34 The Specify Stored Procedure Page

   
   Description of "Figure 9-34 The Specify Stored Procedure Page"
6. 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, 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.

   Figure 9-35 Searching for a Procedure or Function

   
   Description of "Figure 9-35 Searching for a Procedure or Function"

   Select a schema from the list or select <Default Schema>. A list of the available procedures is 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 9-36 shows how you can select the FACTORIAL function. The Arguments tab displays the parameters of the function, including their names, type, mode (IN, IN/OUT or OUT) and the numeric position of the parameter in the definition of the procedure. The return value of a function has no name and is always an OUT parameter at position zero (0).

   Figure 9-36 Viewing the Arguments of a Selected Procedure

   
   Description of "Figure 9-36 Viewing the Arguments of a Selected Procedure"

   Figure 9-37 shows how the Source tab displays the code that implements the function. Text that matches the name of the function is highlighted.

   Figure 9-37 Viewing the Source Code of a Selected Procedure

   
   Description of "Figure 9-37 Viewing the Source Code of a Selected Procedure"
7. Click OK after selecting a procedure or function. Information about the API is displayed. Click Back or Browse to make revisions.

   Figure 9-38 Viewing Procedure or Function Details in the Adapter Configuration Wizard

   
   Description of "Figure 9-38 Viewing Procedure or Function Details in the Adapter Configuration Wizard"
8. Click Next. If the stored procedure or function has an output parameter of type row set (REF CURSOR on Oracle Database), as Figure 9-39 shows, you can define a strongly or weakly typed XSD for this ref cursor.

   Figure 9-39 Viewing Procedure or Function Details in the Adapter Configuration Wizard: Row Set Type

   
   Description of "Figure 9-39 Viewing Procedure or Function Details in the Adapter Configuration Wizard: Row Set Type"

   For more information, see:

   • Row Set Support Using a Strongly Typed XSD

   • Row Set Support Using a Weakly Typed XSD

9. Click Next. The Advanced Options page is displayed, as shown in Figure 9-40. Enter any advanced options, such as the JDBC QueryTimeout value. Other options include retry parameters, such as the number of retry attempts and the interval between them.

   Figure 9-40 The Advanced Options Page

   
   Description of "Figure 9-40 The Advanced Options Page"
10. After specifying all options, click Next, and then click Finish to complete the Adapter Configuration Wizard.

    When you have finished using the Adapter Configuration Wizard, three files are added to the existing project:

    • servicename.wsdl (for example, Factorial.wsdl)

    • service_name_db.jca (for example, Factorial_db.jca)

    • schema_package_procedurename.xsd (for example, SCOTT_FACTORIAL.xsd)

In the paragraphs that follow, the operation name, Factorial, and procedure name, Factorial, are taken from an example cited previously (see Figure 9-38). The generated WSDL imports the XSD file.

<types>
  <schema xmlns="http://www.w3.org/2001/XMLSchema">
    <import namespace="http://xmlns.oracle.com/pcbpel/adapter/db/SCOTT/FACTORIAL/"
      schemaLocation="xsd/SCOTT_FACTORIAL.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 file 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 file for specifying elements only if there are any IN/OUT or OUT parameters. IN/OUT parameters appear in both root elements.

These root elements are represented in the XSD file as an unnamed complexType definition whose sequence includes one element for each parameter. If there are no IN or IN/OUT parameters, then the InputParameters root element is still created; however, complexType is empty. A comment in the XSD file indicates that there are no such parameters. An example of a 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="InputParameters"/>
</message>
<message name="args_out_msg"
  <part name="OutputParameters" element="OutputParameters"/>
</message>

The db namespace is equal to the targetNamespace of the generated XSD. 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 file.

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

<portType name="Factorial_ptt">
  <operation name="Factorial">
    <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 file. 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/Factorial/"

The root elements in the XSD file 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 file. 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 file that gets generated after the stored procedure has executed. It holds the values of any IN/OUT and OUT parameters.

The JCA file provides adapter configuration information for the service. A connection factory is specified so that the adapter run time can connect to the database, as shown in the following example. Non-managed connection properties should not be specified directly in the JCA file. Instead you should create a connection factory on the application server, and refer to it by name in the JCA file (<connection-factory location).

<connection-factory location="eis/DB/oracle" UIConnectionName="oracle"
adapterRef="">
</connection-factory>

The JNDI name, eis/DB/oracle, was earlier specified as the service connection in the Adapter Configuration Wizard.

End point properties for the interaction are also specified. The name of the schema, package, and procedure are specified, as shown in the following example. The operation name ties the JCA file back to the service WSDL.

<connection-factory location="eis/db/oracle" UIConnectionName="oracle" 
adapterRef=""/>
<endpoint-interaction portType="Factorial_ptt" operation="Factorial">
  <interaction-spec
    className="oracle.tip.adapter.db.DBStoredProcedureInteractionSpec">
      <property name="ProcedureName" value="FACTORIAL"/>
      <property name="GetActiveUnitOfWork="false"/>
  </interaction-spec>
  </output>
</endpoint-interaction>

Note the operation name and procedure name. If an explicit schema had been chosen or if the procedure had been defined in a package, then values for these properties would also be listed here.

The Adapter Configuration 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 file.

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 file 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. 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 file. 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.

collections (Varray and nested table) are not supported if they are defined inside of a PL/SQL package specification. For example:

SQL> create package pkg as
   >   type vary is varray(10) of number;
   >   type ntbl is table of varchar2(100;
   >   procedure test(v in vary, n in ntbl);
   > end;
   > /

If a user selects the test procedure in the Adapter Configuration Wizard for stored procedures, an error occurs stating that the types are not supported. However, if the vary and ntbl type definitions were defined at the root level, outside of the package, then choosing the test procedure works without issue. The supported way to use collection types (Varray and nested table) is shown in the following example:

SQL> create type vary as varray(10) of number;
SQL> create type ntbl as table of varchar2(10);
SQL> create package pkg as
   >   procedure test(v in vary, n in ntbl);
   > end;
   /

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 definition 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 file. 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 file, 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.

User-defined types can be defined in arbitrarily complex ways. An OBJECT can contain attributes whose types are defined as any of the user-defined types mentioned in the preceding section. 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.

The Adapter Configuration 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 Adapter Configuration 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 file 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.

The Adapter Configuration 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 Adapter Configuration Wizard generates complexType definitions for FOO and BAR as 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 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 file:

<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. with the type attribute, the element definition indicates that Y is a reference to FOO.

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.

You can refer to types defined in other schemas if 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.

Some user-defined object types can have a very large number of attributes. These attributes can also be defined in terms of other object types that also have many attributes. In short, one object type can become quite large depending on the depth and complexity of its definition.

Depending on the situation, many attributes of a large object type may not even be necessary. It is sometimes desirable to omit these attributes from the object's schema definition. This can be done by physically removing the unwanted XSD elements from the definition of the object type.

See the following example where a stored procedure has a parameter whose type is a complex user-defined type:

SQL> CREATE TYPE OBJ AS OBJECT (A, NUMBER, B <SqlType>, C <SqlType>, ...);
SQL> CREATE PROCEDURE PROC (O OBJ) AS BEGIN ... END;

The InputParameters root element contains a single element for the parameter, O from the API's signature. A complexType definition is to be added to the generated XSD for the object type, as shown in the following code snippet:

<complexType name="OBJ">
  <sequence>
    <element name="A" type="decimal" db:type="NUMBER" minOccurs="0"  
nillable="true"/>
    <element name="B" .../>
    <element name="C" .../>
    ...
  </sequence>
</complexType>

If attributes B and C are not required, then their element in the complexType definition of OBJ can be removed regardless of its type. Values are not required for these attributes in the instance XML. If parameter O had been an output parameter, then elements corresponding with the pruned attributes are also omitted in the generated XML.

Suppose that the type of parameter A was also a user-defined object type and that the definition of OBJ changed accordingly, as shown in the following example:

SQL> CREATE TYPE FOO AS OBJECT (X NUMBER, Y NUMBER, Z NUMBER);
SQL> CREATE TYPE OBJ AS OBJECT (A FOO, B <SqlType>, C <SqlType, ...);

In such a case, the API remains unchanged. Elements corresponding to unwanted attributes in the definition of FOO can also be removed regardless of their type. So, for example, if Y is not required, then its element in the complexType definition of FOO can be removed in the XSD file.

Pruning the XSD file in this fashion improves the runtime performance of the adapter and can significantly reduce memory consumption, as well.

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

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

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

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

4. The element is not specified in the XML file at all (for example, X = <default value>).

In the first case, the value is taken from the XML file 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 file 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 equal to 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 can be excluded from the invocation of the stored procedure. This allows the default value to be used for the parameter. If the parameter is included, then the value of the parameter is used, instead. If the parameter does not have a default clause, then the parameter must be included in the invocation of the procedure. Elements for all parameters of a function must be specified. If an element in the instance XML is missing, then the function is invoked with fewer arguments than is expected.

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 can be 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 these three cases:

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

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

3. There are two possibilities:

4. 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 a supported primitive data types. 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 file; therefore, the VARRAY instance has only four elements.

Assume 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 value 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, then 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. Similar to 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, add the xsi:nil attribute to the element in the XML file, as in

<X xsi:nil="true"/>

This section describes the conversion of data types such as CLOB, DATE, TIMESTAMP, and binary data types including RAW, LONG RAW and BLOB, and similar data types supported by third-party databases.

Microsoft SQL Server, IBM DB2, AS/400, and MySQL support binding various forms of binary and date data types to parameters of a stored procedure, as summarized in Table 9-19.

For a CLOB parameter, if the length of the CLOB parameter is less than 4 kilobytes, then the text extracted from the XML file is bound to the parameter as a String type with no further processing. If the length of the CLOB parameter is greater than 4 kilobytes or if the mode of the parameter is IN/OUT then a temporary CLOB parameter is created. The XML file data is then written to the temporary CLOB before the CLOB is bound to its corresponding parameter. The temporary CLOB parameter is freed when the interaction completes. For other character types, such as CHAR and VARCHAR2, the data is simply extracted and bound as necessary. It is possible to bind an XML document to a CLOB parameter (or VARCHAR2 if it is large enough). However, appropriate substitutions for <, >, and so on, must first be made (for example, < for < and > for >).

A few data types require special processing before their values are bound to their corresponding parameters. These include data types represented by the XML Schema types base64Binary and dateTime.

The XML schema type, dateTime, represents TIME, DATE, and TIMESTAMP. The XML values for these data types 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. Values for these parameters must be specified using this format in the instance XML.

Data for binary data types 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. The decode API is used to convert the base64Binary data into a byte array.

For a BLOB parameter, if the length of a byte array containing the decoded value is less than 2 kilobytes, then the byte array is bound to its parameter with no further processing. If the length of the byte array is greater than 2 kilobytes or if the mode of the parameter is IN/OUT, then a temporary BLOB is created. The byte array is then written to the BLOB before it is bound to its corresponding parameter. The temporary BLOB is freed when the interaction completes. For other binary data types, such as RAW and LONG RAW, the base64Binary data is decoded into a byte array and bound as necessary.

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

Conversions of data retrieved are straightforward. However, CLOB (and other character data), RAW, LONG RAW, and BLOB conversions, and conversions for similar data types supported by third-party databases, 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 file. Hence, character data, for types such as CLOB, CHAR, and VARCHAR2, is messaged as needed to make any required substitutions so that the value is valid and can be placed in the XML file 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 data 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 file for the corresponding element. The MimeUtility decode API must be used to decode this value back into a byte array.

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

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

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

XML elements for parameters of any type (including user-defined types) appear this way if their value is null.

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 OutputParameters root element in the generated XML.

All third-party databases share the same functionality for handling ResultSets. The following is a SQL Server example of an API that returns a ResultSet:

 1> create procedure foo ... as select ... from ...;
 2> go

A RowSet defined in the generated XSD represents a ResultSet. A RowSet consists of zero or more rows, each having one or more columns. A row corresponds with a row returned by the query. A column corresponds with a column item in the query. The generated XML for the API shown in the preceding example after it executes is shown in the following example:

<RowSet>
  <Row>
    <Column name="<column name>" sqltype="<sql datatype">value</Column>
    ...
  </Row>
    ...
</RowSet>
…

The name attribute stores the name of the column appearing in the query while the sqltype attribute stores the SQL datatype of that column, for example INT. The value is whatever the value is for that column.

It is possible for an API to return multiple ResultSets. In such cases, there is one RowSet for each ResultSet in the generated XML. All RowSets always appear first in the generated XML.

Some databases support returning an INTEGER status value using a RETURN statement in a stored procedure. Microsoft SQL Server and AS/400 both support this feature. In both cases, the Adapter Configuration Wizard cannot determine whether a stored procedure returns a status value. Therefore, you must specify that the stored procedure is returning a value. You can use a check box to make this indication.

After choosing a stored procedure in the Stored Procedures dialog, the Specify Stored Procedure page appears, as shown in Figure 9-50. The check box appears at the bottom of the page. Select the box to indicate that the procedure contains a RETURN statement. You can view the source code of the procedure to determine whether a RETURN statement exists.

The check box appears only for stored procedures on databases that support this feature. The check box is not displayed for functions. The value returned by the stored procedure appears as an element in the OutputParameters root element in the generated XSD. The name of the element is the name of the stored procedure. The value of a return statement is lost after the execution of the stored procedure if the check box is not selected.

Figure 9-50 The Specify Stored Procedure Page

Description of "Figure 9-50 The Specify Stored Procedure Page"

The Adapter Configuration Wizard provides a mechanism that detects when PL/SQL Boolean, Record, and Table 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 Adapter Configuration Wizard to create the schema objects in the database schema before the XSD file 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 PLSQL (R REC, T TBL, B BOOLEAN);
END;

Figure 9-57 shows the step in the Adapter Configuration Wizard that is displayed when PROC procedure from PKG package is selected.

Figure 9-57 Specifying a Stored Procedure in the Adapter Configuration Wizard

Description of "Figure 9-57 Specifying a Stored Procedure in the Adapter Configuration Wizard"

As Figure 9-57 shows, the original procedure name is fully qualified, PKG.PLSQL. 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. You can use the check box to force the Adapter Configuration Wizard to overwrite an existing package when the schema objects are created.

Clicking Next twice reveals the Finish page of the Adapter Configuration Wizard, as shown in Figure 9-58.

Figure 9-58 Defining a Database Adapter Service: Finish Page

Description of "Figure 9-58 Defining a Database Adapter Service: Finish Page"

The contents of this page describe what the Adapter Configuration Wizard has detected and what actions are performed when you click Finish. The page includes the following information:

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

2. The name of the JCA file is UseJPub_db.jca.

3. 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.

4. The name of the generated XSD is SCOTT_USEJPUB_PKG-24PLSQL.xsd.

When you click Finish, Oracle JPublisher is invoked to generate the SQL files and load the schema objects into the database. If you selected a database prior to Oracle Database 18c, 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.

If you selected a database prior to Oracle Database 18c, the wrapper generated the following user-defined types 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 0 for FALSE and any nonzero INTEGER value for TRUE. 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 PLSQL, called PKG$PPLSQL, and 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, then the name of the generated wrapper procedure is TOPLEVEL$OriginalProcedureName.

The generated XSD represents the signature of wrapper procedure PKG$PLSQL 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 SQL object or collection data types are 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.

	<property name="PackageName" value="BEPL_USEJPUB"/>
	<property name="ProcedureName" value="PKG$PLSQL"/>

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, then they are truncated.

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