表结构如下
| 字段 | 类型 |
| col1 | varchar2(64) |
| col2 | long |
-- 复制数据
insert into mytable(col1,col2)
select col1 || ' _copy ' ,col2 from mytable where col1 = ' 11 '
-- 出现ORA-00997: 非法使用 LONG 数据类型的错误
解决方案：
存储过程
-- tid 被复制行id
create or replace procedure CopyRow(tid varchar2 )
as
cursor mycur
is select col1,col2 from mytable where col1 = tid;
crow mycur % rowtype;
begin
open mycur;
loop
fetch mycur into crow;
exit when bcur % notfound;
-- 测试下面的sql
-- insert into(col1,col2) select crow.col1||'-copy',crow.col2 from dual
-- 也会出现ORA-00997的错误
-- insert 然后update 成功
insert into mytable(col1)
select crow.col1 || ' -copy ' from dual;
update mytable set col2 = crow.col2 where col1 = crow.col1 || ' -copy ' ;
end loop;
close bcur;
end ;