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;And commit after DML execution.

 

Operation Log

-- Log on to oraclec: \ Users \ wentasy> sqlplus wgbsql * 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-productionwith the partitioning, OLAP, data Mining and real application testing options -- Write 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.

References

Http://blog.csdn.net/gigiouter/article/details/7616627

 

 

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 Pragma in PL/SQL at the highest level of the anonymous block or in the stored procedure, function, data packet, or trigger definition section.
Autonomous_transaction statement. 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.

My mailbox: wgbno27@163.com Sina Weibo: @ wentasy27 public platform: justoracle (No.: justoracle) database technology exchange group: 336882565 (when adding group verification from csdn XXX) Oracle Exchange discussion group: https://groups.google.com/d/forum/justoracleBy Larry Wen

	@ Wentasy blog is for your reference only. Welcome to visit. I hope to criticize and correct any mistakes. If you need to repost the original blog post, please indicate the source. Thank you for the [csdn blog]