|Ora-01002 by select for update in SQL statement [message #589241]
||Wed, 03 July 2013 11:03
Registered: January 2012
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
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
SELECT oraerror_seq.nextval INTO id FROM dual;
n := ora_sql_txt(sql_text);
IF n >= 1
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
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
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);
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.