Cursor variables, Stored Procedures & Pro C

From: pamela cohen <pcohen_at_gte.com>
Date: Fri, 24 Jul 1998 18:19:28 -0400
Message-ID: <35B90870.E48F8900@gte.com>

I want to beable to call one stored procedure from a Proc C routine to open a cursor and then call a second stored procedure to perform a fetch from that cursor.

According to the Oracle documentation, if you defined & declare a cursor variable and then open the cursor variable, the pointer into the query work area should remain accessable from one scope to another. However it does not seem to do so. I receive a runtime error of ( ORA-01001: invalid_cursor) if I try and open a cursor in one pl/sql block and then fetch in another. Does anyone know why? Should it work this way?

Thanks in advance for your help!
pamela

Here's what I do in my code:

1. stored procedure definition create or replace package my_package_name as type my_rec is RECORD ( prefix varchar2(32); startn number, endn number);

    type my_cursor is REF CURSOR return my_rec;

   procedure openmycursor(cur_variable IN OUT my_cursor, , , );    procedure fetchrowfrommycursor(cur_variable IN OUT my_cursor,

                                                       returnrec OUT

my_rec );

end my_package_name;

create or replace package body my_package_name as

        procedure openmycursor (cur_variable  IN OUT my_cursor, , ,) is
            begin
                      open cur_variable for select ........;
        end openmycursor;

        procedure fetchrowfrommycursor(cur_variable IN OUT my_cursor,
                                                             returnrec
OUT my_rec) is
            begin
                        fetch cur_variable into returnrec;
            end fetchrowfrommycursor;

end my_package_name;

2) My Pro C code
...
#include <oraca.h>

exec sql include sqlca;

exec sql whenever sqlerror do my_error_code();

int
main()
{

    [connect to database with user ID ]

   exec sql begin declare section;
     sql_cursor cursor_variable;

     typedef struct my_rec {
     }my_rec_type;

   my_rec_type     return_rec;

   exec sql end declare section;

   exec sql allocate :cursor_variable;

   exec sql execute

         begin
         my_package_name.openmycursor(:cursor_variable, , , );
         end;

   end-exec;

     [ more user code here ]

    /* I receive a runtime error 'ORA-01001: invalid_cursor' on the first iteration of the loop */

   /* Note: I can do many "exec sql fetch :cursor_variable into :returnrec;"

• after the 'exec sql execute open... end-exec', but having the stored
• procedure appear in a different pl/sql block seems to lose cursor
• context */ exec sql execute begin loop my_package_name.fetchrowfrommycursor(:cursor_variable, :return_rec);

          exit when :cursor_variable%notfound;
          end loop;

        end;

    end-exec;
 } Received on Fri Jul 24 1998 - 17:19:28 CDT