Ora-01002 by select for update in SQL statement [message #589241] |
Wed, 03 July 2013 11:03 |
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 #589247 is a reply to message #589246] |
Wed, 03 July 2013 11:52 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 03 July 2013 19:49ORA-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 #589249 is a reply to message #589248] |
Wed, 03 July 2013 12:49 |
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 #589251 is a reply to message #589250] |
Wed, 03 July 2013 13:04 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 03 July 2013 21:00Quote: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 #589484 is a reply to message #589253] |
Sun, 07 July 2013 08:04 |
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
|
|
|