2007/12/21

KB-ORA-14551: cannot perform a DML operation inside a query

當建立 Oracle Function 時,Build/complier 都沒有問題,但是執行就有問題,而且只有在 Select 中執行才會有問題。

create or replace function ufn_test return number is
    begin
        insert into testlog (d) values ('test');
    return 1;
end;
 
--execute
select ufn_test() as a from dual;

會出現以下訊息…

ORA-14551: cannot perform a DML operation inside a query

You need to write a separate procedure/function for executing the DML because oracle does not support executing DML inside the function.

or

cannot perform a DDL, commit or rollback inside a query or DML

後來在網路上找到很多同病相憐的人,整理一下,如果 Function 使用在 Select 語法中就會出現類似的錯誤:

  • Have OUT or IN OUT parameters
  • Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.

交易通常有幾種等級(以 COM+舉例):

  • 停用(Disabled)
  • 不支援(Not Supported)
  • 支援(Supported)
  • 必要(Required)
  • 需要新增(RequiresNew)

在 Oracle Function 中如果遇到交易的狀況,唯一的解決方式就是"需要新增"(RequiresNew),另起一個 Transaction,

可以使用 PRAGMA AUTONOMOUS_TRANSACTION; 來做。

CREATE OR REPLACE FUNCTION ufn_test RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO testlog(d) VALUES ('test');
    COMMIT;
    RETURN 1;
END;

 

問題就可以解決,不過要思考的地方是一致性的問題,如果會有這類的考量,建議改用 Store Procedure 來做會比較好。

才不會主程序沒有確認,在 Function 中就已經更新。

 

 

參考文章:

http://forums.oracle.com/forums/thread.jspa?threadID=598160&tstart=0

 

AUTONOMOUS_TRANSACTION Pragma

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems3.htm#32732

沒有留言: