ORA-14551: cannot perform a DML operation inside a query,ora-14551dml

來源:互聯網
上載者:User

ORA-14551: cannot perform a DML operation inside a query,ora-14551dml

在Oracle函數中進行insert操作就會報 ORA-14551: cannot perform a DML operation inside a query

問題原因:
對資料庫有寫操作(insert、update、delete、create、alter、commit)的函數,無法簡單的用SQL來調用的

解決方案:

需要再聲明添加PRAGMA AUTONOMOUS_TRANSACTION;


例如:

create or replace function update_contractDate(v_contractId in contract.id%type, v_contractBeginDateStr in varchar2)
return number
as
pragma autonomous_transaction;

PRAGMA AUTONOMOUS_TRANSACTION聲明為自治事務

當前的函數作為已有事務的子事務運行,子事務的commit,rollback操作不影響父事務的狀態
在一個事務中可以定義一個或幾個自治事務,自治事務可以獨立commit,不會對外層事務產生影響,同樣外層事務的rollback也對自治事務沒有影響,通常可以考慮將自治事務定義成一個過程,在外層的事務中調用。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.