Solution to "Cannot Perform a Dml Operation Inside a Query"

Solution to cannot perform a DML operation inside a query?

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.

Solving Cannot perform DML inside a query error in Oracle

A function is supposed to compute and return a result, not change the state of the database. If you want to do DML in a function (i.e. if you want to insert rows into tables), you cannot call that function in a SELECT statement since a SELECT statement cannot change the state of the database. In general, you'd be better off creating this sort of thing as a stored procedure rather than a stored function.

You can call this function from a PL/SQL block just as you would call a stored procedure

DECLARE
  l_success_code NUMBER;
BEGIN
  l_success_code := reg('awlad','01968688680','545466455','12345');
END;

If you want to create this as a procedure

CREATE OR REPLACE PROCEDURE reg( name in varchar2, 
                                 cellNo in varchar2, 
                                 voterId in varchar2, 
                                 pass in varchar2, 
                                 succ out NUMBER ) 
AS 
BEGIN 
  succ:=0; 
  insert into logInfo values(loginfo_seq.nextval,cellNo,pass,0); 
  insert into passInfo values(name,cellNo,voterId); 
  succ:=1; 
END;

then you'd need to call the procedure by passing in the OUT parameter

DECLARE
  l_success_code NUMBER;
BEGIN
  reg('awlad','01968688680','545466455','12345', l_success_code);
END;

PLSQL : ORA-14551: cannot perform a DML operation inside a query

That's what you've been told - you can't perform DML (in your case, UPDATE t_scb_temp_objects ...) within a function you're (somehow - you didn't show how) calling from a procedure. That might be - for example:

create or replace function f_test (par_deptno in number)
  return number 
is
  ...
begin
  open c_rules_details;
  loop
    -- your current code goes here
    execute immediate v_sql_stmnt;
  end loop;
  return 1;
end;

create or replace procedure p_test is
  l_ename emp.ename%type;
begin
  select e.ename 
    into l_ename
    from emp e
    where e.deptno = f_test(e.deptno);        --> this will cause the error
end;

---

So, what to do? Perform UPDATE from within a procedure, not a function.

If it must be a function, make it an autonomous transaction (using PRAGMA), but that's something I wouldn't recommend. You'd rather figure something different out. Here's how, though:

SQL> create table test (col number);

Table created.

SQL> insert into test (col) values (100);

1 row created.

SQL> commit;

Commit complete.

First, a function that'll fail (just like yours):

SQL> create or replace function f_test return number is
  2  begin
  3    update test set col = 10;
  4    commit;
  5    return 1;
  6  end;
  7  /

Function created.

SQL> select f_test from dual;
select f_test from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.F_TEST", line 3

Now, the one which will succeed:

SQL> create or replace function f_test return number is
  2    pragma autonomous_transaction;                        --> this
  3  begin
  4    update test set col = 10;
  5    commit;
  6    return 1;
  7  end;
  8  /

Function created.

SQL> select f_test from dual;

    F_TEST
----------
         1

SQL> select * from test;

       COL
----------
        10

SQL>

Once again: you probably don't want to do that.

cannot perform DML operation inside a query

You can use DML inside a PL/SQL function - no problem. However, the function can only be called from PL/SQL, not from SQL - i.e. it can be called like this:

declare
   l_message varchar2(30);
begin
   l_message := test_func('123');
end;

... but not like this:

select test_func(empno) from emp;

That leads to the error message you posted.

Many people (including me) don't like functions that have "side effects" like this, but that is a matter of best practice and standards, not a technical issue.

ORA-14551: cannot perform a DML operation inside a query

The meaning of the error is quite clear: if we call a function from a SELECT statement it cannot execute DML statements, that is INSERT, UPDATE or DELETE, or indeed DDL statements come to that.

Now, the snippet of code you have posted contains a call to PIPE ROW, so plainly you are calling this as SELECT * FROM TABLE(). But it includes DELETE and INSERT statements so clearly it falls foul of the purity levels required for functions in SELECT statements.

So, you need to remove those DML statements. You are using them to populate a global temporary table, but this is good news. You haven't include any code which actually uses the GTT so it is difficult to be sure, but using GTTs is often unnecessary. With more details we can suggest workarounds.

Is this related to this other question of yours? If so, did you follow my advice to check that answer I had given to a similar question?

---

For the sake of completeness, it is possible to include DML and DDL statements in a function called in a SELECT statement. The workaround is to use the AUTONOMOUS_TRANSACTION pragma. This is rarely a good idea, and certainly wouldn't help in this scenario. Because the transaction is autonomous the changes it makes are invisible to the calling transaction. Meaning in this case that the function cannot see the outcome of the deletion or insertion in the GTT.

Oracle : cannot perform a DML operation inside a query error

Oracle works very hard to ensure that select queries are read-only. If you want to create a query that modifies data, you have to work exceptionally hard to bypass these safeguards. And be willing to put up with some pretty hacky solutions.

If you are going to do this, you absolutely must determine what MakeDeepLinkEx actually does and make sure that letting Oracle call this function with whatever parameters the optimizer thinks appropriate (including rows that are filtered out in the where clause) won't cause any problems to your system. You're asking Oracle to bypass some very important guardrails so it's up to you to make sure that the road is safe.

You can create a new function that uses an autonomous transaction

create or replace function MakeDeepLinkExAut( p_user_id integer,
                                              p_ad_id   integer )
  return varchar2
as
  l_retvar varchar2(500);
  pragma autonomous_transaction;
begin
  l_retvar := pbweb.adposter.MakeDeepLinkEx(p_user_id, p_ad_id);
  commit;
  return l_retvar;
end;

and then call this new function in your select statement.

ORA-14551: cannot perform a DML operation inside a query error while using Execute immediate

try to modify your statement:

lv_dyn_sql := 'UPDATE thirty_days
                SET attribute86 ='||p_COLUMN_NAME||',attribute87 ='||P_COLUMN_VALUE
                || 'WHERE SALESREP_ID = :1';

to

lv_dyn_sql := 'UPDATE thirty_days
                SET attribute86 ='||p_COLUMN_NAME||',attribute87 ='||P_COLUMN_VALUE
                || 'WHERE SALESREP_ID ='||P_SALESREP_ID;  

and of course:

EXECUTE IMMEDIATE lv_dyn_sql;

---

Related Topics