Oracle® Application Server Integration InterConnect Adapter for DB Installation and User's Guide
10g Release 2 (10.1.2) B14076-02 |
|
Previous |
Next |
This chapter describes sample use cases for the Database adapter. For all of the scripts and steps for the use cases provided in this chapter, replace the following strings with the correct values.
repo_owner
: The repository owner.
version
: The version of the metadata in iStudio. This is usually V1
unless the metadata versioning features was used in iStudio.
This case illustrates a simple Publish-Subscribe scenario using a Database adapter at each end. In this case, a Customer
message containing the ID
attribute and an array of Addresses is published using a PL/SQL procedure. This message is picked up by the publishing adapter, published, and routed to the corresponding subscribing adapter through the hub. The message becomes a new row in a table in the destination schema. These adapters can be located anywhere and can talk to any database. The scripts described here create the publish and subscribe side schemas on the same database. These scripts can be modified to fit any custom scenario.
The following section describes metadata creation using iStudio.
Create a Business Object in iStudio. Enter Customer
in the Business Object Name field in the Create Business Object dialog box.
Create a common data type. In the Create Data Type dialog box, complete the following:
Enter Address
in the Common Data Type Name field.
Add the following attributes in the Name field:
city (STRING)
state (STRING)
zip (STRING)
Create an event in iStudio. In the Create Event dialog box, complete the following:
Select Customer for the Business Object.
Enter createCustomer in the Event Name field.
Click Add to add the following attributes:
id (NUMBER)
address (Address) [ARRAY]
Create an application in iStudio. Enter demopub
in the Application Name field in the Create Application dialog box.
Create a Published Event using the Publish Wizard in iStudio:
Select demopub from the Application list and Database from the Message Type list in the Select an Event dialog box.
Expand the list in the Select an Event dialog box and select createCustomer.
Click Import in the Define Application View dialog box to import attributes from the Common View.
Create the following mapping for the newCustomer
procedure on the Define Mapping IN Arguments dialog box:
createCustomer [demopub View] -- Object Copy -- createCustomer [Common View]
Click Finish.
Create an application in iStudio. Enter demosub
in the Application Name field in the Create Application dialog box.
Create a Subscribed Event using the Subscribe Wizard in iStudio.
Select demosub from the Application list and Database from the Message Type list in the Select an Event dialog box.
Expand the list in the Select an Event dialog box and select createCustomer.
Click Import in the Define Application View dialog box and select Common View to import data types from the Common View.
Create the createCustomer [Common View] -- Object Copy -- createCustomer [demosub View] mappings
on the Define Mappings dialog box.
Enter the following SQL code on the Define Stored Procedure dialog box:
For sub_createCustomer_
repo_owner
_version
:
Following the line dummy:= 0;
, Enter insert into results values (id, address);
Click Finish.
Export SQL Code using iStudio. In the Export Application dialog box, complete the following:
Select demopub and demosub in the Select the Messages or Types of Message to Export box.
Enter demo in the File Prefix field.
The following files are created and stored in the ORACLE_HOME
/integration/interconnect/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
The following steps are based on the following files:
create_demo_users.sql
create_demo_table.sql
demo_publish.sql
To complete the following steps, run the create_demo_users.sql
file as the system
user.
Start two SQL prompts:
Connect as the demopub/manager and run @demo_demopub_CustomerTYPES, @demo_demopub_Customer, @demo_publish
.
Connect as dempsub/manager and run @demo_demosub_CustomerTYPES, @create_demo_table, @demo_demosub_Customer
.
Start the demopub and demosub adapters:
In a publish SQL prompt, run exec demo_publish(ANY NUMBER)
in the demopub schema. A new row is created in the Results table in demosub schema every time it receives a message from demopub.
Note: If a Database adapter has already been installed with the application name of demopub, use thecopyAdapter script in the ORACLE_HOME /integration/interconnect/bin directory to create the demosub adapter. Usage: copyAdapter demopub demosub . Then, manually enter the user name and password for log in.
|
The following files are related to the run-time steps in CASE ONE.
File: create_demo_users.sql
CREATE USER demopub identified by manager; GRANT connect, resource to demopub; CREATE USER demosub identified by manager; GRANT connect, resource to demosub;
File: create_demo_table.sql
CREATE TABLE results (id NUMBER, address demosub_Address_repo_owner_version_Arr);
File: demo_publish.sql
CREATE OR REPLACE PROCEDURE Demo_Publish(id NUMBER) AS moid NUMBER; aoid NUMBER; addrid NUMBER; BEGIN Customer.crMsg_createCustomer_repo_owner_version(moid, aoid, id); addrid := Customer.cr_Address_address('SFO', 'CA', '94040', moid, aoid); addrid := Customer.cr_Address_address('Reno', 'NV', '93949', moid, aoid); addrid := Customer.cr_Address_address('SJC', 'CA', '95117', moid, aoid); Customer.pub_createCustomer_repo_owner_version(moid, 'demopub'); COMMIT; END; /
This use case illustrates a simple invoke and implement scenario using a Database adapter at each end. Both synchronous and asynchronous modes of invocation are illustrated. A Customer
message containing the ID
attribute, and an array of Addresses
is sent using a PL/SQL procedure. This message is picked up by the invoking adapter and routed to the corresponding implementing adapter through the hub. On the implementing end, a new row is created in a table in destination schema and a response is sent back indicating that it has received this message. Subsequently on receiving the response, the invoking adapter updates the status for the corresponding customer.
These adapters can be located anywhere and can talk to any database. The scripts provided create the sender and receiver side schemas on the same database. These schemas can be modified to adapt to any custom scenario.
Run the demo_setup.sql
file to create necessary schemas in the database on the application or spoke database. It may be necessary to connect as the system
user.
Create a Business Object in iStudio. In the Create Business Object dialog box, enter Customer
in the Business Object Name field.
Create a common data type.
Create a procedure in iStudio. In the Create Procedure dialog box, complete the following:
Select Customer for the business object.
Enter newCustomer
in the Procedure Name field.
Click Import and select Database to import attributes.
Log in to the Database as the FOO user.
Expand the FOO schema, Tables/Views and select FOO.CUSTOMERS.
In the right hand side of the dialog box, select the ID, ADDRESS, and STATUS columns using the control key.
Click Done to return to the Publish Wizard.
Import arguments as IN arguments in the Publish Wizard. Change the last column (IN/OUT/INOUT) for Status to Out and click Save.
Create an application in iStudio. Enter demoinv
in the Application Name field on the Create Application dialog box.
Create an invoked procedure using the Invoke Wizard in iStudio:
Select demoinv for the Application and Database as the Message Type in the Select a Procedure dialog box.
Expand the list in the Select a Procedure dialog box and select newCustomer.
Click Import and select Common View on the Define Application View dialog box to import attributes from the common view.
Change the ID
attribute from IN
to INOUT
.
Check the box for Synchronous.
Click Returned In Args and enter the following:
In Argument: ID
Out Argument: ID
Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments dialog box:
newCustomer:IN [demoinv View] -- Object Copy -- newCustomer:IN [Common View]
Create the following mapping for the newCustomer procedure on the Define Mapping OUT Arguments dialog box:
newCustomer:OUT.STATUS [Common View] -- Copy Fields -- newCustomer:OUT.STATUS [demoinv View]
In the Define Stored Procedure dialog box, do not edit the SQL code, it is correct.
Click Finish.
Create an application in iStudio. In the Create Application dialog box, enter demoimp
in the Application Name field.
Create an implemented procedure using the Implement Wizard in iStudio:
Select demoimp for the Application and Database as the Message Type.
Expand the list in the Select a Procedure dialog box and select newCustomer.
Click Import and select Database in the Define Application View dialog box to import attributes from the database.
Enter the correct information on the Database Login dialog box for the BAR schema.
Expand BAR, Tables/Views and select BAR.RESULTS
.
In the right hand side of the dialog box, select the ID, ADDRESS, and STATUS columns using the control key.
Click Done.
Import arguments as IN arguments. Add an attribute called STATUS [String, OUT].
Create the following mapping for the newCustomer procedure in the Define Mapping IN Arguments dialog box:
newCustomer:IN [Common View] -- Object Copy -- newCustomer:IN [demoimp View]
Create the following mapping for the newCustomer procedure in the Define Mapping OUT Arguments dialog box:
newCustomer:OUT [dempimp View] -- Object Copy -- newCustomer:OUT [Common View]
Edit the SQL code in the Define Stored Procedure dialog box as follows:
For imp_newCustomer_
repo_owner
_version
, following the line dummy:= 0;
, enter insert into results values(i_id, i_address);o_status := 'SUCCESS';
Click Finish.
To Export SQL code, right-click Applications in iStudio, and select Export PL/SQL. Select demoinv and demoimp from the context menu.
Enter demo for the File Prefix field.
The following files are created and stored in the ORACLE_HOME
/integration/interconnect/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
The run-time steps are based on the following files:
demo_setup.sql
create_sync_invoke.sql
Note: Create copies of the Database adapter using thecopyAdapter script named demoinv and demoimp . Then, manually input the user name and password for log in.
|
Bring up two SQL prompts:
At the first SQL prompt, connect as foo/manager.
Run the following SQL scripts:
@demo_demoinv_CustomerTYPES
, @demo_demoinv_Customer
@demo_sync_invoke
At the second SQL prompt, connect as bar/manager.
Run the following SQL scripts:
@demo_demoimp_CustomerTYPES
@demo_demoimp_Customer
Start the demoinv and demoimp adapters using the start scripts.
In invoke side SQL prompt, run exec newCustomer_sync(id, city, state, zip, timeout)
.
A new row in the customers
table in foo
schema is created. This new row has Status
initially set to None
but changes to Success
when the invoking adapter receives a response from the implementing adapter.
A new row is also created in the results
table in bar
schema. If the invoking adapter does not receive a response within the time specified in seconds, in the timeout
parameter, then the Status
column is not updated in foo.customers
; instead, a new row is created in the correlation table cus_newcustomer
_repo_owner
_version
. This table is created by the iStudio exported PL/SQL code. If necessary, foo.customers
has a trigger to update automatically when a new row is created in the correlation table.
The following scripts are related to the run-time steps described in both cases in CASE TWO.
demo_sync_invoke.sql
CREATE OR REPLACE PROCEDURE newCustomer_sync( ID NUMBER, CITY LONG, STATE LONG, ZIP LONG, timeout NUMBER) AS moid NUMBER; aoid NUMBER; addrid NUMBER; corrid NUMBER; ret_id NUMBER; ret_status LONG; BEGIN insert into customers values (id, Address_Array(Address(city, state, zip)), 'NONE'); Customer.crMsg_newCustomer_repo_owner
_version
(moid, aoid, id); addrid := Customer.cr_ADDRESS_ARRAY_ADDRESS(city, state, zip, moid, aoid); corrid := Customer.inv_newCustomer_repo_owner
_version
(moid, 'demoinv', timeout, ret_id, ret_status); update customers set status=ret_status where id=ret_id; COMMIT; END; /
demo_setup.sql
CREATE USER foo identified by manager; GRANT connect, resource to foo; CREATE USER bar identified by manager; GRANT connect, resource to bar; CREATE OR REPLACE TYPE foo.Address IS OBJECT ( city VARCHAR2(1000), state VARCHAR2(1000), zip VARCHAR2(1000) ); / CREATE OR REPLACE TYPE foo.Address_Array IS VARRAY(1000) OF foo.Address; / CREATE TABLE foo.customers (id NUMBER, address foo.Address_Array, status VARCHAR2(20)); CREATE OR REPLACE TYPE bar.Address IS OBJECT ( city VARCHAR2(1000), state VARCHAR2(1000), zip VARCHAR2(1000) ); / CREATE OR REPLACE TYPE bar.Address_Array IS VARRAY(1000) OF bar.Address; / CREATE TABLE bar.results (id NUMBER, address bar.Address_Array);
Run the demo_setup.sql
file to create necessary schemas in the database on the application or spoke database. It may be necessary to connect as the system
user.
Create a Business Object in iStudio. Enter Customer in the Business Object Name field in the Create Business Object dialog box.
Create a common data type.
Create a procedure in iStudio. In the Create Procedure dialog box, complete the following:
Select Customer for the Business Object.
Enter newCustomer
in the Procedure Name field.
Click Import and select Database to import attributes from the database.
Log in to the Database using the correct information.
Expand the FOO schema, Tables/Views, and select FOO.CUSTOMERS.
In the right hand side of the dialog box, select the ID, ADDRESS, and STATUS columns using the control key.
Click Done.
Import arguments as IN arguments. Change the last column (IN/OUT/INOUT) for Status to Out and click Save.
Create an application in iStudio. Enter demoinv
in the Application Name field in the Create Application dialog box
Create an invoked procedure using the Invoke Wizard in iStudio:
Select demoinv for the Application and Database as the Message Type in the Select a Procedure dialog box.
Expand the list in the Select a Procedure dialog box and select newCustomer.
Click Import and select Common View in the Define Application View dialog box to import attributes from the common view.
Change the ID
attribute from IN
to INOUT
.
Uncheck the box for Synchronous.
Click Returned In Args and enter the following:
In Argument: ID
Out Argument: ID
Create the following mapping for the newCustomer procedure in the Define Mapping IN Arguments dialog box:
newCustomer:IN [demoinv View] -- Object Copy -- newCustomer:IN [Common View]
Create the following mapping for the newCustomer procedure in the Define Mapping OUT Arguments dialog box:
newCustomer:OUT.STATUS [Common View] -- Copy Fields -- newCustomer:OUT.STATUS [demoinv View]
Edit the SQL code on the Define Stored Procedure dialog box as follows:
For sub_newCustomer
_repo_owner
_version
, following the line dummy:= 0;
, enter update customers set status=sub_newCustomer
_repo_owner
_version
.status where id=sub_newCustomer
_repo_owner
_version
;
Click Finish.
Create a second application in iStudio. Enter demoimp
in the Application Name field in the Create Application dialog box.
Create an implemented procedure using the Implement Wizard in iStudio:
Select demoimp for the Application and Database as the Message Type.
Expand the list in the Select a Procedure dialog box and select newCustomer.
Click Import and select Database in the Define Application View dialog box to import attributes from the database.
Enter the correct information in the Database Login dialog box.
Expand BAR, Tables/Views, and select BAR.RESULTS.
In the right hand side of the dialog box, select the ID, ADDRESS, and STATUS columns using the control key.
Click Done.
Import arguments as IN arguments. Add an attribute called STATUS [String, OUT].
Create the following mapping for the newCustomer
procedure in the Define Mapping IN Arguments dialog box:
newCustomer:IN [Common View] -- Object Copy -- newCustomer:IN [demoimp View]
Create the following mapping for the newCustomer
procedure in the Define Mapping OUT Arguments dialog box:
newCustomer:OUT [dempimp View] -- Object Copy -- newCustomer:OUT [Common View]
Edit the SQL code in the Define Stored Procedure dialog box as follows:
For imp_newCustomer
_repo_owner
_version
, following the line dummy:= 0;
, enter insert into results values(i_id, i_address);o_status:= 'SUCCESS';
Click Finish.
To Export SQL code, right-click Applications in iStudio, and select Export PL/SQL. Select demoinv and demoimp from the context menu.
Enter demo
for the File Prefix field.
The following files are created and stored in the ORACLE_HOME
/integration/interconnect/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
Bring up two SQL prompts:
At the first SQL prompt, connect as foo/manager.
Run the following SQL scripts:
@demo_demoinv_CustomerTYPES
@demo_demoinv_Customer
@demo_invoke
.
At the second SQL prompt, connect as bar/manager.
Run the following SQL scripts:
@demo_demoimp_CustomerTYPES
@demo_demoimp_Customer
.
Start the demoinv and demoimp adapters.
In invoke side SQL prompt, run exec newCustomer_async(id, city, state, zip, timeout)
.
A new row is created in the customers
table in the demoinv
schema. This new row has STATUS
initially set to none
but changes to success
if the invoking adapter receives a response from the implementing adapter. A new row is created in the Results
table in the bar
schema.
The following scripts are related to the run-time steps described asynchronous invoke/implement:
demo_async_invoke.sql
CREATE OR REPLACE PROCEDURE newCustomer_async( ID NUMBER, CITY LONG, STATE LONG, ZIP LONG) AS moid NUMBER; aoid NUMBER; addrid NUMBER; BEGIN insert into customers values (id, Address_Array(Address(city, state, zip)), 'NONE'); Customer.crMsg_newCustomer_repo_owner
_version
(moid, aoid, id); addrid := Customer.cr_ADDRESS_ARRAY_ADDRESS(city, state, zip, moid, aoid); Customer.inv_newCustomer_repo_owner
_version
(moid, 'demoinv'); COMMIT; END; /
demo_setup.sql
CREATE USER foo identified by manager; GRANT connect, resource to foo; CREATE USER bar identified by manager; GRANT connect, resource to bar; CREATE OR REPLACE TYPE foo.Address IS OBJECT ( city VARCHAR2(1000), state VARCHAR2(1000), zip VARCHAR2(1000) ); / CREATE OR REPLACE TYPE foo.Address_Array IS VARRAY(1000) OF foo.Address; / CREATE TABLE foo.customers (id NUMBER, address foo.Address_Array, status VARCHAR2(20)); CREATE OR REPLACE TYPE bar.Address IS OBJECT ( city VARCHAR2(1000), state VARCHAR2(1000), zip VARCHAR2(1000) ); / CREATE OR REPLACE TYPE bar.Address_Array IS VARRAY(1000) OF bar.Address; / CREATE TABLE bar.results (id NUMBER, address bar.Address_Array);