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

Source: Internet
Author: User

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

The ORA-14551: cannot perform a DML operation inside a query will be reported when the insert operation is performed in the Oracle Function

Cause:
Functions that have write operations (insert, update, delete, create, alter, and commit) on the database cannot be called simply by SQL.

Solution:

You need to add PRAGMA AUTONOMOUS_TRANSACTION again;


For example:

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 declared as an autonomous transaction

The current function runs as a subtransaction of an existing transaction. The commit and rollback operations of the subtransaction do not affect the status of the parent transaction.
One or several autonomous transactions can be defined in a transaction. Autonomous transactions can be independently committed without affecting external transactions. Similarly, the rollback of outer transactions does not affect autonomous transactions, generally, you can consider defining an autonomous transaction as a process that is called in an outer transaction.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

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.