如何除錯以下PL/SQL塊中識別符號無效的錯誤？

對于以下 PLSQL 代碼，我收到錯誤報告 - ORA-00904: "SRC"."PART_ID_CONSOLIDATED": invalid identifier ORA-06512: 在第 37 行，我嘗試通過列印 I.item_no 和 I.PART_ID_CONSOLIDATED 的值來除錯它，得到正確列印的值，但仍然顯示無效的識別符號，無法除錯，請指導。

DECLARE
BEGIN
FOR T IN 
(
 SELECT * FROM TDTEMP.ITEM_MTRL_SPLIT_TMP
    where regexp_like(MATERIAL_NAME, '[[:digit:]],[[:digit:]] %  [[:alpha:]]*')
 ) 
 
  LOOP
   FOR I IN 
   
   (
   WITH parsed as(
    SELECT /*  parallel(t,8) materialize */
    T.item_no,T.item_type,T.bu_code_sup,T.bu_type_sup,T.FROM_PACK_DATE,T.PART_ID,T.MATERIAL_NAME,T.PART_ID_CONSOLIDATED,T.reporting_name,
    regexp_substr(REGEXP_REPLACE(replace(replace(T.MATERIAL_NAME,'%  ','% '),', ',','), '(\d ),(\d )', '\1.\2'),'[^,] ',1,ROWNUM) 
    AS split_value
      FROM dual
 CONNECT BY level <= regexp_count(REGEXP_REPLACE(replace(T.MATERIAL_NAME,'% /','%/'), '(\d ),(\d )', '\1.\2'),'[^,] ')
)
,in_pairs as(
select /*  parallel(k,8) materialize */
item_no,item_type,bu_code_sup,bu_type_sup,FROM_PACK_DATE,PART_ID,material_name,PART_ID_CONSOLIDATED,reporting_name
     ,regexp_substr(split_value, '[0-9] [.]*[0-9] ') as percentage
      ,trim(substr(split_value, instr(split_value, '%')   1)) as component
  from parsed k where split_value LIKE '%\%%' ESCAPE '\'
)
select /*  parallel(it,8) */
distinct item_no,item_type,bu_code_sup,bu_type_sup,FROM_PACK_DATE,PART_ID,material_name,percentage,component,PART_ID_CONSOLIDATED,reporting_name
from in_pairs it 
)

 LOOP 
 
 
    merge into TDTEMP.ITEM_MTRL_SPLIT_TMP targ 
    using (
    SELECT I.item_no,I.item_type,I.bu_code_sup,I.bu_type_sup,I.FROM_PACK_DATE,I.PART_ID,I.material_name,I.percentage,I.component,I.PART_ID_CONSOLIDATED,
    I.reporting_name FROM DUAL
    )src
    on (         targ.item_no                   = src.item_no
              and  targ.item_type               = src.item_type
              and  targ.bu_code_sup             = src.bu_code_sup
              and  targ.bu_type_sup             = src.bu_type_sup
              and  targ.part_id                 = src.part_id
              and  targ.from_pack_date          = src.from_pack_date
              and  targ.component               = src.component
              and targ.percentage               = src.percentage
              and targ.material_name            = src.material_name
              and targ.PART_ID_CONSOLIDATED     = src.PART_ID_CONSOLIDATED
              )
     when not matched then
      insert (item_no   ,           
             item_type  ,       
             bu_code_sup,           
             bu_type_sup    ,       
             from_pack_date ,   
             part_id            ,   
             part_id_consolidated,
             material_name  ,   
             percentage ,       
             component  ,
             reporting_name,
             plastic, 
             ii_date    )
values( src.item_no ,       
        src.item_type   ,   
        src.bu_code_sup ,   
        src.bu_type_sup ,   
        src.from_pack_date, 
        src.part_id     ,   
        src.part_id_consolidated ,  
        src.material_name   ,   
        src.percentage  ,   
        src.component ,
        src.reporting_name,
        'N',
        sysdate 
        )
when  matched then
update set  targ.percentage                 =   src.percentage  ,
            targ.component                  =   src.component   ;
         
            
            END LOOP ;
            END LOOP ;
            
     END ;       

         
         

uj5u.com熱心網友回復：

簡短的回答是，因為您選擇 from dual，它只有一個虛擬列，您需要為您在using子句中選擇的所有值提供別名：

using (
  SELECT I.item_no AS item_no, I.item_type AS item_type, I.bu_code_sup AS bu_code_sup,
    I.bu_type_sup AS bu_type_sub, I.FROM_PACK_DATE AS from_pack_date,
    I.PART_ID AS part_id, I.material_name AS material_name, I.percentage AS percentage,
    I.component AS component, I.PART_ID_CONSOLIDATED AS part_id_consolidated,
    I.reporting_name AS reporting_name
  FROM DUAL
) src

db<> 擺弄一個非常簡化的例子。在某種程度上，這也解決了您問題的“如何除錯”部分 - 將失敗的代碼塊分解為更小更簡單的部分，以便更容易看到正在發生的事情。而且，如果您仍然無法解決，那么您就更接近于可以發布的最小可復制示例，而無需太多噪音讓其他人涉足。

正如 MTO 在評論中建議的那樣，長答案是避免回圈并進行一次合并。

標籤：sql plsql oracle11g

上一篇：PostgreSQL-我想用日期查詢每個客戶的最新訂閱狀態

下一篇：按名稱查找多級類別的id