Microsoft KB Archive/229919

= How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP =

Article ID: 229919

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft Active Server Pages 2.0
 * Microsoft Active Server Pages 3.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Services 5.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q229919



SUMMARY
This article demonstrates how to call an Oracle package to retrieve a recordset using ADO on Active Server Pages (ASP).

This article assumes that:  You are proficient with Visual Basic Scripting Edition (VBScript), ActiveX Data Objects (ADO), and Active Server Pages (ASP). You understand Oracle's Procedural Language/Structured Query Language, if you will also be creating Oracle packages. Your Internet Information Server (IIS) is configured properly to the Oracle database. For additional information, please click the article number below to view it in the Microsoft Knowledge Base:

193225 How to Configure IIS to Connect to Oracle from ASP and ADO





MORE INFORMATION
To call an Oracle stored procedure, the stored procedure must be encapsulated within a package. The following sample uses an Oracle package that was assembled as an example in the Microsoft Knowledge Base article below:

174981 How To Retrieve Typical Resultsets from Oracle Stored Procedures

The script sample below does not use a Data Source Name (DSN) to connect to Oracle. It uses a DSN-less connection. (You can use a system DSN by commenting out the appropriate lines.) The sample uses ODBC to connect to Oracle.

Note that you could also use the Microsoft OLE DB Provider for Oracle in this sample. To do so, comment out the appropriate lines. <%@ LANGUAGE="VBSCRIPT" %>

<%   'Constants adCmdUnknown = 0 adCmdText = 1 adCmdTable = 2 adCmdText = 1 adParamInput = 1 adParamOutput = 2 adInteger = 3 adUseClient = 3 adOpenStatic = 3

Dim cnnOracle Dim cmdStoredProc Dim rsEmp

'This code creates a connection object. Set cnnOracle = Server.CreateObject("ADODB.Connection") cnnOracle.CursorLocation = adUseClient

'System DSN connection 'strConn = "DSN=OracleDSN; UID=UserID; PWD=Password"

'DSN-less connection strConn = "DRIVER={Microsoft ODBC for Oracle}; SERVER=DatabaseAlias; UID=UserID; PWD=Password"

'OLE DB connection 'strConn = "Provider=MSDAORA.1; Data Source=DatabaseAlias; User ID=UserID; Password=Password"

'Note: The DatabaseAlias is the name that was created in SQL*Net Easy Configuration or in Net8.

cnnOracle.Open strConn

'This code creates a command object. Set cmdStoredProc = Server.CreateObject("ADODB.Command") Set cmdStoredProc.ActiveConnection = cnnOracle

'Retrieve only one record 'cmdStoredProc.CommandText = "{call packperson.oneperson(?,{resultset 2, ssn, fname, lname})}" 'cmdStoredProc.CommandType = adCmdText 'cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("wildcard",adInteger,adParamInput)

'Retrieve all records. cmdStoredProc.CommandText = "{call packperson.allperson({resultset 9, ssn, fname, lname})}" cmdStoredProc.CommandType = adCmdText

'This code creates a recordset object. Set rsEmp = Server.CreateObject("ADODB.Recordset") rsEmp.CursorType = adOpenStatic

Set rsEmp.Source = cmdStoredProc

'Set the parameter for to get only one record 'cmdStoredProc(0) = 555662222

rsEmp.Open %>

    Retrieving a Recordset from an Oracle Stored Procedure using ADO on ASP</TITLE> </HEAD> <BODY>

<%       'Traverse through the recordset and display the data While Not rsEmp.EOF Response.Write(rsEmp(0) & "&#xa0;" & rsEmp(1) & " " & rsEmp(2) & "<BR>") rsEmp.MoveNext Wend

' Close the Recordset and the Connection rsEmp.Close cnnOracle.Close

' Dereference the ADO Objects Set cmdStoredProc = nothing Set rsEmp = nothing Set cnnOracle = nothing

%> </BODY> </HTML>

<div class="references_section">