How To Retrieve ADO Recordset from Oracle Through ASP Using REF CURSORS

Article translations Article translations
Article ID: 255043 - View products that this article applies to.
This article was previously published under Q255043
Expand all | Collapse all

On This Page

SUMMARY

With the release of Microsoft Data Access Components (MDAC) 2.5, the Microsoft OLEDB provider for Oracle now supports the return of ActiveX Data Objects (ADO) recordsets from an Oracle procedure through the use of a REF CURSOR type. This functionality is only supported in the Microsoft OLEDB provider for Oracle. The Microsoft ODBC for Oracle driver does not support the use of REF CURSORS.

MORE INFORMATION

This new functionality has some benefits over the previous provider:
  • In previous versions of the provider the only way to retrieve an ADO recordset from an Oracle procedure was to declare a PL/SQL table for each column that was being returned in the recordset. With the new provider you only need to declare one REF CURSOR, which then handles the return of all of the columns.

    For additional information on retrieving Oracle procedures through PL/SQL tables, click the article number below to view the article in the Microsoft Knowledge Base:
    229919 How To Retrieve a Recordset from Oracle Using ADO on ASP
  • In the previous version of the provider you had to specify the maximum number of records that would return in the recordset. When you open an ADO recordset that is based on a REF CURSOR, that step is no longer necessary. With the new provider you can just specify 0 for the number of records. For example:

    {call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}
    					
To use the sample in this article:
  • You should be proficient with Microsoft Visual Basic Scripting Edition (VBScript), ActiveX Data Objects (ADO), and Active Server Pages (ASP).

  • If you will be creating Oracle packages, you must understand Oracle's Procedural Language/Structured Query Language.

  • Your Internet Information Server (IIS) must be configured properly to work with the Oracle database.

    For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    193225 How to Configure IIS to Connect to Oracle from ASP and ADO

Sample Code

The following sample code demonstrates how to retrieve an ADO recordset from an Oracle procedure through a REF CURSOR, and then displays the data on an ASP page. You must modify the OLEDB connection string used in the ASP code to connect to your Oracle database.

  1. Create the following Oracle package on the Oracle server before you run any of the ASP code. This package uses some tables defined in the Oracle Scott/Tiger schema. The Oracle Scott/Tiger schema is installed with the default Oracle installation. If this schema does not exist, you must run the following Table Scripts and insert some information into the tables:

    Table Scripts
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2,0) NOT NULL, 
    DNAME VARCHAR2(14) NULL, 
    LOC VARCHAR2(13) NULL,
    PRIMARY KEY (DEPTNO)
    );
    
    CREATE TABLE EMP
    (EMPNO NUMBER(4,0) NOT NULL, 
    ENAME VARCHAR2(10) NULL, 
    JOB VARCHAR2(9) NULL, 
    MGR NUMBER(4,0) NULL, 
    HIREDATE DATE NULL, 
    SAL NUMBER(7,2) NULL, 
    COMM NUMBER(7,2) NULL, 
    DEPTNO NUMBER(2,0) NULL,
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),  
    PRIMARY KEY (EMPNO)
    );
    						

    Package Scripts
    CREATE OR REPLACE PACKAGE curspkg_join AS 
    	TYPE t_cursor IS REF CURSOR ; 
    	Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor); 
    END curspkg_join;
    / 
    
    CREATE OR REPLACE PACKAGE BODY curspkg_join AS
    Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 
    IS 
    	v_cursor t_cursor; 
    BEGIN 
    	IF n_EMPNO <> 0 
    	THEN
    		OPEN v_cursor FOR 
    		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
    			FROM EMP, DEPT 
    			WHERE EMP.DEPTNO = DEPT.DEPTNO 
    			AND EMP.EMPNO = n_EMPNO;
    
    	ELSE 
    		OPEN v_cursor FOR 
    		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
    			FROM EMP, DEPT 
    			WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    	END IF;
    	io_cursor := v_cursor; 
    END open_join_cursor1; 
    END curspkg_join;
    / 
    						
  2. Following is the ASP script that calls the preceding procedure:
    <%@ Language=VBScript %>
    <HTML>
    <HEAD>
    <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    </HEAD>
    <BODY>
    <P>
    <%
    	Const CONNECT = "Provider=MSDAORA.1;Data Source=your_tns_alias_name;User ID=scott;Password=tiger"
    		
    	Dim cn, rs, cmd, param
    	Dim SQL
    
    	set cn = Server.CreateObject( "ADODB.Connection" )
    	cn.Open CONNECT	
    
    	SQL = "{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}"
    	set cmd = server.CreateObject ("ADODB.Command")
    	with cmd
    		set .ActiveConnection	= cn
    	    .CommandText			= SQL
    	    .CommandType			= 1		'adCmdText
    	    'Pass in 0 to retrieve all of the records
    	    set param = .CreateParameter("id", 131 , 1 , , 0)  '<<131=adNumeric, 1=adParamInput
    	    .Parameters.Append param
    	end with
    					
    	set rs = server.CreateObject ( "ADODB.Recordset" ) 
    	set rs = cmd.execute
    		    
    	%>
    	<TABLE WIDTH="80%" ALIGN=center BORDER=1 CELLSPACING=3 CELLPADDING=3>
    	<%
    	Response.Write ("<TR bgcolor=Gray>")
    	For i = 0 To rs.Fields.Count - 1
    		Response.Write ("<TD>" & rs.Fields(i).Name & "</TD>")
    	Next
    	Response.Write ("</TR>")
    	Do until rs.EOF
    		response.write ("<TR>")
    		for i = 0 to rs.Fields.Count - 1
    			If IsNull(rs(i).value) then 
    				Response.Write ("<TD> NULL </TD>")
    			else 
    				Response.Write ("<TD>" & rs(i).value & "</TD>")
    			end if 
    		next 
    		rs.MoveNext
    		response.write ("</TR>")
    	loop
    	%>
    	</TABLE>
    	<%
    	Set cmd = Nothing
    	Set param = Nothing
    	rs.close
    	Set rs = Nothing
    	cn.close
    	Set cn = Nothing
    %>
    </P>
    </BODY>
    </HTML>
    						

REFERENCES

For more information about MDAC, please see the following Web site:
http://msdn2.microsoft.com/en-us/data/aa937695.aspx
Oracle8 Enterprise Edition documentation

Properties

Article ID: 255043 - Last Review: May 17, 2007 - Revision: 3.3
APPLIES TO
  • Microsoft OLE DB Provider for Oracle Server 1.0
Keywords: 
kbhowto kboracle KB255043
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com