Re: Oracle Ref Cursor and ADO do not play well together!!

From: Mark A. Williams <nobody_at_nospam.nowhere.nohow>
Date: Sat, 03 Nov 2001 01:38:36 GMT
Message-ID: <wUHE7.116835$My2.65062665@news1.mntp1.il.home.com>

Not sure if you have a solution for this yet or not, but the following worked for me. A couple of caveats: I am not a programmer by nature, so you will find the code could probably be much better, and I am not a Microsoft/ASP/OLE expert by any means. Anyway, here's what worked for me:

/////////////////////////

Oracle database (8.1.7)

/////////////////////////

SQL> connect scott/tiger
Connected.

SQL> create or replace package adoDemo
  2 as
  3 type rc_emp is ref cursor;
  4 procedure getEmployees(p_cursor out rc_emp);   5 end adoDemo;
  6 /

Package created.

SQL> create or replace package body adoDemo   2 as
  3 procedure getEmployees(p_cursor out rc_emp) is   4 begin
  5 open p_cursor for select ename from scott.emp order by ename;   6 end getEmployees;
  7 end adoDemo;
  8 /

Package body created.

/////////////////////////

ASP Code

/////////////////////////

<% @Language = "VBScript" %>
<%

    Option Explicit
    Response.Buffer = True
    Response.Expires = -1000
%>

<body>
<p align=center><font size=5><b>ADO Demo</b></font>
<table border>
<tr><th>Employee Name</th></tr>
<%

    Dim cnAdoDemo
    Dim rsAdoDemo
    Dim cmdAdoDemo

Set cnAdoDemo = CreateObject("ADODB.Connection") Set rsAdoDemo = CreateObject("ADODB.Recordset") Set cmdAdoDemo = CreateObject("ADODB.Command")

cnAdoDemo.Provider = "OraOLEDB.Oracle" cnAdoDemo.ConnectionString = "Data Source=cds2;User ID=scott;Password=tiger;PLSQLRSet=1;"

cnAdoDemo.Open
cmdAdoDemo.ActiveConnection = cnAdoDemo cmdAdoDemo.CommandText = "{CALL scott.adoDemo.getEmployees}" Set rsAdoDemo = cmdAdoDemo.Execute

While Not rsAdoDemo.EOF

        Response.Write("<tr><td>" & rsAdoDemo(0) & "</td></tr>")
        rsAdoDemo.MoveNext

Wend

rsAdoDemo.Close
cnAdoDemo.Close

    Set cmdAdoDemo = Nothing
    Set rsAdoDemo = Nothing
    Set cnAdoDemo = Nothing
%>

</table>
</p>
</body>
</html>

"ericn" <ericn_at_sarek.cjb.net> wrote in message news:a271a1ed.0110301409.3703c69b_at_posting.google.com...
> I am wondering if anyone has been able to get an ADO recordset from an
> oracle stored function as a return value?

> I have literally tried cutting and pasting examples from the
> OraOLEDB.oracle documentation! It does not work, I am starting to get
> a little annoyed. This should not be a difficult task.

> I have been searching newsgroups for 2 days and have found no sample
> code that works for me. I am using the latest MDAC, and all the
> stored functions work fine if I just call them from a SqlPlus window,
> etc.

> When I try calling from ASP via ADO using the OraOLEDB.Oracle driver,
> I get an error saying there was an exception on the execute statement
> and then the program bombs.

> Subsequent attemts to reload the page all result in an error saying
> create object of null caused an exception... even if comment out the
> execute line, this continues to happen when trying to create any ADODB
> object.

> I found one article on MSDN saying this can happen if you store result
> sets in session variables, but I am NOT using session variables at
> all. I hate session variables.

> My Oracle database is running on Unix and the web server is on Win2k
> advanced server SP2 (IIS 5). I have already tried updating the MDAC
> to the latest version, and it makes no difference. I tried using the
> Microsoft OLEDB driver... that does not work. I have tried everything
> I can think of.

> If anyone has had this sort of thing happen, and you found a fix,
> please let me know. Thanks.

>
> --Eric
Received on Fri Nov 02 2001 - 19:38:36 CST