Oracle Error Code 1002
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Ora-01002 Fetch Out Of Sequence In Oracle 11g
ORA-01002: fetch out of sequence tips Oracle Error Tips by Burleson Consulting fetch out of sequence in oracle cursor The Oracle oerr utility notes this on the ora-01002 error: ORA-01002: fetch out of sequence Cause: This error means that a fetch java.sql.sqlexception: ora-01002: fetch out of sequence 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,
Ora-01002 Fetch Out Of Sequence Ref Cursor
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 re-executing the statement. Action: 1) Do not issue a fetch statement after the last row has been retrieved - there
How To Solve Ora 01002 Fetch Out Of Sequence
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) Re-execute the statement after rebinding, then attempt to fetch again. ORA-01002 can have multiple causes including: A PL/SQL loop does fetches without notice Attempting to fetch from a cursor that is no longer valid (fetching from a row which has been retrieved). Fetching after a COMMIT has already been issued and a cursor is opened with the FOR UPDATE clause. Issuing a fetch before re-executing a SQL after rebinding placeholders. You may want to try using cursor attributes to dodge ORA-01002 in the future. To resolve a current ORA-01002, there are three actions you can perform: After the last record is received, do not issue a fetch Inside a fetch loop on a SELECT FOR UPDATE, do not use a COMMIT Try fetching again after re-executing the statement (after rebinding) �� Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! A
ExpressionsReport Column PageResult SetSelect QuerySequenceSQL PlusStored Procedure FunctionSubquerySystem PackagesSystem Tables ViewsTableTable JoinsTriggerUser PreviliegeViewXML"ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop. : Cursor Fetch«Cursor«Oracle PL / SQLOracle PL / SQLCursorCursor Fetch"ORA-1002: fetch sql error: 1002, sqlstate: 24000 out of sequence" because of the commit inside the SELECT..FOR UPDATE
Ora-01002 Fetch Out Of Sequence Ora-02063 Preceding Line From
loop. SQL> SQL> SQL> CREATE TABLE lecturer ( 2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 ora 01002 fetch out of sequence select last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 ); Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10001, 'Scott', 'Lawson','Computer Science', http://www.dba-oracle.com/t_ora_01002_fetch_out_of_sequence.htm 11); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits) 2 VALUES (10002, 'Mar', 'Wells','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10004, 'Man', http://www.java2s.com/Code/Oracle/Cursor/ORA1002fetchoutofsequencebecauseofthecommitinsidetheSELECTFORUPDATEloop.htm 'Kyte','Economics', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10005, 'Pat', 'Poll','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10006, 'Tim', 'Viper','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10007, 'Barbara', 'Blues','Economics', 7); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10008, 'David', 'Large','Music', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10010, 'Rose', 'Bond','Music', 7); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3); 1 row created. SQL> SQL> select * from lecturer; ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS -------- -------------------- -------------------
exactly what was being done wrong and I could easily to correct the error. https://db24all.wordpress.com/2011/05/26/oracle-error-ora-1002-fetch-out-of-sequence/ However a few days ago when I got this error message http://dbaforums.org/oracle/index.php?showtopic=20940 in some developments of mine and I was not knowing what to do, I was stupefied with the error and i thought : "What is it?" Below I will describe the two scenarios that rise this type of error. The incorrect use of "FOR UPDATE" fetch out in a cursor and "COMMIT" statement The execution of the code listed below is interrupted with the "ORA-1002 Fetch out of sequence" error. The "COMMIT" location and the "FOR UPDATE" statement in the cursor are not conjugates of the better way. DECLARE CURSOR c_getLogInfo IS SELECT * FROM APPL_LOG WHERE TYPE_ID =1 FOR UPDATE; lr_log_info c_getLogInfo%ROWTYPE; ln_count fetch out of NUMBER:=1; BEGIN OPEN c_getLogInfo; FETCH c_getLogInfo INTO lr_log_info; WHILE(c_getLogInfo%FOUND) LOOP UPDATE APPL_LOG SET MESSAGE='Finished.' WHERE LOG_ID = lr_log_info.LOG_ID AND TYPE_ID = lr_log_info.TYPE_ID; COMMIT; ln_count := ln_count + 1; FETCH c_getLogInfo INTO lr_log_info; END LOOP; CLOSE c_getLogInfo; END; To avoid this error you must remove the "FOR UPDATE" statement from the cursor, or else in alternatively you may keep the "FOR UPDATE" statement but change the "COMMIT" location, put it after closing the cursor. Another solution is by the use of "SAVE POINT" statement, as is shown below: DECLARE CURSOR c_getLogInfo IS SELECT DENSE_RANK() OVER (PARTITION BY TYPE_ID ORDER BY TYPE_ID) IDX, l.* FROM APPL_LOG l WHERE TYPE_ID IN (1,2) FOR UPDATE; lr_log_info c_getLogInfo%ROWTYPE; ln_log_idx NUMBER := 1; ln_idx NUMBER := 1; ls_savepoint VARCHAR2(128); BEGIN OPEN c_getLogInfo; FETCH c_getLogInfo INTO lr_log_info; WHILE( c_getLogInfo%FOUND ) LOOP ls_savepoint := 'log_info_'||LPAD(ln_idx,10,'0'); EXECUTE IMMEDIATE 'SAVEPOINT '||ls_savepoint ; UPDATE APPL_LOG SET SEQ_ID = LOG_ID WHERE LOG_ID = lr_log_info.LOG_ID; IF ( ln_log_idx = 1 ) THEN EXECUTE IMMEDIATE 'ROLLBACK TO SAVEPOINT '||ls_savepoint; END IF; ln_log_i