Adapter Service and Cursors

, ,
1

Hi,

I searched the forum and couldn’t find the exact match to my question. I found [url=“wmusers.com”]wmusers.com little close yet not what I am looking for.

I have a stored procedure which has IN/OUT parameter as a multi column Cursor. I am able to figure out how to read the result set in the Stored Procedure Adapter Service. However, the input looks like a document. How do I pass the multi row, multi column input parameter to the Adapter Service to pass the Stored Procedure.

Help would be appreciated.

2

Hello,
Do you want to pass something like a StringTable to the SP? If you already have the data (in whatever cast and format), you may want to cast it to an object and try passing that in. You may want to make a java service that takes in your first type and gives an object (not array or list form). Good day.

Yemi Bedu

3

Thanks Yemi.
Could anybody tell me if an Oracle Stored Procedure outputs a “Table of Record” can I read it directly into record set of a SP Adapter Service or I need to pass it as REF CURSOR to the adapter service?

4

I have CAST the table of records into a cursor.
However, now when I try to invoke the SP I get an error “Ref cursor is invalid”.

My Stored Procedure gives out a cursor (its type is declared in the package).

In my Adapter service I have defined the recordset and also defined OracleCursor as output.

Anybody has any ideas why I am getting this error?

Thanks

5

Adrienne,

To handle a resultset from a cursor
1)you have to first write the SQL in REF CURSOR
2) Use database driver specific OracleTypes.CURSOR for Classes12.jar.

Below link is quite explanatory.
[url=“http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/”]http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/[/url]

6

Adrienne,

To handle a resultset from a cursor
1)you have to first write the SQL in REF CURSOR
2) Use database driver specific OracleTypes.CURSOR for Classes12.jar.

Below link is quite explanatory.
[url=“http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/”]http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/[/url]