COMMIT inside FOR UPDATE cursor

From: Steve <stevek_at_binc.net>
Date: 14 Sep 1999 21:34:05 GMT
Message-ID: <7rmf0d$gpi$1@grandprime.binc.net>

I have the following code in a stored proc:

CURSOR extract_cursor IS

   SELECT SYSID, FTYPE, S_ID, RT, FTYPE, FID,

          X_TIME, TEST_SW
          FROM BILLING t1, LOOKUP_DATE t2
          WHERE TRUE_DATE BETWEEN start_date and end_date
          AND t1.DATE_ID = t2.DATE_ID
          AND EXTRACTED IS NULL
          FOR UPDATE OF EXTRACTED, DATE_EXTRACTED;

BEGIN
   output_file := UTL_FILE.FOPEN('c:\export\','expbill.dat','W');    FOR extract_record IN extract_cursor    LOOP

      UTL_FILE.PUT(output_file,RPAD(extract_record.sysid,17,' '));
      UTL_FILE.PUT(output_file,RPAD(extract_record.ftype,5,' '));
      UTL_FILE.PUT(output_file,LPAD(extract_record.s_id,9,'0'));
      UTL_FILE.PUT(output_file,LPAD(extract_record.rt,9,'0'));
      UTL_FILE.PUT(output_file,extract_record.ftype);
      UTL_FILE.PUT(output_file,LPAD(extract_record.fid,10,'0'));
      UTL_FILE.PUT(output_file,LPAD(TO_CHAR(extract_record.x_time),13,'0'));
      UTL_FILE.PUT_LINE(output_file,extract_record.test_sw);
      UTL_FILE.FFLUSH(output_file);
      UPDATE BILLING SET EXTRACTED = 'Y', DATE_EXTRACTED = current_date
         WHERE CURRENT OF extract_cursor;
      COMMIT;

   END LOOP; The last time I ran it, I processed 6,197 rows. However, in the Oracle documentation, it says that I can't do a COMMIT in a FOR UPDATE cursor, to wit:

"All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit."

I wrote the proc and tested it before I saw the ref to the "can't commit in a FOR UPDATE cursor" - as far as I knew, from experience, it was OK.

Am I not reading the proper part of the documentation?? Is the documentation wrong?? I'm running Oracle 8i Server (8.1.5) on NT

Any responses appreciated. Received on Tue Sep 14 1999 - 16:34:05 CDT