JDBC Sender - Oracle Stored procedures

[former_member190313]

hi all

i am working on JDBC sender - File receiver

I am fetching data from Oracle database

if i write select query directly in sender adapter the scenario is working properly but when try to write stored procedure in oracle and use execute statement in JDBC, adapter is throwing error.

As in oracle to fetch multiple rows i have to use cursors .

i read lot of articles in forum but still not clear on following issues :can any one solve them?

1 . To get multiple rows can i write SP without cursors?

2 . If SP is using cursor do my XI accept it or it only accepts resultset? in that case how to write sp.

thanks & regards

sheetal

[STALANKI · Accepted solution]

check this one.,,/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

[bhavesh_kantilal]

Sheetal,

Adding to Jai Shankar's reply, Like pointed out, Oracle Stored Procedures return Cursors and XI sender JDBC adapter expects resultsets and so it is not possible to call Oracle SP's from Sender JDBC adapters.

But, I have also come across a few recent threads where there have been discussions that this is possible from SP 16 onwards. I have not been able to check and confirm on this, but, maybe you can check your SP level and see if you are below SP 16 . If yes, definetly not possible. If SP 16, or above, a Quick OSS to SAP should surely give the answer if it is possible or not!

Do let us know your findings.

Regards

Bhavesh

[former_member190313]

thanks a lot Jai Shankar , Sravya Talanki n Bhavesh Kantilal

will try the same on sp16

regards

Sheetal

[Former Member]

Sheetal,

Very eager to hear from you. Let us know if you suceed in SP16.

Regards,

Jai Shankar

[Former Member]

Hi Sheetal,

XI sender adapter can accept only result sets and not cursors. SO you can not go for a SP in ORACLE. You have no option other than giving your querry in SQL ststement. But the receiver JDBC adapter can call a stored procedure in ORACLE(I beleive since it is not going to return any values to XI).

Regards,

Jai Shankar