ORA-01002: fetch out of sequence

最新推荐文章于 2023-02-24 15:46:11 发布

原创最新推荐文章于 2023-02-24 15:46:11 发布 · 3.7k 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE 之 error 诊断专栏收录该内容

35 篇文章

订阅专栏

当遇到ORA-01002错误时，通常是因为尝试从不再有效的游标中获取数据。可能的原因包括：1) 在已检索完所有行并返回ORA-1403错误后仍尝试获取；2) 在带有FOR UPDATE子句打开的游标中执行COMMIT；3) 重新绑定SQL语句中的占位符后，未重新执行就尝试获取。解决方案包括：1) 不要在检索完最后一行后尝试获取；2) 不要在FOR UPDATE游标的循环内执行COMMIT；3) 重新执行语句后再尝试获取。

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;