TIBCO JDBC Call Procedure: How to Call Stored Procedures

TIBCO JDBC Call Procedure activity from JDBC Palette in TIBCO BW is used 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:

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:

 

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:

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:

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

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:

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:

 

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:

 

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:

 

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