The Oracle (tm) Users' Co-Operative FAQ

How do I return a record set from a stored procedure.


Author's name: Keith Jamieson

Author's Email: Keith_Jamieson@hotmail.com

Date written: 26 July 2001

Oracle version(s): 7.34, 8.0.6, 8.1.7

ADO version:  2.5

This question could probably be broken down into: How do I return a record set from an oracle stored procedure to  <Language Name>   using <OLE/ODBC>. It is important to note that there a lot of Database version and Driver dependencies, as well as a few other issues to be aware of. Questions of this nature are frequently posed in comp.databases.oracle.server.  This answer concentrates mainly on returning a recordset using ADO 2.5 

Back to index of questions


My intention here, is to demonstrate how to write a stored procedure, which is capable of returning a recordset to VB using MDAC ( ADO) 2.5, and make people aware of some of the issues which I have encountered.Here is the  list of ADO  issues that I have discovered, along with the appropriate workaround.

Key Issue Workaround
Char(x) An ADO procedure call will fail if it has a char datatype in the parameter list. (Inclusive of Ref Cursors) Replace the char datatype with varchar2 datatype.
Pls_integer An ADO procedure call will fail if it has a pls_integer datatype in the parameter list. (Inclusive of Ref Cursors) Replace the pls_integer datatype with the integer datatype
Date A call to a parameter defined as date will fail The solution to this is to declare the "date" parameter as varchar2 and include an additional varchar2 parameter (dateformat) which is the Oracle date format mask to be applied to the parameter.
Parameter Order A call to a stored procedure will be incorrect and may fail if parameters are not in the same order as those declared in the code Parameters in the VB/ADO call must be defined in the same order as those in the stored procedure.
Module Overloading Procedure calls fail if overloaded modules are used Do not use overloaded modules
RecordSets(OLE) Recordsets can only be returned using a ref cursor when using ADO/OLE. It is necessary to have the recordset declared as a ref cursor and it must be the last parameter in the parameter list.

This also necessitates that no direct manipulation of the resultset can be performed.

The ref cuirsor must be declared in a package

Recordsets(ODBC) Resultsets can only be returned using a pl/sql table and it is necesary to specify the max no of rows which are to be returned. Specifying too few results in error, and too many has memory/performance considerations. One possible solution is to count the max possible no of rows to be returned, when the query is generated. This then should be used as an input parameter to the stored procedure.

Recordsets can however be manipulated before the results are returned. The pl/sql table must be declared in a package

Max Cursors The max_open cursors error is returned The recordset must be set to null after the ado call. All open cursors in the pl/sql procedure must be closed in the main code and also in the exception handler
Parameter Lengths Parameters which are above 32K in length can not be passed into or out of a stored procedure. Parameters must be broken up into 32K chunks.
No of Parameters This is Database and ADO version dependent. The limit is on the ADO side and is 10 for 8.0.x databases or 50 for 8.1.x Databases. Fixed in ADO 2.6 Return the results in a record set.
Blobs/Clobs Blobs/Clobs cannot be passed into a stored procedure Pass in a long raw. If necessary, it can be converted to a BLOB/CLOB.
ref_cursor parameter The package fails to compile successfully when the ref_cursor parameetr is defined as out only. (Oracle 7.3.4 only) Declare the ref_cursor parameter as in/out for Oracle 7.3.4. Can be either IN/OUT or OUT only for Oracle 8 and above.

In order to return result sets, we need to use a Package.

We need to declare a record specifying all the columns to be returned by the result set.

TYPE sample_record is RECORD(name    varchar2(30),
                                     rank        integer,
                                     serialno    varchar2(15));

Declare a ref cursor which returns the defined record type:

TYPE sample_CURSOR is REF CURSOR RETURN sample_record;

Code the Procedure Specification.

PROCEDURE faq_sample(p_cursor_param  IN OUT  sample_CURSOR);

The Packaged Procedure  should then contain  code to populate the Ref Cursor:

OPEN p_cursor_param 
FOR   
SELECT name,
       rank,
       serialno 
  FROM battalion;

Finally, write a PL/SQL stub to test  your procedure through SQL*PLUS, ensuring that  you issue set serveroutput on before you start.

DECLARE
  v_sampleCURSOR samplepkg.sample_cur;
  sample_rec     samplepkg.sample_rec;
BEGIN
    SAMPLEPKG.faq_sample(v_sampleCURSOR );
LOOP
    FETCH v_sampleCURSOR into sample_rec;
    EXIT WHEN v_sampleCURSOR%NOTFOUND;
    dbms_output.enable;
    dbms_output.put('1:'||sample_rec.name);
    dbms_output.put('2:'||sample_rec.serialno);
    dbms_output.put_line('3:'||sample_rec.serialno);
END LOOP;
CLOSE v_sampleCURSOR;
END;

Here is an example of some VB code which reads a recordset and returns a result set.

 '// Approach 1
    Set rsTest = cmTest.Execute
    
    '// Approach 2 (see notes at top of module)
    'Set rsTest = New ADODB.Recordset
    'rsTest.Open cmTest
    
    '// Work with the recordset...
    Do While Not rsTest.EOF
        '...
        rsTest.MoveNext
    Loop
    
    rsTest.Close

Following these rules and paying attention to any issues in the table above, it is possible to return a recordset using ADO.

You can also return a PL/SQL  table to VB by using a call as follows:

msSelect = "{call samplepkg.sample_code(?,{resultset 1000,sample_input})} 

In the example above, samplepkg is the name of the package, sample_code the name of the procedure, and sample_input is an input parameter. The main problem with this method is that you must specify the maximum no of rows to be returned. In this case, we have specified 1000. Had we specified 10 and attempted to get back 11 rows, then we would have encountered a driver error.  The temptation would then be to set the value arbitrarily high, but this can have severe memory applications for your client. 

It is also possible to return PL/SQL tables to a ref cursor, using Oracle Objects. Further details can be found elsewhere on this site.


Further reading: http://osi.oracle.com/~tkyte/ResultSets/index.html,
                        VB Oracle 8 Programmers Reference DOV TRIETSCH John Mason ISBN: 1-861001-78-9
                         


Back to top

Back to index of questions