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

PL/SQL "ORA-14551: cannot perform DML operations in queries" solved the environment Oracle 11.2.0 + SQL Plus problem by writing a function according to the following requirements. in the emp table, the total number of employees whose salaries are lower than the average wage plus 200 is returned. An updated operation in PL/SQL, executing this function reports the following error: ORA-16551: unable to perform DML operations in queries. Add PRAGMA AUTONOMOUS_TRANSACTION when declaring the function, and COMMIT after DML execution. Operation Log

[SQL] -- 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 to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Compile function 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. -- Call function, error SQL> SELECT raise_sal () FROM DUAL; SELECT raise_sal () FROM DUAL * row 1st error: ORA-14551: unable to execute DML operation in query 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 -- verify Step 2: query the total number of employees with lower salaries than average 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 ---------- 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 has selected 12 rows. -- Verification Step 4: Call the function. If the value is 8, implement the function SQL> SELECT raise_sal () FROM dual; RAISE_SAL () --------- 8 -- verify Step 5: query table data again SQL> SELECT ename, sal FROM emp; ename sal ---------- 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 has selected 12 rows.

 

Reference http://www.bkjia.com/database/201204/127424.html reference text-a better understanding of autonomous transactions database transactions is a unit operation, either all operations are successful or all 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.