Home » Developer & Programmer » Precompilers, OCI & OCCI » Ora-01002 by select for update in SQL statement (Oracle 11.2.0.1.0 on OEL 5.7 (VM))
Ora-01002 by select for update in SQL statement [message #589241] Wed, 03 July 2013 11:03 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I'm having a strange case of ORA-01002 Fetch out of sequence when I perform a process in the application.

Application logs, trace files (produced with dbms_monitor),
A system trigger to capture the error - all proved that the culprit is a single SQL,
*Not a PL/SQL block* That caused the issue:


Select COL1, COL2, COL3, COL4, rowid
FROM TAB1
WHERE COL1 = :1
AND   COL2 = :2
AND   COL3 = :3
ORDER BY COL1 ASC, COL2 ASC, COL4 ASC
FOR UPDATE NOWAIT;


binds are: 'AAA' , 10000 , 0

Also, My trigger looks like this:

CREATE OR REPLACE TRIGGER after_error
 AFTER SERVERERROR ON DATABASE
 
DECLARE
 pragma autonomous_transaction;
 id NUMBER;
 sql_text ORA_NAME_LIST_T;
 v_stmt CLOB;
 n NUMBER;
BEGIN
 SELECT oraerror_seq.nextval INTO id FROM dual;
 n := ora_sql_txt(sql_text);
 IF n >= 1
 THEN
 FOR i IN 1..n LOOP
 v_stmt := v_stmt || sql_text(i);
 END LOOP;
 END IF;

 FOR n IN 1..ora_server_error_depth LOOP
IF ora_server_error(n) in (  '1002')
 OR ( (ora_server_error(n) = '1476' ) and (instr(v_stmt,'/* OracleOEM') =0) ) -- execption bug in Oracle OEM
 THEN
   INSERT INTO system.oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(n), ora_server_error_msg(n), v_stmt);
   COMMIT;
 END IF;
END LOOP;
 --
END after_error;




I've read some about this error and everywhere it says that it has to do with fetching from an invalid cursor,
And all the examples I've observed are of PL/SQL block - never seen any example/explanation of how it happens in a SQL query.

I wonder if the OCI maybe is somehow doing things different than the logs/triggers show?
Is there anyone who can guide me and help me to shed some light over this issue?

Thanks in advance.


Re: Ora-01002 by select for update in SQL statement [message #589246 is a reply to message #589241] Wed, 03 July 2013 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.
Re: Ora-01002 by select for update in SQL statement [message #589247 is a reply to message #589246] Wed, 03 July 2013 11:52 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 03 July 2013 19:49
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.


Did you read my post at all?
Why are you being a robot?

Regards,
Andrey
Re: Ora-01002 by select for update in SQL statement [message #589248 is a reply to message #589247] Wed, 03 July 2013 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
i posted what you SHOULD post by yourself.
Do you think we know all error codes by heart?
Why don't you correctly post?

Regards
Michel
Re: Ora-01002 by select for update in SQL statement [message #589249 is a reply to message #589248] Wed, 03 July 2013 12:49 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
I don't understand, why would I post documentation???

I've posted that I get "ORA-01002 Fetch out of sequence".

I've posted the query that invokes the error as appears in application log and database trigger.

I cannot invoke this error by executing the query from SQL*Plus,
And I am asking for advice regarding finding out what is causing the error,
while stating some suspicions about it being related to the way OCI sends it to the database,
But don't know how to prove it (tried a couple of OCI debuggers that didn't work on my Windows7 machine.



Re: Ora-01002 by select for update in SQL statement [message #589250 is a reply to message #589249] Wed, 03 July 2013 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
hy would I post documentation???


To prevent us from wasting our time to do it!
This is YOUR question, YOUR problem, YOU should post everything about it, INCLUDING the related documentation.

The time we spend to understand and complete your question is time we don't spend to find a solution or an explanation.
And the time I waste to explain these obvious things is time I don't spend to investigate in the question.

Regards
Michel
Re: Ora-01002 by select for update in SQL statement [message #589251 is a reply to message #589250] Wed, 03 July 2013 13:04 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 03 July 2013 21:00
Quote:
hy would I post documentation???


To prevent us from wasting our time to do it!
This is YOUR question, YOUR problem, YOU should post everything about it, INCLUDING the related documentation.

The time we spend to understand and complete your question is time we don't spend to find a solution or an explanation.
And the time I waste to explain these obvious things is time I don't spend to investigate in the question.

Regards
Michel


Sorry, I thought this part was obvious.
I will note that for the future.

What about the actual issue? any ideas?

Regards,
Andrey
Re: Ora-01002 by select for update in SQL statement [message #589252 is a reply to message #589251] Wed, 03 July 2013 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't have time to investigate on it till now.

Regards
Michel

[Updated on: Wed, 03 July 2013 13:24]

Report message to a moderator

Re: Ora-01002 by select for update in SQL statement [message #589253 is a reply to message #589252] Wed, 03 July 2013 13:31 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 03 July 2013 21:24
Didn't have time to investigate on it till now.

Regards
Michel


What do you mean by this?


Regards,
Andrey

[Updated on: Wed, 03 July 2013 13:31]

Report message to a moderator

Re: Ora-01002 by select for update in SQL statement [message #589484 is a reply to message #589253] Sun, 07 July 2013 08:04 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Very mature Michel. I made an effort to provide all the needed details,
And when you requested me to change my approach I accepted it and said I'll take it into account next time.

In the meantime, you've chosen to give me a hard time, I guess it's what you get your kicks from.


For anyone who's interested in the solution to this (related to versions of Magic 9.4 prior to service pack sp8g):

It was a problem in the Gateway or Magic SP8e platform, and it was fixed by implementing the SP8g service pack,
which contained a newer "MGORA8.dll" file that contained logic to prevent getting this error.


Regards,
Andrey

[Updated on: Sun, 07 July 2013 08:05]

Report message to a moderator

Previous Topic: analytic functions in Pro*C - rank()
Next Topic: Pro*C++ Resultset Data types question
Goto Forum:
  


Current Time: Thu Mar 28 18:36:38 CDT 2024