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

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.

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;
/

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>

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

SUMMARY

MORE INFORMATION

Sample Code

REFERENCES