FAQ #4: Returning Recordsets from Oracle Stored Procedures (Packages) via ADO
By: John Kilgo, July 1, 1999

This article is made up mostly of (complete) example code to return a recordset from an Oracle package through ADO using the Microsoft 2.5 ODBC driver.  I've tried the Microsoft OLEDB driver for Oracle, and it  will not work. I haven't tried the Microsoft 2.0 ODBC driver, but I don't believe it works either. The article is in response to the many questions on the subject on the Oracle list server.

This example draws from the Microsoft Knowledge Base examples and advice, but actually works.

I believe the two keys to understanding how to get recordsets from Oracle Packages are 1) knowing how Oracle returns multiple-row type data, and 2) knowing the call syntax within ASP or VB.

Oracle does not return recordsets, or cursors, like most other databases do.  It returns a multi-dimensional array of values which ASP can interpret as a recordset.  To make matters more confusing, Oracle does not use "arrays" in its terminology.  Instead it uses "tables".  Syntactically, PL/SQL tables are like arrays.  Their implementation, however, is more like a normal database table with two columns, KEY and VALUE.  The type of key is always BINARY_INTEGER and the type of VALUE is whatever you specify in the definition.  Examine the code in the Specification section of the package below and you will see how the tables are defined.

In the ASP code below you will see the syntax for calling the package.  An example is as follows:

{call packperson.allperson({resultset 10000, o_ssn, o_fname, o_lname})}.  

The general form is:
{call PackageName.ProcedureName({resultset <NumberOfRows>, Table(array), Table(array),...})}

The "resultset <NumberOfRows>" indicates the number of rows you expect to be returned.  Of course you usually do not know how many rows to expect.  Unfortunately, specifying the number of rows you expect to receive is a required element in a call to an Oracle stored procedure of this type.  Fortunately, as long as you specify more rows than you really expect you will be OK (it doesn't have to be 10,000 as I used - any number large enough for your purposes will do).  There is no penalty for specifying a large number as far as I can tell.

Except for creating the example table and populating it with a few rows yourself, you should be able to cut and paste all of the code in this article and have a complete, functioning system.

Step One:
Choose an Oracle database (schema) and create a small table named PERSON with the following layout:

(I know, the SSN shouldn't be a NUMBER, but for this example it is.)

Step Two:
Populate the table with a few rows using INSERT statements.

Step Three:
Create and save the following Oracle Package in the same database (schema):

Package Specification:

PACKAGE PackPerson
AS
TYPE tbl_ssn IS TABLE of person.ssn%type
INDEX BY BINARY_INTEGER;

TYPE tbl_fname IS TABLE of person.fname%type
INDEX BY BINARY_INTEGER;

TYPE tbl_lname IS TABLE OF person.lname%type
INDEX BY BINARY_INTEGER;

PROCEDURE AllPerson (o_ssn OUT tbl_ssn,
                                         o_fname OUT tbl_fname,
                                         o_lname OUT tbl_lname);

PROCEDURE OnePerson (i_onessn IN NUMBER,
                                           o_ssn OUT tbl_ssn,
                                           o_fname OUT tbl_fname,
                                           o_lname OUT tbl_lname);

PROCEDURE GetSSN (o_ssn OUT tbl_ssn);

END PackPerson;

Package Body:

Package Body PACKPERSON
IS

-- MODIFICATION HISTORY
-- Person        Date                Comments
-- --------- ------ ------------------------------------------------
-- John Kilgo   05/03/1999    Test Package for returning resultsets
--                                           to Visual Basic or ASP pages.

-- Procedure AllPerson returns all rows.
-- Procedure OnePerson returns one row based upon SSN IN Parameter from caller.
-- Procedure GetSSN returns all Social Security Numbers in order to fill a combo box for use
-- with applications that call Procedure OnePerson.

PROCEDURE AllPerson (o_ssn OUT tbl_ssn,
                                         o_fname OUT tbl_fname,
                                         o_lname OUT tbl_lname)
IS
CURSOR c1 IS
    SELECT ssn, fname, lname
    FROM person
    ORDER BY ssn;

percount NUMBER DEFAULT 1;

BEGIN
FOR c IN c1 LOOP
    o_ssn(percount) := c.ssn;
    o_fname(percount) := c.fname;
    o_lname(percount) := c.lname;
    percount := percount + 1;
END LOOP;
END;

PROCEDURE OnePerson (i_onessn IN NUMBER,
                                           o_ssn OUT tbl_ssn,
                                           o_fname OUT tbl_fname,
                                           o_lname OUT tbl_lname)
IS
CURSOR c1 IS
    SELECT ssn, fname, lname
    FROM person
    WHERE ssn = i_onessn;

percount NUMBER DEFAULT 1;

BEGIN
FOR c in c1 LOOP
    o_ssn(percount) := c.ssn;
    o_fname(percount) := c.fname;
    o_lname(percount) := c.lname;
    percount := percount + 1;
END LOOP;
END;

PROCEDURE GetSSN (o_ssn OUT tbl_ssn)
IS
CURSOR c1 IS
    SELECT ssn
    FROM person
    ORDER BY SSN;

percount NUMBER DEFAULT 1;

BEGIN
FOR c in c1 LOOP
    o_ssn(percount) := c.ssn;
    percount := percount + 1;
END LOOP;
END;

END;

Step Four:
Copy and save the following two .ASP pages.  (Due to the problems with displaying HTML code in a browser, you may want to first create these two files in Interdev then use the Paste as HTML selection on the Edit menu to completely replace the shell that Interdev created.  If you don't do it this way you may see &lt; for "<" and &gt; for ">" all through your code.)
  
The first page (GetInput) simply allows the user to choose whether they want all rows returned, or just one row, based upon Social Security Number.  GetInput also uses one of the procedures to fill a combo box with SSNs in the table.  Note: "strConnectString" is a DSN-less connection string.  You will have to modify it for your setup.

<%@ Language=VBScript %>
<%Option Explicit 
Response.Buffer = True

' ---------------------------------------------------------------------------------------------
' Name:     GetInput.asp
' By:       John Kilgo
' Date:     05/03/1999
'
' Purpose:  Displays choices of how to retrieve data using an Oracle
'           Stored Procedure.
'
'           Called by: <none>
'           Calls: GetData.asp
' ---------------------------------------------------------------------------------------------
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>Oracle Stored Procedure Test Application</TITLE>
</HEAD>
<BODY>

<%
Dim cnnTest
Dim cmdSSN
Dim rsTest
Dim strConnectString
Dim strSQL
Dim intSsn

'Setup connection object
strConnectString = "UID=alf_info;PWD=<password>;driver={Microsoft ODBC for Oracle};SERVER=ora_p;"
Set cnnTest = Server.CreateObject("ADODB.Connection")
With cnnTest
    .ConnectionString = strConnectString
    .CursorLocation = adUseClient
    .Open
End With

'Define call to stored procedure
strSQL = "{call packperson.getssn({resultset 10000, o_ssn})}"

'Command Object
Set cmdSSN = Server.CreateObject("ADODB.Command")
With cmdSSN
    Set .ActiveConnection = cnnTest
    .CommandText = strSQL
    .CommandType = adCmdText
End With

'Setup Recordset Object
Set rsTest = Server.CreateObject("ADODB.Recordset")
With rsTest
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
End With

Set rsTest.Source = cmdSSN
rsTest.Open
%>
<CENTER>
<H2><font color=blue>Result Sets From Oracle Stored Procedures</font></H2>
<P>
<FORM name="quickform" action="GetData.asp" method="post">
<TABLE width="60%">
    <TR>
        <TD width="20%" align="right">
            <INPUT type="radio" id="radio1" name="radio1" value="A" checked>
        </TD>
        <TD width="20%" align="left">
            Get All Data
        </TD>
        <TD width="20%" align="right">
            <INPUT type="radio" id=radio2 name=radio1 value="O">
        </TD>
        <TD width="20%" align="center">
            Get One Record
        </TD>
        <TD width="20%" align="left">
            <SELECT id="select1" name="select1">
            <%
            Do While Not rsTest.EOF
                Response.Write("<OPTION>" & rsTest(0) & "</OPTION>")
                rsTest.MoveNext
            Loop
            rsTest.Close
            Set cmdSSN = Nothing
            cnnTest.Close
            Set cnnTest = Nothing
            %>
            </SELECT>
        </TD>
    </TR>
    <TR>
        <TD wdith="100%" align="center" colspan="5">
            <input type="submit" value="Get Data" id="submit1" name="submit1">
        </TD>
    </TR>
</TABLE>
</FORM>
</CENTER>
</BODY>
</HTML>

The second page, GetData, calls one of the procedures in the package depending upon whether the user chose to return all rows or only one.  Note: "strConnectString" is a DSN-less connection string.  You will have to modify it for your setup.

<%@ Language=VBScript %>
<%Option Explicit
Response.Buffer = True

' ---------------------------------------------------------------------------------------------
' Name:     GetData.asp
' By:       John Kilgo
' Date:     05/03/1999
'
' Purpose:  Displays result sets obtained from an Oracle Stored Procedure
'           Displays all rows in the table, or a single row depending upon
'           Choices input by user.
'
'           Called by: GetData.asp
'           Calls: none
' ---------------------------------------------------------------------------------------------
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<%
Dim cnnTest
Dim cmdAllRows
Dim cmdOneRow
Dim rsTest
Dim strConnectString
Dim strSQL
Dim strOneSSN
Dim strQueryType
Dim intSsn

'Get Query type / value
strQueryType = Request.Form("radio1")
IF strQueryType="O" THEN
    'Wants only one row, so get the SSN to select
    intSSN = Request.Form("select1")
END IF

'Setup connection object
strConnectString = "UID=alf_info;PWD=<password>;driver={Microsoft ODBC for Oracle};SERVER=ora_p;"
Set cnnTest = Server.CreateObject("ADODB.Connection")
With cnnTest
    .ConnectionString = strConnectString
    .CursorLocation = adUseClient
    .Open
End With

'Define call to stored procedure to return all data
strSQL = "{call packperson.allperson({resultset 10000, o_ssn, o_fname, o_lname})}"

'Command Object for all data
Set cmdAllRows = Server.CreateObject("ADODB.Command")
With cmdAllRows
    Set .ActiveConnection = cnnTest
    .CommandText = strSQL
    .CommandType = adCmdText
End With

'Define call to stored procedure to return one row based upon SSN input by user
strSQL = "{call packperson.oneperson(?,{resultset 2, o_ssn, o_fname, o_lname})}"

'Command Object for one row
Set cmdOneRow = Server.CreateObject("ADODB.Command")
With cmdOneRow
    Set .ActiveConnection = cnnTest
    .CommandText = strSQL
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With

'Setup Recordset Object
Set rsTest = Server.CreateObject("ADODB.Recordset")
With rsTest
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
End With

Response.Write("<center>")
Response.Write("<h2><font color=blue>Oracle Stored Procedure Output</font></h2>")
Response.Write("<p>")

'Begin recordset results HTML table
Response.Write("<table border=1 cellspacing=2 cellpadding=2>")
Response.Write("<tr>")
Response.Write("<td>SSN</td><td>First Name</td><td>Last Name</td>")
Response.Write("</tr>")

'Begin displaying data
IF strQueryType="A" THEN
    'All data was requested, so set recordset object to appropriate source
    Set rsTest.Source = cmdAllRows
    'Open the recordset
    rsTest.Open
    'Loop through recordset and produce table output
    While Not rsTest.EOF
        Response.Write("<tr>")
        Response.Write ("<td>" & rsTest(0) & "</td><td>" & rsTest(1) & "</td><td>" & rsTest(2) & "</td>")
        Response.Write("</tr>")
        rsTest.MoveNext
    Wend
ELSE
    'One row was requested
    Set rsTest.Source = cmdOneRow
    'Provide the input parameter (SSN)
    cmdOneRow(0) = intSSN
    'Open the recordset
    rsTest.Open
    Response.Write("<tr>")    
    IF rsTest.BOF and rsTest.EOF THEN
        Response.Write("<td>SSN " & intSSN & " Not Found." & "</td>")
        Response.Write("</tr>")
    ELSE
        Response.Write ("<td>" & rsTest(0) & "</td><td>" & rsTest(1) & "</td><td>" & rsTest(2) & "</td>")
        Response.Write("</tr>")
    END IF
END IF
rsTest.Close
Set rsTest = Nothing
Response.Write("</table>")
Response.Write("</center>")

Set cmdOneRow = Nothing
cnnTest.Close
Set cnnTest = Nothing
%>

</BODY>
</HTML>

Final Notes:

You can also get a recordset from an Oracle procedure (not a package) using Oracle Objects For OLEDB (OO4O).  Perhaps someone else can submit a working example code for that method.

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.