ORA-14551: 无法在查询中执行 DML 操作 解决方法


--创建测试表
create table fn_dml_test
(
  ID    NUMBER(20),
  text  VARCHAR2(200)
)

--创建函数
CREATE OR REPLACE FUNCTION fn_test(ID NUMBER) RETURN NUMBER
IS
/*
14551, 00000, "cannot perform a DML operation inside a query "
// *Cause:  DML operation like insert, update, delete or select-for-update
//          cannot be performed inside a query or under a PDML slave.
// *Action: Ensure that the offending DML operation is not performed or
//          use an autonomous transaction to perform the DML operation within
//          the query or PDML slave.
*/
pragma  AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO fn_dml_test(id,text) values(ID,'success');
  COMMIT;
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
  INSERT INTO fn_dml_test(id,text) values(ID,'fail');
  COMMIT;
  RETURN 0;
END;

--查询验证
SELECT fn_test(1) from dual