---
layout: post
title: ORA-01002
category : Oracle
tags : [Oracle, Database, DBA, Exception]
---

reproduct ORA-01002

create table

	create table test as select object_id,object_name from dba_objects where rownum <= 100;

execute script
	
	declare
	   cursor v_cursor is select * from test for update;
	begin
	   for v in v_cursor
	   loop
		update test set object_id = 0 where current of v_cursor;
		commit;
	   end loop;
	end;
	/

	ERROR at line 1:
	ORA-01002: fetch out of sequence
	ORA-06512: at line 4


	
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: 

* Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 
* If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 
* Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
	
Action: 

* Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch. 
* Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE. 
* Reexecute the statement after rebinding, then attempt to fetch again.

## Reference

* Oracle Database Error Message