Home » SQL & PL/SQL » SQL & PL/SQL » fetch out of sequence
fetch out of sequence [message #283363] Mon, 26 November 2007 23:07 Go to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
im using 3 REF cursors in my procedure implemented with BULK collect feature with LIMIT clause but getting an error i.e. fetch out of sequence.
Re: fetch out of sequence [message #283368 is a reply to message #283363] Mon, 26 November 2007 23:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try with Exit condition Before fETCH Collect.

Thumbs Up
Rajuvan
Re: fetch out of sequence [message #283373 is a reply to message #283368] Mon, 26 November 2007 23:48 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
thnx for replying but same error i.e. fetch out of sequence
Re: fetch out of sequence [message #283377 is a reply to message #283363] Tue, 27 November 2007 00:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Where it is happening ? show the output .

This may be helpfull for you

ORA-01002: "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.


Thumbs Up
Rajuvan.

[Updated on: Tue, 27 November 2007 00:03]

Report message to a moderator

Re: fetch out of sequence [message #283380 is a reply to message #283377] Tue, 27 November 2007 00:10 Go to previous messageGo to next message
ss_ss
Messages: 12
Registered: November 2007
Junior Member
while calling tht procedure in a java program.


CallableStatement stmt = con.prepareCall("BEGIN OP_INVOICE(?,?,?,?); END;");

String strDate1=args[0];

stmt.setString(1,strDate1);

stmt.registerOutParameter(2,oracle.jdbc.driver.OracleTypes.CURSOR);

stmt.registerOutParameter(3,oracle.jdbc.driver.OracleTypes.CURSOR);

stmt.registerOutParameter(4,oracle.jdbc.driver.OracleTypes.CURSOR);

stmt.execute();

rs= ((OracleCallableStatement)stmt).getCursor(2);

rs1=((OracleCallableStatement)stmt).getCursor(3);

rs3=((OracleCallableStatement)stmt).getCursor(4);
Re: fetch out of sequence [message #283382 is a reply to message #283380] Tue, 27 November 2007 00:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You fetched the cursors up till the end in your pl/sql procedure. Returned ref-cursors are supposed to be fetched by the calling code.
Previous Topic: Combine table info
Next Topic: USING TO_NUMBER and GETTING INVALID NUMBER ERROR
Goto Forum:
  


Current Time: Sun Dec 11 07:55:28 CST 2016

Total time taken to generate the page: 0.07322 seconds