Re: Problem with Cursor and Commit

> I just wrote a simple little program to test the affect of a submit on an
> open
> cursor under Solaris, and discovered that a commit has no adverse affect
> on an open cursor. The code appears below. Table "FOO" has eight
> records of two numbers and table "BAR" has no records. The code
> segment reads the entries from FOO and places them into BAR,
> committing after every 2nd row. I don't know, however, if there is a
> difference under HP/UX.
>
> EXEC SQL DECLARE MyCur CURSOR FOR
> SELECT * FROM FOO;
>
> EXEC SQL WHENEVER NOT FOUND DO break;
> EXEC SQL OPEN MyCur;
> count = 1;
> while(1){
>
> EXEC SQL FETCH MyCur INTO :f1, :f2;
>
> EXEC SQL INSERT INTO BAR VALUES (:f1,:f2);
>
> if(count == 2){
> count = 1;
> EXEC SQL COMMIT;
> }else{
> count++;
> }
> }
>
> EXEC SQL COMMIT WORK RELEASE;
>
>
> --- Rich
>

From memory, Oracle's documentation states that a commit 'invalidates' the cursor. It also releases your locks.

In my experience - the latter is not an issue until the program reaches a point where 'snapshot too old' could be expected. How long this takes would seem to depend on system activity and rollback In most small runs the above should work fine, but in larger runs (eg 4-24+ hrs in my experience on a data warehousing platform) I'd expect the program to fail.

Cheers,

Richard Gowan Received on Sat Mar 17 2001 - 13:45:53 CST