Nested cursors in Pro*C?

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 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. Received on Sun Mar 21 1999 - 23:59:28 CST