Reputation: 29
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
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