Re: Nested cursors in Pro*C?

In article <36F5DC40.3286AAFC_at_cardsetc.com.au>,   James Webster <jamesw_at_cardsetc.com.au> wrote:
> Hello everyone,
>
> It is possible to nest cursors in Pro*C applications... ie.
>
> EXEC SQL DECLARE CURSOR1;
> EXEC SQL OPEN CURSOR1;
> EXEC SQL FETCH CURSOR1;
>
> while FETCH OK
> {
> ...do something...
>
> EXEC SQL DECLARE CURSOR2;
> EXEC SQL OPEN CURSOR2;
> EXEC SQL FETCH CURSOR2;
>
> while FETCH OK
> {
> ...do something...
> EXEC SQL FETCH CURSOR2;
> }
>
> EXEC SQL CLOSE CURSOR2;
>
> ...do something...
>
> ### EXEC SQL FETCH CURSOR1; ###
> }
>
> EXEC SQL CLOSE CURSOR1;
>
> Presently, Oracle returns ORA-01002, 'Fetch out of sequence' when it
> reaches the line marked with hashes (#). Also note, that in my code,
> the ...do something...'s before and after the nested cursor include SQL
> statements, some of which modify the contents of the database. They
> don't however, touch the tables utilised by CURSOR2 - this is all
> contained within a transaction anyhow.

Do you mean here that maybe you modify tables utilized by CURSOR1?

Also, by "contained within a transaction anyhow" do you imply that you are doing a commit before the next fetch of CURSOR1?

>
> Do the values of the MODE, MAXOPENCURSORS, HOLD_CURSOR and
> RELEASE_CURSOR parameters affect this? I am currently operating Pro*C
> under ANSI mode.
>
> Thanks!
>
> Regards,
> James W.
>

Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 22 1999 - 12:10:02 CST