returning recordsets from oracle procedures

[Steven McGarva]

I've an Oracle Stored Procedure which builds a Dynamic Table filled with calculated fields from other 'real' Tables. This data can be viewed in SQL*Plus, but is obviously removed from memory when the Procedure completes.

I now need to get this data back to VB (Access actually but the concept should be the same).

I don't want to build tables to store this dynamic data but would rather deal with a Recordset.

Is there any way to call the Oracle Stored Procedure from VBA and populate a Recordset?

ADO would be my prefered choice as I have an open connection in my app, but I don't know how to call the Stored Procedure in such a way that a Recordset is populated. Or how to return such a Recordset from the Oracle side.

Has anyone done something similar and can offer me advice?

[Gaffer]

Yeah, every day

This is something ADO had real problems with until release 2.5

I assume your sp is in a package (doesn't really matter, but as you are probably aware, it's more efficient). This example, I'll use a function to retureve the capital city from a country

dim cn as new adodb.connection
dim rs as new adodb.recordset
dim spGetCapital as new adodb.command  'this is your oracle stored proc
dim pmCountry as adodb.parameter   'this is the country paramter you're going to pass to your stored proc
 
with spGetCapital
    .activeconnection = cn
    .commandtype = adCmdStoredProc
    .commandtext = "myPackage.GetCapitalCity"   'package.procedure
    
    set pmCountry = .createparameter ("p_Country", adVarChar, adParamInput, 1)
    ' p_country is the name of your paramter in your package
    'eg if your procedure looked like this:
    'creat or replace procedure GetCapitalCity (p_Country IN VARCHAR2, CursorOut in out curYourRefCursor)
    
    .Parameters.Append pmCountry
end with
 
'set the paramter
pmCountry.Value = "Mongolia"
 
set rs = spGetCapital.Execute

Make sure you have the latest version of ADO

[Steven McGarva]

Thanks Gaffer - this is the sort of stuff I do every day also but my problem is slightly different, and I suspect more difficult.

My Oracle Stored procedure builds a dynamic Table that is Rows & Columns. It is this collection that I want returned.

Generally, I'd create an Oracle procedure with OUTPUT parameters as you suggest but this is a Single Row concept.

I need Recordsets returned - Multiple Rows.............

[Gaffer]

Steven, can you give me a simple example of your procedure? I need to visualise what you are doing...

[Steven McGarva]

Well you asked for it ;-) This is the kind of Stored Procedure I have.

My question really is at the bottom of it.

[Gaffer]

Ah, you're using PL/SQL tables. OK, this is the way I see it (tell me if you agree):

PL/SQL tables are similar to arrays: they do not exist as phyical data structures, and must be treated on a loop basis. Now, it seems to me that if you are not returning any values from your stored procedure, the only way you can see that data with another application is to populate a physical, albeit temporary, SQL table.

insert into myTable (thisTable(i).iUSTUP1, thisTable(i).LIVEDATE) values (USTUP1, LIVEDATE);

You can then create your recordset from myTable.


Are you using PL/SQL table because you are accessing certain records by their row numbers? If you are not selectively isolating individual records, I would question using PLSQL table, and just put each cfursor row into a SQL-based table...

[Steven McGarva]

Yes Gaffer, I think you are right. The way forward here is to populate a Temp Table and then query that in the normal ADO way.

I was trying to avoid this because many users might concurrently fill the temp table with this transient data that I'm creating before I pull back my stuff & delete the rows from the table.

The rows might get all mixed up and I'd be in a mess.

I think I've heard of a way to create a temp table with a session ID so that each user has their own copy. Have you heard of this?

NB: Microsoft Q174679 & Q176086 show how to retrieve records in the way we are looking at but my ODBC driver is not compatible (Oracle 9i unfortunately).

[Gaffer]

I would stick with one table, but include a unique user id as one of the fields. You can retrieve the Unique Session ID like this:

FUNCTION DBMS_SESSION.UNIQUE_SESSION_ID RETURN VARCHAR2

Are you connecting to this using ODBC?? ODBC uses an extra layer over ADO, and is pretty slow compared to ADO (mor stable though)

[Gaffer]

Oh, and there is a book I use from time to time Called VB Oracle Programmer referenc by Dov Trietsch - it's teh ony VB Oracle book I can find, and it's pretty good