PL/SQL ORA-14551: cannot perform DML operations in queries solved

Environment

Oracle 11.2.0 + SQL Plus

Problem

Write a function as required: add 200 of the employees whose salaries are lower than the average wage in the scott. emp table, and return the total number of employees who have changed their salaries. An updated operation in PL/SQL, executing this function reports the following error: ORA-16551: unable to perform DML operations in queries.

Solution

Add PRAGMA AUTONOMOUS_TRANSACTION when declaring the function, and COMMIT after executing DML.

Operation Log

-- Log on to Oracle
C: \ Users \ Wentasy> sqlplus wgb

SQL * Plus: Release 11.2.0.1.0 Production on Saturday June 29 15:32:21 2013

Copyright (c) 1982,201 0, Oracle. All rights reserved.

Enter the password:

Connect:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Compile Functions
SQL> CREATE OR REPLACE FUNCTION raise_sal
2 RETURN NUMBER
3 IS
4 v_num NUMBER: = 0;
5 v_avg emp. sal % TYPE;
6 BEGIN
7 select avg (sal) INTO v_avg FROM emp;
8 UPDATE emp SET sal = sal + 200 WHERE sal <v_avg;
9 v_num: = SQL % ROWCOUNT;
10 RETURN v_num;
11 END raise_sal;
12/

The function has been created.

-- An error occurred while calling the function.
SQL> SELECT raise_sal () FROM DUAL;
SELECT raise_sal () FROM DUAL
*
Row 3 has an error:
ORA-14551: unable to perform DML operations in queries
ORA-06512: In "WGB. RAISE_SAL", line 8

-- Add PRAGMA AUTONOMOUS_TRANSACTION and COMMIT.
SQL> CREATE OR REPLACE FUNCTION raise_sal
2 RETURN NUMBER
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 v_num NUMBER: = 0;
6 v_avg emp. sal % TYPE;
7 BEGIN
8 select avg (sal) INTO v_avg FROM emp;
9 UPDATE emp SET sal = sal + 200 WHERE sal <v_avg;
10 v_num: = SQL % ROWCOUNT;
11 COMMIT;
12 RETURN v_num;
13 END raise_sal;
14/

The function has been created.

-- Verification Step 1: query the average salary
SQL> SELECT AVG (sal) FROM emp;

AVG (SAL)
----------
2543.75

-- Verification Step 2: query the total number of employees with lower salaries than average salaries
SQL> SELECT count (sal) FROM emp WHERE sal <(SELECT AVG (sal) FROM emp );

COUNT (SAL)
----------
8

-- Verification Step 3: query data
SQL> SELECT ename, sal FROM emp;

ENAME SAL
--------------------
The SMITH 1600
ALLEN 2400
WARD 2050
JONES 2975
MARTIN 2050
BLAKE 2850
CLARK 2450
KING 5000
TURNER 2300
JAMES 1750
FORD 3000

ENAME SAL
--------------------
MILLER 2100

12 rows have been selected.

-- Verification Step 4: Call a function. If the value is 8, the function is implemented.
SQL> SELECT raise_sal () FROM dual;

RAISE_SAL ()
-----------
8

-- Verification Step 5: Query table data again
SQL> SELECT ename, sal FROM emp;

ENAME SAL
--------------------
The SMITH 1800
ALLEN 2600
WARD 2250
JONES 2975
MARTIN 2250
BLAKE 2850
CLARK 2650
KING 5000
TURNER 2500
JAMES 1950
FORD 3000

ENAME SAL
--------------------
MILLER 2300

12 rows have been selected.

References

ORA-14551: unable to perform DML operations in queries

Quote text-better understanding of autonomous transactions

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 use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of the anonymous block or in the stored procedure, function, data packet, or trigger definition section. SQL Server statements executed in such a module or process are autonomous. The trigger cannot contain the COMMIT statement, unless the PRAGMA AUTONOMOUS_TRANSACTION flag exists. However, only the statements in the trigger can be committed, but not the primary transaction.