Result set from Oracle stored procedure using Outbound SQL Adapter

[Tom Longmoore]

Hi everyone,

I'm having trouble getting an Oracle stored procedure to return a result set when called via the Outbound SQL Adapter. Here is what the procedure call looks like:

set tQuery = "{ call PR_GET_ORACLE_DATA(?) }"
set tSC = ..Adapter.ExecuteProcedure(.snapShotList,,tQuery,"i*",pRequest.StringValue)

The Oracle procedure is basically just a big SELECT statement that gets data related to "pRequest.StringValue". I was hoping that the adapter would run the procedure and return the results of the SELECT statement as a list of SQL snapshots in "snapShotList". This isn't happening.

I did some research and found that the recommended way of returning a result set from an Oracle function or stored procedure is to put it in a REF CURSOR and pass it back to the caller as either the return value or an output parameter. When I tried this, it appeared that the Ensemble outbound adapter didn't recognize the REF CURSOR data type (which makes sense based on the InterSystems documentation for the ExecuteProcedure method). I ended up with an empty "snapShotList".

For now, I have copied the SELECT statement into the business operation class and am getting the result set using the adapter's ExecuteQuery method. This solves the immediate problem, but in the long run I would like to maintain the SQL in Oracle as a procedure.

Has anyone had any success getting an Oracle stored procedure to return a result set to Ensemble? I would appreciate any help I can get.

Thanks ,
Tom