Skip to Content
author's profile photo Former Member
Former Member

JDBC Receiver with Oracle Stored Procedures

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:

sql-stored-procedure-returning-cursors-in-xi-30

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2005 at 05:25 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.