Microsoft KB Archive/255043

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

Article ID: 255043

Article Last Modified on 5/17/2007

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q255043



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

</ul>

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.

<ol>  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; /                        </li>  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 %>   <%    Set cmd = Nothing Set param = Nothing rs.close Set rs = Nothing cn.close Set cn = Nothing %> </P> </BODY> </HTML> </li></ol>

<div class="references_section">