Re: Easy in SQL Server. How do you do this in Oracle?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 16 May 2006 13:05:03 -0700
Message-ID: <1147809903.210185.277490@i39g2000cwa.googlegroups.com>

Chad emailed me regarding returning two cursors, which I can't get to work in entirety (from VB), so I'll turn it back over to the group if anyone knows more...

/********************************************************************************/

CREATE OR REPLACE PACKAGE p0515 AS
  TYPE myCurType IS REF CURSOR;
  PROCEDURE return_cursor(cur1 OUT myCurType,

                          cur2 OUT myCurType);

END;
/

CREATE OR REPLACE PACKAGE BODY p0515 IS
  PROCEDURE return_cursor(cur1 OUT myCurType,

                          cur2 OUT myCurType) AS

  BEGIN
    OPEN CUR1 FOR SELECT * FROM t0515 WHERE c = 1;     OPEN CUR2 FOR SELECT * FROM t0515 WHERE c = 2;   END return_cursor;
END;
/

/********************************************************************************/

The above will return two cursors (recordsets).

In VB, I couldn't get the following to work (provider error), but I can in Java, so I know the Oracle piece works.

/********************************************************************************/

Sub oracle_cursor()

  Set con = CreateObject("ADODB.Connection")
  Set rs1 = CreateObject("ADODB.Recordset")
  Set rs2 = CreateObject("ADODB.Recordset")
  Set com = CreateObject("ADODB.Command")

  con.Open "Provider=MSDAORA;" _


         & "Password=rep;" _

         & "User ID=rep;" _

         & "Data Source=test10g2;" _

         & "Persist Security Info=True"

  com.ActiveConnection = con
  com.CommandText = "{CALL p0515.return_cursor()}"
  com.CommandType = adCmdText

  rs1.CursorLocation = adUseClient
  rs1.Open com
  Do Until rs1.EOF
    MsgBox (rs1(0))
    rs1.MoveNext
  Loop

  rs2 = rs1.NextRecordset
  Do Until rs2.EOF
    MsgBox (rs2(0))
    rs2.MoveNext
  Loop
End Sub

/********************************************************************************/

Regards,

Steve Received on Tue May 16 2006 - 15:05:03 CDT