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

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 15 May 2006 11:06:56 -0700
Message-ID: <1147716416.770186.41040@j55g2000cwa.googlegroups.com>

Hi Chad,

It looks like you may be a VB guy, so what is below may point you in the right direction...

In Oracle...

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

SQL> create table t0515(c number);

Table created.

SQL> begin
  2 for i in 1..5 loop
  3 insert into t0515 values(i);
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE PACKAGE p0515 AS
  2 TYPE myCurType IS REF CURSOR;
  3 PROCEDURE return_cursor(cur OUT myCurType);   4 End;
  5 /

Package created.

SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY p0515 IS   2 PROCEDURE return_cursor(cur OUT myCurType) AS   3 BEGIN
  4 OPEN CUR FOR SELECT * FROM t0515;   5 END return_cursor;
  6 END;
  7 /

Package body created.

SQL> SHOW ERRORS
No errors.
SQL>

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

In VB...

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

Sub oracle_cursor()
  Set con = CreateObject("ADODB.Connection")   Set rs = 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
  Set rs = com.Execute

  Do Until rs.EOF
    MsgBox (rs(0))
    rs.MoveNext
  Loop
End Sub

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

Regards,

Steve Received on Mon May 15 2006 - 13:06:56 CDT