如创建以下程序:
[sql] view plain copy
create or replace function fun_test varchar2 is
Result varchar2(200);
cursor cur_row is
select id, msg
from (select t.*
,sum(lengthb(t.msg)) over(order by crt_dttm) len
from p17_etl_sms t
where 1 = 1
and sms_dttm is null)
where 1 = 1
and len <= 30;
begin
Result := '';
for c_r in cur_row loop
Result := Result || '[' || c_r.msg || ']';
update p17_etl_sms
set sms_dttm = sysdate
where 1 = 1
and id = c_r.id;
end loop;
commit;
return(Result);
end FUN_GET_ETL_MSG;
那么在执行 select FUN_GET_ETL_MSG from dual 时,就会报 “ ora-14551 无法在查询中执行DML操作”
现可以通过 自治事务,解决此问题
[sql] view plain copy
create or replace function fun_test varchar2 is
Result varchar2(200);
cursor cur_row is
select id, msg
from (select t.*
,sum(lengthb(t.msg)) over(order by crt_dttm) len
from p17_etl_sms t
where 1 = 1
and sms_dttm is null)
where 1 = 1
and len <= 30;
pragma autonomous_transaction;
begin
Result := '';
for c_r in cur_row loop
Result := Result || '[' || c_r.msg || ']';
update p17_etl_sms
set sms_dttm = sysdate
where 1 = 1
and id = c_r.id;
end loop;
commit;
return(Result);
end FUN_GET_ETL_MSG;
OK, 可以执行 select FUN_GET_ETL_MSG from dual 了。
通过自治事务解决ORA-14551: 无法在查询中执行 DML 操作
最新推荐文章于 2021-12-29 12:30:31 发布