If I do this i receive
ORA-01002: fetch out of sequence
This is the correct behavior. When you open a cursor with a for update
clause all the records in the set are locked. When you issue a commit all
the records are released. When the second fetch is executed the error should
be received and the result should be an update of the only first record.
You need to do it in another way:
CURSOR extract_cursor IS
SELECT t1.rowid, 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;
BEGIN
FOR extract_record IN extract_cursor
LOOP
SELECT 'x'
FROM BILLING
WHERE rowid = extract_record.rowid
FOR UPDATE;
UPDATE BILLING SET EXTRACTED = 'Y', DATE_EXTRACTED = current_date
WHERE rowid = extract_record.rowid;
COMMIT;
END LOOP;
END;
Steve <stevek_at_binc.net> wrote in message
news:7rmf0d$gpi$1_at_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 Wed Sep 15 1999 - 05:36:46 CDT
