ORA-14551:使用立即执行时无法在查询错误内执行DML操作 [英] ORA-14551: cannot perform a DML operation inside a query error while using Execute immediate

问题描述

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

ORA-14551

请在此处建议

推荐答案

尝试修改您的陈述:

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

到

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

当然:

EXECUTE IMMEDIATE lv_dyn_sql;

这篇关于ORA-14551:使用立即执行时无法在查询错误内执行DML操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！