动态查询中v_MONTH的无效标识符错误 [英] Invalid identifier error for v_MONTH in dynamic query

问题描述

我正在包装内执行以下过程，以将条目发布到表ledger_stat_dly中. 我已经编写了动态查询来替换案例语句，但遇到了以下错误. 您能否建议在过程中正确定义V_Month is invalid identifier错误时为什么会弹出错误. 在此先感谢您的帮助.

I'm running following procedure inside a package to post entries in table ledger_stat_dly. I've written dynamic query to replace case statements but I am facing following error. Could you please suggest why V_Month is invalid identifier error popping up while it is defined properly in procedure. Thanks in advance for help.

Error is:
ORA-00904: "V_MONTH": invalid identifier

    (
        V_IDENTITY_CODE      NUMBER,    
        V_CONSOLIDATION_CD   NUMBER,    
        V_FINANCIAL_ELEM_ID  NUMBER,    
        V_ORG_UNIT_ID        NUMBER,   
        V_GL_ACCOUNT_ID      NUMBER,    
        V_COMMON_COA_ID      NUMBER,   
        V_PRODUCT_1_ID       NUMBER,   
        V_PRODUCT_ID         NUMBER,   
        V_PRODUCT_3_ID       NUMBER,    
        V_DATE               DATE,        
        V_AMOUNT             NUMBER,    
        V_MEMO_GL_ACCOUNT_ID NUMBER DEFAULT 0,  
        V_POSTINGTYPE        CHAR DEFAULT 'N', 
        V_BALANCE_TYPE_CD    NUMBER DEFAULT 0 
)
IS

V_CNT NUMBER;
V_MONTH CHAR(2);
V_MO NUMBER;
V_YEAR_S NUMBER;

-- variables store result of dynamic cursor
V_SL   VARCHAR2(2500);
V_TARGET_COLUMN VARCHAR2(6 CHAR);

BEGIN              


   IF V_POSTINGTYPE = 'N' THEN

        IF NVL(V_AMOUNT,0) <> 0  THEN

                V_MO := (MONTH(V_DATE));
                V_MONTH := LPAD(V_MO,2,'0');
                V_YEAR_S := (YEAR(V_DATE));
                V_TARGET_COLUMN := CONCAT('DAY_',LPAD(TO_CHAR(DAY(V_DATE)),2,'0'));              

                    EXECUTE IMMEDIATE UTL_LMS.FORMAT_MESSAGE('UPDATE /*+ index(a LEDGER_STAT_DLY_IDX02_IN) */ LEDGER_STAT_DLY A
                           SET %s =  NVL(%s,0) + NVL(V_AMOUNT,0)
                           WHERE IDENTITY_CODE =  NVL(V_IDENTITY_CODE,0)
                              AND YEAR_S =  NVL(V_YEAR_S,0)
                              AND MONTH_NO = NVL(V_MONTH,0)',V_TARGET_COLUMN, V_TARGET_COLUMN);


        END IF; 

    END IF;  --CLOSURE FOR POSTING TYPE IF STATEMENT



END IN_LEDGER_STAT_DAILY;

推荐答案

因为您将SQL语句组成为字符串，所以PLSQL引擎不会替换变量名(它们只是文字字符串的一部分)，因此，SQL引擎看到字符串"V_MONTH"，但是该名称没有列，因此标识符无效.如果您使用动态SQL，则必须自己进行值替换.其他变量也一样.所以:

Because you composed the SQL statement as a string the PLSQL engine does NOT substituted for the variable name (their just part of a literal string), therefore the SQL engine sees the string 'V_MONTH' but there is no column by that name thus invalid identifier. If you stay with dynamic SQL you'll have to do value substitution yourself. The same also applies to the other variables. So:

  EXECUTE IMMEDIATE UTL_LMS.FORMAT_MESSAGE(
  'UPDATE /*+ index(a LEDGER_STAT_DLY_IDX02_IN) */ LEDGER_STAT_DLY A
      SET %s =  NVL(%s,0) + NVL(%s ,0)
    WHERE IDENTITY_CODE =  NVL(%s ,0)
      AND YEAR_S =  NVL(%s ,0)
      AND MONTH_NO = NVL(%s ,0)'
     ,V_TARGET_COLUMN, V_TARGET_COLUMN ,V_AMOUNT,V_IDENTITY_CODE,V_YEAR_S,V_MONTH);

您可能还需要进行任何必要的格式转换.

You may also need to do any necessary format conversions.

这篇关于动态查询中v_MONTH的无效标识符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！