StoredProcedure call with Signature which returns a Ref_cursor.

Hi all,
I am working on a SP call which returns a list of records which in turn are spooled to a file.
I am making a SP call to Oracle DB view by StoredProcedurewithSignature in webmethods.
Now, I have selected the SPwithSignature. In the call tab, I have selected the Schema Name and the Procedure Name.
It is giving the inputs and output parameters by itself.
The output parameter SQL Type is REF CURSOR. JDBC Type is ORACLECURSOR. Output Field Type is Document.

Till here, it is generating by itself. Now, In the ResultSet Tab, What should i give? I have mentioned resultset index 1 as CSR.
and in the bottom of it, I mentioned the resultset Name and JDBC Type as ORACLECURSOR. And it is setting the output type as java.lang.Object.
After that…When I see in the Input/Output tab, The input fields are strings and in the Output. There is a CSR Doc list, under it CSR is a Object.

When I run the SP, I am not getting any data back.
Please assist me.
Thanks,
David.

If my Question is not clear. Please excuse me. Just tell me the procedure to call a Stored Procedure and the Parameters which I need to takecare to get a set of records.

David,
You are on the right track but your Result Index tab needs a little work. For the Result Set Name I usually leave that at the default which matches what was generated from the Call tab (but you don’t have to). At the bottom of the ResultSet tab you need to map in what the StoredProcedure actually is returning ie. Each individual column name and type in order

So if your procedure was returning, Name, Address, City, State those would be your column names in that order. Map each on of those back to the Result Set Name and declare the appropriate type and you will be all set.

1 Like

I had the same the same question than David and your reply worked for me… Thanks Mark!!!