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.
Go through the below link for brief idea on first approach.
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.
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.
- 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.
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)
open emp_details for
select * from db_employee where empNo = empID;
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;
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
/*ref cursor instance which will be passes as parameter to stored procedure*/
/*row type temporary variable to loop through ref cursor and pipe the values*/
/*invoke stored procedure*/
get_emp_details(empID, empDetails );
/*loop through ref cursor*/
FETCH empDetails INTO empDetail ;
exit when empDetails%NOTFOUND;
/*sending values of the ref cursor row by to the PIPE */
pipe row (empDetail);
/*end of function*/
/* end pkg body */
Compile the package and commit the changes in the database.
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.
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.