How do I return a set of records from a pl/sql procedure

The Oracle (tm) Users' Co-Operative FAQ

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

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.

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