Re: Stored Proc with Cursor Param

From: Geoff <nospam_at_nospam.com>
Date: Mon, 29 May 2006 20:30:27 GMT
Message-ID: <DRIeg.9368$y4.4941@newsread2.news.pas.earthlink.net>

Hello,

That works great, it prints out the name of each column and the length. The next thing is how to print out the values in the table.

I went ahead and did a define, associating the cursor with a buffer and fetched rows. However, only the first column prints out. Here is the pl/sql stored proc and output:

edit
Wrote file afiedt.buf

  1 create procedure get_test2_data( p_cursor in out sys_refcursor )   2 as
  3 begin
  4 open p_cursor for select * from test2;   5* end;
SQL> / Procedure created.

SQL> commit;

Commit complete.

SQL> variable x refcursor
SQL> exec get_test2_data(:x);

PL/SQL procedure successfully completed.

SQL> print x

FIELD1 FIELD2
---------- ----------

         3          4
         5          6
         7          8
         9         10
         1          2

6 rows selected.

. . . the test2 table has 2 integer columns, field1 and field2. I
translated what you wrote to the language I am using and the following prints out:

name = FIELD1, length = 22
name = FIELD2, length = 22

. . . I went ahead and did a define using the new statement handle and the
position of '1' (which does not make sense to me), then started fetching rows and printing them. What prints out is:

3
5
7
9
1

. . . I can not get field2 to print. I tried some different things like
doing a define with position 2, and nothing. Any suggestions how to pull out the values for all the columns?

Thanks.

-g Received on Mon May 29 2006 - 15:30:27 CDT