主页 / 问题 / 1120559
Accepted
Анатолий Эрнст
Asked:2020-05-05 17:02:54 +0000 UTC2020-05-05 17:02:54 +0000 UTC

错误：ORA-01410：通过 CURRENT OF 条件更改表时 ROWID 无效

  • 772

在Live SQL上执行以下脚本时，出现错误：

ORA-01410：无效的 ROWID
ORA-06512：在第 24 行
ORA-06512：在“SYS.DBMS_SQL”，第 1721 行

毕竟，光标应该指向一个特定的t2.rowid，不是吗？

测试数据：

create table t1 (
    id integer  primary key,
    val varchar2(100)
);

create table t2 (
    id integer  primary key,
    gr_id integer,
    val varchar2(100)
);

insert all
    into t1 values (1, 'A')
    into t1 values (2, 'B')
    into t1 values (3, 'C')

    into t2 values (1, 1, 'X11')
    into t2 values (2, 1, 'X21')
    into t2 values (3, 2, 'X32')
    into t2 values (4, 2, 'X42')
    into t2 values (5, 3, 'X53')
    into t2 values (6, 3, 'X63')
select * from dual;

commit;

脚本本身：

declare
    type t_rec is record (id integer, val t2.val%type);
    s_rec t_rec;
    cursor cur is
        select t1.id, t2.val
        from t1, t2
        where t2.gr_id = t1.id
        for update of t2.val nowait;
begin
    open cur;
        loop
            exit when cur%notfound;
            fetch cur into s_rec;

            update t2 set val = val||' Y'
            where current of cur;

        end loop;
    close cur;
end;
sql

1 个回答

  1. Best Answer

    在循环的最后一次迭代中，当游标结束并返回属性notfound=true时，会尝试修改无效的表ROWID。

    应始终在以下情况后立即 FETCH检查循环退出条件：

    fetch cur into s_rec;
exit when cur%notfound;
    • 3

相关问题