Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Solution to "cannot perform a DML operation inside a query"?

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this

create or replace procedure complex(datainput in VARCHAR2) is begin insert into dumtab values (datainput); end complex; 

I executed this in SQL Developer using the following statement

begin complex('SomeValue');   end; 

It worked fine, and the value was inserted into the table. However, the above statements are not supported in the Data Analysis tool, so I resorted to use a function instead. The following is the code of the function, it compiles.

create or replace function supercomplex(datainput in VARCHAR2) return varchar2 is begin insert into dumtab values (datainput); return 'done'; end supercomplex;    

Once again I tried executing it in SQL Developer, but I got cannot perform a DML operation inside a query upon executing the following code

select supercomplex('somevalue') from dual; 

My question is - I need a statement that can run the mentioned function in SQL Developer or - A function that can perform what I am looking for which can be executed by the select statement. - If it is not possible to do what I'm asking, I would like a reason so I can inform my manager as I am very new (like a week old?) to PL/SQL so I am not aware of the rules and syntaxes.

P.S. How I wish this was C++ or even Java :(

EDIT

I need to run the function on SQL Developer because before running it in DMine (which is the tool) in order to test if it is valid or not. Anything invalid in SQL is also invalid in DMine, but not the other way around.

Thanks for the help, I understood the situation and as to why it is illegal/not recommended

like image 661
Joshua1729 Avatar asked Jan 04 '12 15:01

Joshua1729


People also ask

Can not perform DML operation inside a query?

Cause: DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave. Action: Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

Which view does not allow DML operations?

simple view 2. complex view . In simple view We create view on single base table That's why we can perform all DML operations.it also called as Updatable view. But In case of Complex view We create view on multiple base tables that's why we cannot perform DML operations It is ReadOnly View (Only Select Operation).

Can we perform DML operation inside?

You can perform DML operations inside an Oracle PL/SQL function and, although this is generally not a good practice, call it from SQL.

Why we Cannot use DML in function?

Answers. Only READ-ONLY database access in functions. If DML operations would be allowed in functions, then functions would be pretty similar to stored procedures. The way it is, a stored procedure can use a function, but not vice versa.


1 Answers

You could use the directive pragma autonomous_transaction. This will run the function into an independant transaction that will be able to perform DML without raising the ORA-14551.

Be aware that since the autonomous transaction is independent, the results of the DML will be commited outside of the scope of the parent transaction. In most cases that would not be an acceptable workaround.

SQL> CREATE OR REPLACE FUNCTION supercomplex(datainput IN VARCHAR2)   2     RETURN VARCHAR2 IS   3     PRAGMA AUTONOMOUS_TRANSACTION;   4  BEGIN   5     INSERT INTO dumtab VALUES (datainput);   6     COMMIT;   7     RETURN 'done';   8  END supercomplex;   9  /  Function created  SQL> SELECT supercomplex('somevalue') FROM dual;  SUPERCOMPLEX('SOMEVALUE') -------------------------------------------------------------------------------- done  SQL> select * from dumtab;  A -------------------------------------------------------------------------------- somevalue 

Tom Kyte has a nice explanation about why the error is raised in the first place. It is not safe because it may depend upon the order in which the rows are processed. Furthermore, Oracle doesn't guarantee that the function will be executed at least once and at most once per row.

like image 134
Vincent Malgrat Avatar answered Sep 21 '22 04:09

Vincent Malgrat