Ora 01002 Error
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 java.sql.sqlexception: ora-01002: fetch out of sequence Ion Excel-DB Don Burleson Blog
Ora-01002 Fetch Out Of Sequence Ref Cursor
ORA-01002: fetch out of sequence tips Oracle Error Tips by
How To Solve Ora 01002 Fetch Out Of Sequence
Burleson Consulting The Oracle oerr utility notes this on the ora-01002 error: ORA-01002: fetch out of sequence Cause: This error means that a fetch has been attempted
Ora-01002 Fetch Out Of Sequence Ora-02063 Preceding Line From
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 fetch out of sequence in oracle cursor 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 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
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More ora-01002 fetch out of sequence hibernate discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 5 ora 01002 fetch out of sequence select Replies Latest reply on May 4, 2011 11:48 AM by pollywog Error ORA-01002: fetch out of sequence 567881 May 4, 2011 10:26 AM sql error: 1002, sqlstate: 24000 Hi all, I created 3 cursors. The scenario is like following : Begin Cur A Begin FOR xx IN Cur B LOOP Begin FOR xx IN Cur C LOOP End LOOP Cur C; commit; End LOOP Cur B; End http://www.dba-oracle.com/t_ora_01002_fetch_out_of_sequence.htm Cur A; When i run the program its display error ORA-01002: fetch out of sequence. The objective of my program is to insert into table and commit every transaction. Kindly share info with me why this error happened. TQ. tim I have the same question Show 0 Likes(0) 35908Views Tags: none (add) This content has been marked as final. Show 5 replies 1. Re: Error ORA-01002: fetch out of sequence Thierry H. May 4, 2011 10:31 AM (in https://community.oracle.com/thread/2219240 response to 567881) Tim, Refer to the following: 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. HTH, Thierry Edited by: Thierry H. on May 4, 2011 12:30 PM Reformatting Like Show 0 Likes(0) Actions 2. Re: Error ORA-01002: fetch out of sequence Hoek May 4, 2011 10:32 AM (in response to 567881) Kindly share info with me why this error happened. Because you're committing in a loop. Never commit in a loop, commit only once, when the complete transaction is done. http://asktom.oracle.com/pls/asktom/f?p
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 out http://www.java2s.com/Code/Oracle/Cursor/ORA1002fetchoutofsequencebecauseofthecommitinsidetheSELECTFORUPDATEloop.htm of sequence" because of the commit inside the SELECT..FOR UPDATE loop. SQL> SQL> SQL> CREATE TABLE lecturer ( 2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name http://www.dbasupport.com/forums/showthread.php?11795-ORA-01002-fetch-out-of-sequence-Please-Help-Urgent 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', 11); 1 fetch out 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', 'Kyte','Economics', 8); 1 fetch out of 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 -------- -------------------- -------------------- ------------------------------ --------------- ######## Scott Law
out of sequence. Please Help-Urgent If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 6 of 6 Thread: ORA-01002: fetch out of sequence. Please Help-Urgent Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 06-08-2001,11:03 AM #1 anuragmin View Profile View Forum Posts Senior Member Join Date Sep 2000 Posts 362 Hi, I am trying to delete about a million rows from a table using a pl/sql block but am getting an error ORA-01002: fetch out of sequence Here is the sample of the block 1 declare 2 cursor selectfordelete is select * from test where region not in ('REGROLL','WWF','WWR','CON',' 3 'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON') for upd 4 begin 5 for i in selectfordelete loop 6 delete from test where current of selectfordelete; 7 commit; 8 end loop; 9* end; 10 / declare * ERROR at line 1: ORA-01002: fetch out of sequence ORA-06512: at line 5 Please suuggest something. Thanks Anurag Reply With Quote 06-08-2001,12:28 PM #2 nealh View Profile View Forum Posts Member Join Date Apr 2001 Location UK Posts 137 I tried a simple test on my system and it seemed to work. Committing while a cursor is open is a slightly dodgy area. I believe the ANSI standard is that you shouldn't do it and a commit will actually close the cursor (which would cause the fetch out of sequence error). Oracle normally allows it unless you're using PRO*C compiled in ANSI mode. As a quick solution, you could try taking out the commit but you will need a large rollback segment to accommodate all 1 million rows. In any case, committing after every single row is a bit inefficient. You could try committing less frequently. Use something like: if mod(selectfordelete%rowcount,1000) = 0 then commit; end if; Reply With Quote 06-11-2001,07:52 AM #3 m1l View Profile View Forum Posts Junior Member Join Date Oct 2000 Posts 90 Using 'where current of ...' in the delete could be causing the problem, especially after doing a commit. I would change it to be the primary key fields. Reply With Quote 06-12-2001,06:02 AM #4 sbreddy View Profile View Forum Po