Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PLSQL Error: ORA-01002: fetch out of sequence
Without seeing the actual code for the procedure, this is only a general response:
The most common reason for this error is the cursor being closed and then another fetch being issued - check your code for nested loops and be sure the close cursor ( explicit and implicit ) instructions are properly 'scoped' - that is they operate in the correct sequence ( even when exceptions are raised in one of the loops...)
hth,
John Greco
D Hauser <david.hauser_at_ualberta.ca> wrote:
>Hi there,
>
>I am writing a stored package to load some data from a temp table to a
>prod table. The proc. uses a for cursor loop where the contents of the
>load table are held in the cursor.
>
>It also uses SAVEPOINTs to implement an optimal commit requirement where
>a save point is set every time the DB is successfully written to and
>after OPTIMAL_COMMIT_LEVEL number of writes have been made, a commit is
>executed.
>
>The cursor isNOT an UPDATEable one...here is my error
>UNEXPECTED ERROR: ORA-01002: fetch out of sequence
>
>If you have any ideas please let me know,
>
>Thanks,
>David
>
>PS: here is the OERR o/p
>
>SQL> host oerr ora 01002
>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.
>
>SQL>
-----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
http://www.newsfeeds.com The Largest Usenet Servers in the World!
------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----
Received on Tue Nov 09 1999 - 14:55:07 CST