Oracle Error Code Ora-01002
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 ora-01002 fetch out of sequence in oracle 11g out of sequence tips Oracle Error Tips by Burleson Consulting The Oracle oerrJava.sql.sqlexception: Ora-01002: Fetch Out Of Sequence
utility notes this on the ora-01002 error: ORA-01002: fetch out of sequence Cause: This error means that a fetch has been attempted from a
Fetch Out Of Sequence In Oracle Cursor
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
How To Solve Ora 01002 Fetch Out Of Sequence
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 are no more rows to fetch. 2) Do not issue a ora-01002 fetch out of sequence ref cursor 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! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on adver
Last updated: July 02, 2012 - 2:12 pm UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: A little known ORDER BY extension ora-01002 fetch out of sequence ora-02063 preceding line from Latest Followup You Asked Hello Tom, we've got a problem concerning ORA-01002 ora 01002 fetch out of sequence select in a PL/SQL block. As we learned this error sometimes happens under special circumstances when updating selected data, i.e. when ora-01002: fetch out of sequence c# using a select for update. But this is not the case here! The strange thing is that we're getting this error when only reading data from a cursor, without any updates http://www.dba-oracle.com/t_ora_01002_fetch_out_of_sequence.htm on the cursor data. The error occurs when executing a rollback to savepoint statement: SAVEPOINT my_savepoint; FOR my_record IN my_cursor LOOP IF my_condition THEN ROLLBACK TO SAVEPOINT my_savepoint; ELSE do_something; --> no updates on my_cursor; this data is only read --> but: update some other data (without any commit) IF another_condition THEN SAVEPOINT my_savepoint; END IF; END IF; END LOOP; COMMIT; Normally this works https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:18202801725435 fine: if there is still at least one row left to read in my_cursor, there are no problems executing the rollback to savepoint statement. But: If my_record is the last record to be read within my_cursor, and if my_condition equals to true, then the rollback to savepoint statement fails with error ORA-01002! We are using Oracle 8.1.7. Up to now we couldn't find a similar problem in any forum, so any hints are welcome. Thanks. martin Followup: Hello Tom, it took some hours of work to find out what exactly the problem is, but now I've got a little example to reproduce the error: /* --create two tables and insert one row: create table test (a integer); insert into test values (1); create table test2 (b integer); insert into test2 values (11); */ declare cursor c is select * from test; begin update test2 set b = 22; --this one's new! savepoint my_savepoint; for x in c loop if (true) then rollback to savepoint my_savepoint; else null; end if; end loop; end; / ...leads to "ORA-01002". Seems to be legal PL/SQL for me, so I hope you can help me
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn http://stackoverflow.com/questions/18606831/ora-01002-fetch-out-of-sequence-while-using-transactional more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORA-01002: fetch out of sequence while using @transactional up vote 1 down vote favorite When i try to invoke a procedure from my application got fetch out an error saying ORA-01002: fetch out of sequence Technologies used : Mybatis 3 Spring MVC The interesting point here is the error occurs only if i use @Transactional (org.springframework.transaction.annotation.Transactional) annotation for the invoking method in service class. If i remove @Transactional then there is no ORA error. I am using @Transactional as i have several DAOs injected into a Service. Please find my code pasted below. @Transactional public boolean saveavgFlyHrs(AverageFlyingHoursReport averageFlyingHoursReport) throws TransactionDataException { String status fetch out of = null; boolean isOk = false; if(averageFlyingHoursReportDAO.saveavgFlyHrs(averageFlyingHoursReport)) { status = averageFlyingHoursReportDAO.updateCheckEff(averageFlyingHoursReport.getSubFleet()); logger.debug("OUT_STATUS:"+status); if(ConstantStringUtil.SUCCESS.equalsIgnoreCase(status)) { isOk = true; } else { isOk = false; } } return isOk; } Anyone pls help me with this. spring oracle spring-mvc plsql mybatis share|improve this question asked Sep 4 '13 at 6:27 prabu 3051821 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted ORA-01002 is an Oracle error. You have not shown any Oracle code so we have to guess what happens. This error is usually thrown when you commit across a FOR UPDATE cursor, for example: SQL> CREATE TABLE TEST (ID NUMBER, c VARCHAR2(10)); Table created SQL> INSERT INTO TEST VALUES (1, 'a'); 1 row inserted SQL> INSERT INTO TEST VALUES (2, 'b'); 1 row inserted SQL> BEGIN 2 FOR cc IN (SELECT * FROM TEST FOR UPDATE) LOOP -- FOR UPDATE cursor 3 UPDATE TEST SET c = UPPER(c) WHERE ID = cc.id; 4 COMMIT; -- this will invalidate our cursor 5 END LOOP; 6 END; 7 / ORA-01002: fetch out of sequence ORA-06512: at line 3 I can imagine that adding @Transactional to a unit of work will make it commit on success/rollback on error. So maybe this code is part of a larger loop using a FOR UPDATE cursor of some sort. When you add @Transactional, it commits each