HOWTO: Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP

ID: Q229919

The information in this article applies to:

Active Server Pages
ActiveX Data Objects (ADO), version 2.1
Microsoft ODBC for Oracle (Build 2.573.2927), version 2.5
Microsoft OLE DB Provider for Oracle, versions 2.0, 2.1
Microsoft Internet Information Server versions 4.0, 5.0

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:
Q193225 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:

Q174981 HOWTO: 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
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual InterDev 6.0">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
<TITLE>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>

REFERENCES

For additional information, click the article number below to view it in the Microsoft Knowledge Base:

Q176086 HOWTO: Retrieve Recordsets from Oracle Stored Procs Using ADO

For information on why you must use an Oracle Package instead of an Oracle stored procedure, click the article number below to view it in the Microsoft Knowledge Base:

Q167225 HOWTO: Access an Oracle Database Using RDO

For additional information on ADO and to download the latest version of the Microsoft Data Access Components (MDAC), please see the following Microsoft Web site:

Universal Data Access Web Site

Additional query words: kbGrpASP kb3rdparty kbADO150 kbADO200 kbDatabase kbOracle

Keywords : kbASP kbASPObj kbDatabase kbOLEDB200 kbOLEDB210 kbOracle kbGrpASP kbODBC250 kbDSupport kbiis400 kbiis500 kbOEM
Version : WINDOWS:2.0,2.1,2.5; winnt:
Platform : WINDOWS winnt
Issue type : kbhowto