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:

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:

TIBCO 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 Integration Consultant By Profession with 13+ years experience in Integration domain mainly with TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Cloud, TIBCO Flogo, TIBCO Mashery, TIBCO Spotfire, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in various sectors by using his skills in TIBCO Flogo, TIBCO API Management (Mashery), TCI, Tibco Designer, TIBCO Business Studio, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi has experience with MuleSoft ESB as well. Ajmal Abbasi is also experienced in the area of API Management particularly with WSO2 API management platforms. Ajmal Abbasi is also experienced in developing solutions using Core Java and J2EE Technologies. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn - YouTube

9 thoughts on “TIBCO JDBC Call Procedure: How to Call Stored Procedures

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

        hi Ajmal,
        can you able to provide a perfect differences between call procedure and JDBC query. like pros and cons please.

        thanks,
        Srini

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

    i have created function in Sybase database, but I am not able to call it from jdbc stored proc pallate,could you please help me why.

    Reply
  5. Dillip Patnaik

    Do you use PL/SQL WEB Service or TIBCO? We like to see TIBCO ESB to pickup external file data send and receive data through PL/SQL Web service through TIBCO EBS

    Reply

Leave a Reply

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