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;

ORA-01002: fetch out of sequence

原文：http://blog.csdn.net/rhys_oracle/article/details/18563971