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

Question

CREATE OR REPLACE FUNCTION XXCHR_UPDATE      (
                                                                P_SALESREP_ID          IN   NUMBER,
                                                                p_COLUMN_NAME           IN     VARCHAR2,
                                                                P_COLUMN_VALUE            IN   varchar2
                                                           )
   RETURN VARCHAR2

IS

   lv_dyn_sql                        VARCHAR2(2000):= NULL;

BEGIN


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

    EXECUTE IMMEDIATE lv_dyn_sql using P_SALESREP_ID;

    return(1);



EXCEPTION
      WHEN OTHERS
      THEN
         dbms_output.put_line('Returning Error : '||SQLERRM);
         --lv_return_val:= 0;
     RETURN SQLERRM;

END XXCHR_UPDATE;

When I execute this function its returning error

ORA-14551

please suggest here

[How do I ask a **good** question?](http://stackoverflow.com/help/how-to-ask) — marc_s, Jul 06 '16 at 05:24
What do you do with this function. `select XXCHR_UPDATE (a,b,c) from dual;` ? — Arkadiusz Łukasiewicz, Jul 06 '16 at 05:39
maybe you need add quotes in your dyn sql ? because when you output sql string, you can see what attribute87 = attr, but need attribute87 = 'attr' — Анатолий Предеин, Jul 06 '16 at 05:48
Already asked: http://stackoverflow.com/questions/8729236/solution-to-cannot-perform-a-dml-operation-inside-a-query btw you seem to be missing a space between `p_column_value` and your `where` clause. — William Robertson, Jul 06 '16 at 11:23

score 0 · Accepted Answer · answered Jul 06 '16 at 06:28

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;

score 0 · Answer 2 · answered Jul 06 '16 at 20:26

Why aren't all three parameters? Get rid of the string joins:

lv_dyn_sql := q'[UPDATE thirty_days
                    SET attribute86 = :arg1
                      , attribute87 = :arg2
                  WHERE SALESREP_ID = :arg3]';

execute immediate lv_dy_sql using p_column_name, p_column_value, p_salesrepid;

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

2 Answers2

Linked