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 previous message
Andrey_R
Messages: 309
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.


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


Current Time: Mon Jan 27 19:58:41 CST 2020