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

陈晓龙

--创建测试表

create table fn_dml_test

(

  ID    NUMBER(20),

  text  VARCHAR2(200)

)

--创建函数

CREATE OR REPLACE FUNCTION fn_test(IDNUMBER) RETURN NUMBER

IS

/*

14551, 00000, "cannot perform a DMLoperation inside a query "

// *Cause:  DML operation like insert,update, delete or select-for-update

//          cannotbe performed inside a query or under a PDML slave.

// *Action: Ensure that the offending DMLoperation is not performed or

//          use anautonomous transaction to perform the DML operation within

//          thequery or PDML slave.  www.2cto.com  

*/

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

万望

万望

顶了，哈哈

万望

陈晓龙

万望 发表于 2016-9-4 22:25

真给面子

陈晓龙

万望 发表于 2016-9-4 22:24
顶了，哈哈