4 Sample Use Cases

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.

Case One: Publish and Subscribe

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.

Design-Time Steps

The following section describes metadata creation using iStudio.

See Also: Oracle Application Server Integration InterConnect User's Guide

1. Create a Business Object in iStudio. Enter Customer in the Business Object Name field in the Create Business Object dialog box.

2. Create a common data type. In the Create Data Type dialog box, complete the following:

   1. Enter Address in the Common Data Type Name field.

   2. Add the following attributes in the Name field:

      • city (STRING)

      • state (STRING)

      • zip (STRING)

3. Create an event in iStudio. In the Create Event dialog box, complete the following:

   1. Select Customer for the Business Object.

   2. Enter createCustomer in the Event Name field.

   3. Click Add to add the following attributes:

      • id (NUMBER)

      • address (Address) [ARRAY]

4. Create an application in iStudio. Enter demopub in the Application Name field in the Create Application dialog box.

5. Create a Published Event using the Publish Wizard in iStudio:

   1. Select demopub from the Application list and Database from the Message Type list in the Select an Event dialog box.

   2. Expand the list in the Select an Event dialog box and select createCustomer.

   3. Click Import in the Define Application View dialog box to import attributes from the Common View.

   4. Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments dialog box:

      • createCustomer [demopub View] -- Object Copy -- createCustomer [Common View]

   5. Click Finish.

6. Create an application in iStudio. Enter demosub in the Application Name field in the Create Application dialog box.

7. Create a Subscribed Event using the Subscribe Wizard in iStudio.

   1. Select demosub from the Application list and Database from the Message Type list in the Select an Event dialog box.

   2. Expand the list in the Select an Event dialog box and select createCustomer.

   3. Click Import in the Define Application View dialog box and select Common View to import data types from the Common View.

   4. Create the createCustomer [Common View] -- Object Copy -- createCustomer [demosub View] mappings on the Define Mappings dialog box.

   5. 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);

8. Click Finish.

9. Export SQL Code using iStudio. In the Export Application dialog box, complete the following:

   1. Select demopub and demosub in the Select the Messages or Types of Message to Export box.

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

Run-Time Steps

The following steps are based on the following files:

• create_demo_users.sql

• create_demo_table.sql

• demo_publish.sql

  
  

  See Also: "Related Files"

  
  

To complete the following steps, run the create_demo_users.sql file as the system user.

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

2. 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 the copyAdapter 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.

     
     

Related Files

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;
  /
  
  

Case Two: Invoke and Implement

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.

Synchronous Invoke and Implement

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.

See Also: Oracle Application Server Integration InterConnect User's Guide

Design-Time Steps

1. Create a Business Object in iStudio. In the Create Business Object dialog box, enter Customer in the Business Object Name field.

2. Create a common data type.

3. Create a procedure in iStudio. In the Create Procedure dialog box, complete the following:

   1. Select Customer for the business object.

   2. Enter newCustomer in the Procedure Name field.

   3. Click Import and select Database to import attributes.

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

4. Create an application in iStudio. Enter demoinv in the Application Name field on the Create Application dialog box.

5. Create an invoked procedure using the Invoke Wizard in iStudio:

   1. Select demoinv for the Application and Database as the Message Type in the Select a Procedure dialog box.

   2. Expand the list in the Select a Procedure dialog box and select newCustomer.

   3. Click Import and select Common View on the Define Application View dialog box to import attributes from the common view.

   4. Change the ID attribute from IN to INOUT.

      
      

      See Also: Appendix A, "Frequently Asked Questions"

      
      

   5. Check the box for Synchronous.

   6. Click Returned In Args and enter the following:

      • In Argument: ID

      • Out Argument: ID

6. 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]

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

8. In the Define Stored Procedure dialog box, do not edit the SQL code, it is correct.

9. Click Finish.

10. Create an application in iStudio. In the Create Application dialog box, enter demoimp in the Application Name field.

11. Create an implemented procedure using the Implement Wizard in iStudio:

    1. Select demoimp for the Application and Database as the Message Type.

    2. Expand the list in the Select a Procedure dialog box and select newCustomer.

    3. Click Import and select Database in the Define Application View dialog box to import attributes from the database.

    4. 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].

12. 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]

13. 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]

14. 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';

15. Click Finish.

16. To Export SQL code, right-click Applications in iStudio, and select Export PL/SQL. Select demoinv and demoimp from the context menu.

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

Run-Time Steps

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 the copyAdapter script named demoinv and demoimp. Then, manually input the user name and password for log in.

  
  
  
  

  See Also: Related Files for Synchronous Invoke Implement

  
  

Bring up two SQL prompts:

1. At the first SQL prompt, connect as foo/manager.

2. Run the following SQL scripts:

   • @demo_demoinv_CustomerTYPES, @demo_demoinv_Customer

   • @demo_sync_invoke

3. At the second SQL prompt, connect as bar/manager.

4. Run the following SQL scripts:

   • @demo_demoimp_CustomerTYPES

   • @demo_demoimp_Customer

5. Start the demoinv and demoimp adapters using the start scripts.

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

Related Files for Synchronous Invoke Implement

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);
  
  

Asynchronous Invoke and Implement

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.

See Also: Oracle Application Server Integration InterConnect User's Guide

Design-Time Steps

1. Create a Business Object in iStudio. Enter Customer in the Business Object Name field in the Create Business Object dialog box.

2. Create a common data type.

3. Create a procedure in iStudio. In the Create Procedure dialog box, complete the following:

   1. Select Customer for the Business Object.

   2. Enter newCustomer in the Procedure Name field.

   3. Click Import and select Database to import attributes from the database.

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

4. Create an application in iStudio. Enter demoinv in the Application Name field in the Create Application dialog box

5. Create an invoked procedure using the Invoke Wizard in iStudio:

   1. Select demoinv for the Application and Database as the Message Type in the Select a Procedure dialog box.

   2. Expand the list in the Select a Procedure dialog box and select newCustomer.

   3. Click Import and select Common View in the Define Application View dialog box to import attributes from the common view.

   4. Change the ID attribute from IN to INOUT.

      
      

      See Also: Appendix A, "Frequently Asked Questions"

      
      

   5. Uncheck the box for Synchronous.

   6. Click Returned In Args and enter the following:

      • In Argument: ID

      • Out Argument: ID

6. 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]

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

8. 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;

9. Click Finish.

10. Create a second application in iStudio. Enter demoimp in the Application Name field in the Create Application dialog box.

11. Create an implemented procedure using the Implement Wizard in iStudio:

    1. Select demoimp for the Application and Database as the Message Type.

    2. Expand the list in the Select a Procedure dialog box and select newCustomer.

    3. Click Import and select Database in the Define Application View dialog box to import attributes from the database.

    4. 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].

12. 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]

13. 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]

14. 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';

15. Click Finish.

16. To Export SQL code, right-click Applications in iStudio, and select Export PL/SQL. Select demoinv and demoimp from the context menu.

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

Run-Time Steps

Bring up two SQL prompts:

1. At the first SQL prompt, connect as foo/manager.

2. Run the following SQL scripts:

   • @demo_demoinv_CustomerTYPES

   • @demo_demoinv_Customer

   • @demo_invoke.

3. At the second SQL prompt, connect as bar/manager.

4. Run the following SQL scripts:

   • @demo_demoimp_CustomerTYPES

   • @demo_demoimp_Customer.

5. Start the demoinv and demoimp adapters.

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

Related Files for Asynchronous Invoke and Implement

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);