Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 229919 - Last Review: July 2, 2004 - Revision: 5.3
How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP
This article was previously published under Q229919
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.
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=174981/EN-US/
)
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
%>
<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) & " " & 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>
For additional information, click the article number below to view it in the Microsoft Knowledge Base:
176086Â
(http://kbalertz.com/Feedback.aspx?kbNumber=176086/EN-US/
)
How To 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:
167225Â
(http://kbalertz.com/Feedback.aspx?kbNumber=167225/EN-US/
)
How To 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:
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
| kb3rdparty kbaspobj kbcodesnippet kbdatabase kbhowto kboem kboracle KB229919 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate