CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11grollback
Andrew Daw
Andrew Daw

Reputation: 29

Strange error inserting rows followed by a rollback in oracle11g

Writing a script to test a (to be written) trigger, but I am getting a strange error message if I rollback the change. Here is the test script

    insert into prescription values(
  143, 115, 
  to_date('January 15, 1989, 11:00 A.M.',
  'Month dd, YYYY, HH:MI A.M.',
  'NLS_DATE_LANGUAGE = American'),
  5, 12, 'hours', 12);
select * from prescription;
rollback;

After scrolling about half way through the rows returned by the select statement the table evaporates and I get hit by an error message:

ORA-01002: fetch out of sequence
01002. 00000 -  "fetch out of sequence"
*Cause:    This error means that a fetch has been attempted from a cursor
           which is no longer valid.  Note that a PL/SQL cursor loop
           implicitly does fetches, and thus may also cause this error.
           There are a number of possible causes for this error, including:
           1) Fetching from a cursor after the last row has been retrieved
           and the ORA-1403 error returned.
           2) If the cursor has been opened with the FOR UPDATE clause,
           fetching after a COMMIT has been issued will return the error.
           3) Rebinding any placeholders in the SQL statement, then issuing
           a fetch before reexecuting the statement.
*Action:   1) Do not issue a fetch statement after the last row has been
           retrieved - there are no more rows to fetch.
           2) Do not issue a COMMIT inside a fetch loop for a cursor
           that has been opened FOR UPDATE.
           3) Reexecute the statement after rebinding, then attempt to
           fetch again.

If I run the script without the rollback and then issue the rollback separately everything is fine. But that will make for an annoying test unit later on.

Upvotes: 0

Views: 284

Answers (1)

Andrew Daw
Andrew Daw

Reputation: 29

Found out the problem! Turns out oracle SQL developer only returns 50 rows at a time by default. So after I start scrolling down it will go off and fetch the next 50 rows which makes for possibly invalid results seen as the select was issued before the rollback and I get an error. Fixed by going into preference->database and changing the return size which luckily for me was just inside the max amount they allow (200 results).

Upvotes: 1

Related Questions