Using Oracle Objects and Collections in JDBC Call Procedure and JDBC Query Activities

The JDBC Call Procedure activity calls a database procedure. The JDBC Query activity performs the specified SQL SELECT statement.

This sample has the following processes:

  • The CreateCustomer and SelectCustomer processes show how to use Oracle Objects.
  • The CreateCustomerMultiAddress and SelectCustomerMultiAddress processes show how to use Oracle Collections.

Prerequisites

  • You must have credentials that allow you to run scripts against your Oracle database.
  • Run the jdbc_object_collection.sql file against your Oracle database to create the test objects. The file is located in the samples directory under the path TIBCO_HOME\bw\n.n\samples\palette\jdbc\Collection.
  • Ensure to install Drivers for Design time and Run time.
    Note: For more information about installing Drivers, see "JDBC Connection" section in the TIBCO ActiveMatrix BusinessWorks™ Bindings and Palettes Reference guide.

Procedure

  1. In the samples directory, select palette > jdbc > Collection and double-click tibco.bw.sample.palette.jdbc.Collection. For more information, see Accessing Samples .
  2. In the Project Explorer view, expand the tibco.bw.sample.palette.jdbc.Collection project.
  3. Set the default ApplicationProfile to match the OS you are running on. For more information, see Setting the Default Application Profile.
  4. Fully expand tibco.bw.sample.palette.jdbc.Collection.application and double-click Properties. The JDBC properties defined for the application are shown next. Provide a valid username, password, and database URL to connect to the Oracle database in your environment.


  5. Verify your JDBC connection:
    1. Fully expand the Resources directory.
    2. Double-click OracleConnection-OracleThinDriver.jdbcResource.
    3. In JDBC Driver click, Click Here to Set Preferences.
    4. Set the JDBC driver folder directory preference and click Apply. Click OK.
    5. Click the Test Connection button to verify the connection.
  6. Click File > Save to save the project.
  7. Click Run > Debug Configurations.
  8. At the left hand tree of Debug Configuration wizard, expand BusinessWorks Application and select BWApplication.
  9. Click the Applications tab and then click the Deselect All button if you have multiple applications. Select the check box next to tibco.bw.sample.palette.jdbc.Collection.application.
  10. Click Debug.
    This runs the sample in Debug mode.
  11. Click the Terminate icon to stop the process.

Result

  • On successful completion, the CreateCustomer and CreateCustomerMultiAddress processes insert a row each in the database.
  • On successful completion, the SelectCustomer and SelectCustomerMultiAddress processes display the data that was inserted by each process in the Output tab.
  • The JDBC_Collection.log output file at C:\tmp\JDBC_Collection shows the Customer details with Single and Multiple addresses.

Understanding the Configuration

The sample consists of the following processes:

  • CreateCustomer: In this process, the JDBCCallProcedure activity ADD_SINGLE_ADDRESS_CUSTOMER inserts a record containing Oracle Objects into the database.
  • CreateCustomerMultiAddress: In this process, the JDBCCallProcedure activity ADD_MULTI_ADDRESS_CUSTOMER inserts a record containing Oracle Collections into the database.
  • SelectCustomer: In this process, the JDBCQuery activity queries the database for the record added using the CreateCustomer process and displays the record in the Output tab.
  • SelectCustomerMultiAddress: In this process, the JDBCQuery activity queries the database for the record added using the CreateCustomerMultiAddress process and displays the record in the Output tab.