store procedure call to fetch data in JDBC sender ...

Former Member · ‎07-02-2008

Hi guys,

I have to fetch data from a Oracle server through JDBC adpater.

the partner has in place some store procedure that have to be used with this purpose.

But al the time I try to call a store procedure I got error that variables are not binded.

I fill up the query sql statement field with following text

call <store procedure name> (v1 OUT VARCHAR2, v2 in varchar2)

Does anyone know the syntax to access such store procedure within jdbc adapter?

As far as I know the jdbc call the store procedure with Callable statement but the paremeters need to be linked to datatype,but here I do not see such possibility.

Former Member · ‎10-22-2008

I used oracle functions and all was fine

Former Member · ‎07-03-2008

check your config,

take data from payload and try to execute stored proc. in database itself with parameters from payload.

bhavesh_kantilal · ‎07-03-2008

You cannot call a oracle Stored Procedure in the Jdbc adapter of XI ( except for the Asynch call in Receiver JDBC adapter).

You need to use, Oracle functions as a alternate,

Regards

Bhavesh

former_member240483 · ‎07-02-2008

Hi there,

after triggering the message through JDBC sender ,In mapping call stored procedure to fetch the data for that record..

former_member192892 · ‎07-02-2008

Hi Pace,

The problem with oracle stored procedures is that they return a CURSOR object and not a resultset. The JDBC sender in XI deals with only resultset objects.

Former Member · ‎07-02-2008

Hi,

It seems my question has been misunderstood.

I meant Store procedure through JDBC adapter in Adapter framework.

All is XI/PI related.

I would say it is a XI expert related question

thanks.

Best Regards,

Giammaria Pace

Former Member · ‎07-02-2008

HI

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

http://help.sap.com/saphelp_nw04/helpdata/en/1d/756b3c0d592c7fe10000000a11405a/content.htm

One of the usage is to prevent the query to directly write to the database

http://en.wikipedia.org/wiki/Stored_procedure

Check these:

http://help.sap.com/saphelp_nw04s/helpdata/en/b0/676b3c255b1475e10000000a114084/frameset.htm

http://help.sap.com/saphelp_nw04s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/45/023c41325fa831e10000000a1550b0/frameset.htm

cheers