Re: Stored Proc with Cursor Param

From: Geoff <nospam_at_nospam.com>
Date: Sun, 28 May 2006 20:30:22 GMT
Message-ID: <yLneg.743$eB3.339@newsread3.news.pas.earthlink.net>

Hello,

I am still missing something or doing something wrong and all their C file examples use obsolete oci calls. Here is the simple routine in pl/sql:

SQL>
SQL> create or replace package body my_pkg   2 as

  3      procedure get_data( p_cursor in out rc )
  4      is
  5          l_data myArrayType;
  6      begin
  7          select myScalarType( user_id, created, username )
  8            BULK COLLECT into l_data
  9            from all_users where rownum <= 5;
 10
 11          open p_cursor for
 12          select *
 13            from TABLE( cast(l_data as myArrayType) );
 14      end;

 15 end;
 16 /

Package body created.

SQL>
SQL> variable x refcursor
SQL> exec my_pkg.get_data(:x);

PL/SQL procedure successfully completed.

SQL> print x

         X Y Z

---------- --------- ------------------------------
         0 21-JUN-01 SYS
         5 21-JUN-01 SYSTEM
        11 21-JUN-01 OUTLN
        18 21-JUN-01 DBSNMP
        20 22-JUN-01 OPS$ORA815

. . . in my code, I am doing the following (not using C so, I'll write
pseudo-code):

plsqlBlock = begin my_pkg.get_data(:cursor1); end;.

stm1p = allocate a statement handle.
stm2p = allocate a statement handle.

prepare the sql statement using stm1p.
bind ':cursor1' to stm2p, stm1p is the statement handle. define (associate) a buffer using stm1p. execute the sql using stm1p.

fetch the next row using stm1p.

. . . the fetch results in nothing coming back. I have tried different
combinations but usually get 'a define has not been done before fetch' error.

What is the correct order to do this?

Thanks.

-g Received on Sun May 28 2006 - 15:30:22 CDT