ORA-14551: unable to perform DML operations in the query.

Recently, when debugging a function with DML operations, it has never been successful. It is okay to test in PL/SQL. It will not work when calling a function through SQL statements, at the beginning has not found the cause, then inadvertently caught in the function code comment out, finally through SQL debugging, a pop-up "ORA-14551: unable to execute DML operations in the query. "error, find the cause of the problem, and find a solution. I found an article on the Internet to talk about autonomous transactions and main transactions. After reading this article for a long time, I am still in the fog and find the key points, it is to add a statement "Pragma autonomous_transaction;" and submit the DML operation at the end of the commit statement. The problem can be solved. As for the autonomous transaction and the main transaction, there is still time to digest it later.

---- The following is a reference:

When inserting data into a temporary table in a function, the following error occurs:

ORA-14551: unable to perform DML operations in queries

ORA-06512: In "nstcsa. ns_st_getraisefundx", Line 29

 

Add the following string:

Pragma autonomous_transaction;

 

 

A database transaction is a unit operation. Either all operations are successful or all operations fail. In Oracle, a transaction starts from executing the first data management language (DML) statement until it executes a commit statement, commits and saves the transaction, or executes a rollback statement, stop this operation.

 

It is difficult to record the error information to the database table because the transaction fails to be re-run, the insert statement used to write log entries has not been completed yet.

 

To address this dilemma, Oracle provides a convenient method, that is, autonomous transactions. An autonomous transaction starts from the current transaction and runs in its own context. They can be submitted or re-run independently without affecting running transactions. As a result, they form an ideal form of writing error log tables. When an error is detected in a transaction, you can insert a row in the error log table and submit it, and then roll back the primary transaction without losing this insert.

 

Because the autonomous transaction is separated from the primary transaction, it cannot detect the current status of the modified row. It seems that they are always in separate sessions before the primary transaction is committed, and they are unavailable for autonomous transactions. However, in turn, the situation is different: the main transaction can detect the results of self-governing transactions that have been executed.

 

To create an autonomous transaction, you must usePragma autonomous_transactionStatement. The SQL Server statements executed in such a module or process are autonomous.

 

The trigger cannot contain the commit statement unlessPragma autonomous_transactionMark. However, only the statements in the trigger can be committed, but not the primary transaction.

Exp: Create Table MSG (MSG varchar (50); homemade transactions:
Create or replace procedure autonomouse_insert is Pragma autonomous_transaction; Begin insert into MSG values ('autonomouse insert'); Commit; end;
Non-autonomous transactions: Create or replace procedure nonautonomouse_insert as begin insert into MSG values ('nonautonomouse insert'); Commit; end; SQL> begin
2
3 insert into MSG values ('this main info ');
4
5 nonautonomouse_insert;
6
7 rollback;
8
9 end
10;
11/
 
PL/SQL procedure successfully completed
 
SQL> select * From MSG;
 
MSG
--------------------------------------------------
This main info
Nonautonomouse insert
Because there is a commit in the process, rullback in the anonymous block does not work. Therefore, the rollback will affect the entire transaction in a non-autonomous transaction. Let's look at another situation:
SQL> Delete MSG;
 
2 rows deleted
 
SQL> NO commit here; SQL> begin
2
3 insert into MSG values ('this main info ');
4
5 rollback; -- rollback is added here;
6
7 nonautonomouse_insert;
8
9 rollback;
10
11 end
12;
13/
 
PL/SQL procedure successfully completed
 
SQL> select * From MSG;
 
MSG
--------------------------------------------------
This main info
Nonautonomouse insert
Nonautonomouse insert
Why is there no rollback (delete * From SSG? Because the process is a new session, the previous session is normally exited and automatically submitted at the same time. Therefore, we can see three rows of data.
SQL> commit;
 
Commit complete
 
SQL> select * From MSG;
 
MSG
--------------------------------------------------
This main info
Nonautonomouse insert
Nonautonomouse insert
 
SQL> commit;
 
Commit complete
 
SQL> select * From MSG;
 
MSG
--------------------------------------------------
This main info
Nonautonomouse insert
Nonautonomouse insert is a meaningless transaction control statement because of a new session. SQL> Delete MSG;
 
3 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select * From MSG;
 
MSG
------------------------------------------------ You can see that the submission is normal. Let's take a look at the homemade transaction: SQL> begin
2
3 insert into MSG values ('this main info ');
4
5 autonomouse_insert;
6
7 rollback;
8
9 end
10
11;
12/
 
PL/SQL procedure successfully completed
 
SQL> select * From MSG;
 
MSG
--------------------------------------------------
Autonomouse insert we can see that it is a row of data. Obviously, the first SQL insert is rollback, which proves that a self-made transaction is a transaction independent of the main program and does not affect the control of the main transaction. In addition, in the distributed environment, we often encounter ORA-02064 error, that is, because the main transaction has its own transaction control statement, but the called remote process also has its own transaction control statement, of course, an error will be reported. If we declare the called process as a self-made transaction, then OK.

When the insert statement was used in the function today, an error was reported that the ora-14551 could not perform the DML operation in the query.

There are two solutions to the error:

1. Set the stored procedure outside the function;

Ii. Use autonomous transaction)

Add this sentence to the function declaration section.

Pragma autonomous_transaction;

I chose the latter one.