本篇文章主要介绍了" 通过自治事务解决ORA-14551: 无法在查询中执行 DML 操作"，主要涉及到方面的内容，对于Oracle感兴趣的同学可以参考一下：
如创建以下程序：[sql] view plain copycreate or replace function fun_test varchar2 is ...
如创建以下程序：
[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 操作，包括了方面的内容，希望对Oracle有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_3566215.html
