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

更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12