TIBCO BW: How to Call Stored Procedures Using JDBC Call Procedure Activity

By | April 18, 2014

In TIBCO BW Proceeses, you can use JDBC Call Procedure activity to call a Stored Procedure in a database to perform any type of actions. In this tutorial, I will explain how to use JDBC Call Procedure activity to call a database procedure and then use the result data in the process.

Step 1: Create a database Connection

First thing that we need to do for any project involving database activities is creation of a database connection. Go to JDBC Palette and choose database connection. Then specify the connection configurations. In my case, I am connecting to Oracle database as shown in below screenshot:

JDBC Call Procedure database connection

Click on the Test Connection button in the bottom of the configuration to make sure that all your configurations for the connection are correct. As you can see below, connection is successful to the database in my case:

jdbc call procedure test connection

 

Step 2: Create designer process to call a stored procedure

Before creating a designer process which can use JDBC Call Procedure activity to execute a stored procedure, we first need to create a database procedure. I have already created a simple PROCEDURE in my database with the following sql:

create or replace procedure PRC_ACCOUNT_QUERY(
pi_accountId in number,
po_balance out number,
po_beneficiary out varchar,
po_type out varchar) is

begin

select t.account_balance, t.account_beneficiary,t.account_type
into po_balance,po_beneficiary,po_type
from tbl_account_test t
where
t.account_id=pi_accountId;

exception
when oTHERS then
null;

end PRC_ACCOUNT_QUERY;

This procedure simply takes an account id as an input and then queries a database to get account details (beneficiary name, account type and account balance) and then returns the data as OUT parameter values.

Below is the current records available in the database table:

database table data

Now that we have a PROCEDURE created and data made available in the respective database table, we can move forward for creation of a Process.

Create a new process in designer and add a JDBC Call Procedure Activity in the process.  In the configuration tab of JDBC Call Procedure activity, specify JDBC Connection, choose appropriate Schema and the procedure name (choose the procedure that we created above).

Configuration will now look like below:

jdbc call procedure configuration

In the Input tab, I am going to give a hard coded value to PI_ACCOUNTID for testing purpose as shown below:

jdbc call procedure input

I want to use Render XML activity to render the output XML data of JDBC Call Procedure activity so that resultant data may be written to a text file.

For this purpose, let’s first create a XML Schema which can be used to render the XML based on JDBC Call Procedure Output.

XSD created for this purpose is given below:

JDBC Call Procedure Output Schema

Now let us add a Render XML activity in our process. In the Input Editor tab of Render XML, choose XML Element Reference option and refer to the schema that is created above.

Then in the Input tab, map OutputSet of JDBC Call Procedure activity to the root element with for each option as shown below:

jdbc call procedure render xml input mapping

 

Now as the last step of process designing, let us add a Write File activity to write the rendered XML string to a text file. As shown below, XMLString is being written to the file by mapping it in the Input tab:

jdbc call procedure write output to file

 

Step 3: Test JDBC Call Procedure TIBCO Process

Now our process is completely designed. Validate the process to ensure that there is no error. Once validated successfully, load the process in designer tester.

As you can see below, process has run successfully and we can see that result data is correctly there in the Output tab of JDBC Call Procedure activity:

tester jdbc call procedure

 

Now if you check the created file, you can see that data has been correctly rendered and then written into the file:

tester jdbc call procedure output written in file

 

 

Ajmal Abbasi

Ajmal Hussain Abbasi is a TIBCO Consultant By Profession with more than 6 years experience in TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Spotfire, TIBCO BE, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in Telecom sector by using his skills in Tibco Designer, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi is also experienced in developing solutions using Oracle PL/Sql, Linux and Java. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn

4 thoughts on “TIBCO BW: How to Call Stored Procedures Using JDBC Call Procedure Activity

  1. PRABAKAR

    my Stored Procedures has input parameter definition and no out definition since out put is XML. but in the select statement i have used top clause statement to restrict the number of rows in XML. will tibco able to build the out put cursor and build an valid xml prases. in sql xml is coming correctly but in tibco we are getting unknownResultSets and it’s failing.

    Reply
  2. Tibco Learner

    What are the advantages Vs dis advantages using stored procedure vs SQL query

    Reply
    1. Ajmal Abbasi Post author

      Dear Sandeep,

      Below are the scenarios when use of Stored Procedures is preferred over use of straight sql query:
      1. When you are integrating with some external/third-party system which doesn’t expose table level details and shares only procedures/functions.
      2. When logic is complex enough involving multiple tables.
      3. For re-usability purposes (existing Stored Procedures to be re-used instead of re-inventing the wheel).

      Using Stored Procedure approach; Service Orientation at database level is achieved by encapsulating the internal logic in the procedure body and exposing only the parametric details. Internal logic can be changed later without affecting the TIBCO code (if parameters remain unchanged).

      On the other hand; there can be scenarios where It would make more sense to simply utilize JDBC Query, JDBC Update or SQL direct activity from JDBC palette instead of calling stored procedures when the logic is simple enough.

      Reply
  3. Bamba

    Hello Ajmal,

    I am using a stored procedure with output parameter as described in the tutrorial. My procedure is building an xml file and when I run the procedure in standalone I am getting the whole xml generated. But in BW the resulting xml is truncated to 255 characters. Could you please help.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *