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

Isenção de Responsabilidade para Conteúdo da KB Desativado

Este artigo foi escrito sobre produtos para os quais a Microsoft já não fornece suporte. Por conseguinte, este artigo é oferecido "tal como está" e não será mais atualizado.

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:

Oracle8 Enterprise Edition documentation
Propriedades

ID do Artigo: 255043 - Última Revisão: 19 de jun de 2014 - Revisão: 1

Comentários