ORACLE|ora-14551:질의 안에 DML 작업을 수행할 수 없습니다(message displayed when calling an Oracle Stored Function)

오라클 Function내에서 DML수행시 "ora-14551:질의 안에 DML 작업을 수행할 수 없습니다" 에러가 발생함.
아울러서, ora-06512 에러가 동반함.
 

이럴경우 아래 내용을 참고하여 해결가능.

<출처: 오라클메타링크 > 

TIP:  Click help for a detailed explanation of this page.

Subject:  ORA-14551 message displayed when calling an Oracle Stored Function

--------------------------------------------------------------------------------

 

Goal

The following message is signaled:

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

when an Oracle Stored Function is used within a SELECT instruction.

The Oracle Stored Function is:

 

FUNCTION myFunction

RETURN varchar2

IS

BEGIN

INSERT INTO myTable

VALUES (...);

COMMIT;

RETURN 'OK';

END;

 

What is the cause of the problem?
 

Solution

According to the Oracle error message guide, DML operations such as inserts, updates, deletes or select-for-update cannot be performed inside a query or by a Parallel DML slave.

To workaround this issue, ensure that the offending DML operation is not performed in, or uses, an autonomous transaction to perform the DML operation within the query or by a Parallel DML slave.

The present example, add 'pragma autonomous_transaction' to the function description, as following:
 

FUNCTION myFunction

RETURN varchar2

IS

pragma autonomous_transaction;

BEGIN

INSERT INTO myTable VALUES

(...);

COMMIT;

RETURN 'OK';

END;

#출처:  http://h2k1.springnote.com/pages/4238699?print=1