JDBC Receiver with Oracle Stored Procedures

[Former Member]

I work on a scenario which returns data from a JDBC Receiver Adapter from a Stored Procedure. The Database is Oracle 9i.

Calling a Stored Procedure with simple Input and Output Parameters seems to be no problem, but i encounter problems with lists of data (Resultsets).

Oracle offers e.g. to return a list of data via self defined Objects/Records packed into a table type. Its no problem to do that with JDBC, but as the JDBC Adapter only supports basic SQL Types, it s not possible that way.

The online help (http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm)

describes

".. The results within the stored procedure are returned either as a table or .."

So it should be possible to return multiple elements as a table.

Did anyone find a workaround for how to do this?

Researching throug sdn i also found this thread:

There's a descritpion about using cursors for return a resultset. Did anyone try this..what Output Type is then needed for the Cursor as "Cursor" doesn't exist as SQL Type in the JDBC Adapter?

Greetz

Sebastian

[Former Member]

Sebastian,

When you execute a Stored PROC in oracle whatever the resultset can be seen in the Adapter Message Monitor. Then you can format you are getting the data make your Data Type and message type.

Lets say you get the data like this

<resultset>

<row>One</row>

<row>Two</row>

<row>Three</row>

</resultset>

Then make your data type

resultset

row data type = xsd:string and occurance 0:unbounded.

I havent heard anyone using Cursor or possibility of using cursor with Adapter.

hope this helps.

regards

Shravan