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

Question

I went through some of the already answered questions, mentioned below, but could not find a solution that worked for me.

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

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

I have written this within a function, that I am calling from a procedure.

OPEN c_rules_details;
            LOOP
                FETCH c_rules_details INTO rule_conditions_record;
                EXIT WHEN c_rules_details %notfound;                
                dbms_output.put_line('Range and rule is  '|| rule_conditions_record.rdr_tmplt_id || rule_conditions_record.rdr_rng_from ||rule_conditions_record.rdr_rng_to); 
                SELECT COUNT(*) INTO v_balance_records FROM t_scb_temp_objects WHERE RULE_CONDITION=rule_conditions_record.rdr_tmplt_id AND CHARGE is NULL;
                dbms_output.put_line('Number of records in rule are  '|| v_balance_records); 
                IF v_balance_records>rule_conditions_record.rdr_rng_from AND v_balance_records<rule_conditions_record.rdr_rng_to THEN
                    v_ssql_stmnt:='UPDATE t_scb_temp_objects
                    SET CHARGE='||rule_conditions_record.rfr_chrg_amt||' WHERE RULE_CONDITION='||rule_conditions_record.rdr_tmplt_id;

                    EXECUTE IMMEDIATE v_ssql_stmnt;
                END IF;
            END LOOP;
        CLOSE   c_rules_details;

Why not simply turn your function into a procedure instead? – Kaushik Nayak Mar 12 '19 at 06:19 — Kaushik Nayak, Mar 12 '19 at 06:19

score 1 · Accepted Answer · answered Mar 12 '19 at 06:15

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.

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

1 Answers1