ORA-01002: fetch out of sequence

ORA-01002: fetch out of sequence

错误定位：
 
sql> !oerr ora 01002
 01002, 00000, "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.
 
sql>

查看pl sql：

declare
  cursor emp_cursor is
    select * from emp_text for update;
  v_object_name emp_text%rowtype;
begin
  open emp_cursor;
  loop
    fetch emp_cursor
      into v_object_name;
    if emp_cursor%found then
      update emp_text
        set object_id =
            (emp_seq.nextval)
      where object_name = v_object_name.object_name;
      COMMIT;
    end if;
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
 end;

正确写法：

declare
  cursor emp_cursor is
    select * from emp_text for update;
  v_object_name emp_text%rowtype;
begin
  open emp_cursor;
  loop
    fetch emp_cursor
      into v_object_name;
    if emp_cursor%found then
      update emp_text
        set object_id =
            (emp_seq.nextval)
      where object_name = v_object_name.object_name;
    end if;
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
  COMMIT;
end;