Executing stored procedure from Sender adapter in SAP PI 7.1

[Former Member]

Invoking Oracle stored procedures from within a JDBC sender channel is only possible for Oracle DBMS versions >= 10.2.x using table functions

       (check OSS note  Note 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities point "6. Oracle Database 10g 10.2.x JDBC Driver"       for              details") and other approach is developing your own JDBC adapter .

      Go through the below link for comparison of the above two approaches and brief explanation on second approach.

      http://scn.sap.com/community/pi-and-soa-middleware/blog/2011/10/04/pixi-sender-jdbc-adapter-fo...

     Go through the below link for brief idea on first approach.

     http://scn.sap.com/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sende...

     Here i'm focusing on explaining the first approach in  an easy manner by taking a business case.

In order to execute the stored procedure we have to use PIPELINING concept to fetch the results of the executed stored procedure. PIPLINED functions can be executed using select statement, in which output will be given as result set to PI, which will later converted as a XML document for further processing.

      This document describes creation of packages, pipeline functions and collection types in the database and the adapter configuration that                        needs  to be  done from PI end.

     Assumptions:

It’s assumed that the reader of this document is familiar with the basics concepts of SAP PI and database stored procedure or functions and

how to develop interfaces in SAP PI.

     This document is limited to only sender JDBC adapter. Receiver adapter is not discussed as part of this document.

     Prerequisites:

• To connect to the database required drivers files must be installed in PI server.
• Fire wall ports should be opened to database from PI server.

     Stored procedure:

In this example we will execute the below given stored procedure “get_emp_details” which will take the empID as input and will return the

employee details as a response in the refcursor.

     create or replace procedure get_emp_details(empID IN varchar2, emp_details OUT SYS_REFCURSOR)

     is

     begin

     open emp_details for

     select * from db_employee where empNo = empID;

             end;

     Define package, collection type & function:

We need to create collection type to hold the ref cursor output data of the stored procedure. And also a PIPELINED function in which

we will write the code to execute the required stored procedure.

       In order to group these we will create a package inside which we will create these functions and collection type.

     create or replace package PITestPkg AS

     /*table type to hold the dbEmployee details*/

     type emp_table is table of db_employee%rowtype;

     /*pipeline fucntion to execute the stored procedure */

     function fn_Get_Empdetails(empID varchar2) return PITestPkg.emp_table pipelined;

              end PITestPkg;

In the above code db_employee%rowtype is the type of the stored procedure output ref cursor which will be the return parameter

of the pipeline function.

       Please note that after the function return type pipelined key word is used to specify that this function is pipelined.

     Define package, function body:

Create the package body with the function code to execute the stored procedure and return the pipelined values.

     create or replace package body PITestPkg AS

     function fn_Get_Empdetails(empID varchar2) return PITestPkg.emp_table pipelined

     is

     /*ref cursor instance which will be passes as parameter to stored procedure*/

     empDetails sys_refcursor;

     /*row type temporary variable to loop through ref cursor and pipe the values*/

     empDetail db_employee%rowtype;

     begin

     /*invoke stored procedure*/

     get_emp_details(empID, empDetails );

     /*loop through ref cursor*/

     loop

     FETCH empDetails INTO empDetail ;

     exit when empDetails%NOTFOUND;

     /*sending values of the ref cursor row by to the PIPE */

     pipe row (empDetail);

     end loop;

     return;

     /*end of function*/

     end fn_Get_Empdetails;

     /* end pkg body */

             end PITestPkg;

Compile the package and commit the changes in the database.

Adapter configuration:

There is not much configuration changes required from the PI end; we need to use the select statement to execute the pipelined function

available inside a package.

     Configure all IDE objects as required, Save and activate all the IDE objects. Now you can see the result of the stored  procedure  

     in PI. Below is the   screen shot of the output of the “get_emp_details” stored procedure received in SXMB_MONI.

          Disadvantages :

         1.    Cannot pass dynamic values as input to the stored procedure.

         2.    Executing stored procedure with more than one ref cursor will need very complex collection types and pipeline function

         3.    Need to create package and function in addition to the stored procedure to execute it.

Regards

Venkat