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

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.

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  (http://support.microsoft.com/kb/229919/EN-US/ ) 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  (http://support.microsoft.com/kb/193225/EN-US/ ) 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>
   						

For more information about MDAC, please see the following Web site: Oracle8 Enterprise Edition documentation