How to debug the error of invalid identifier in the following PL/SQL block?-CodePudding

For the below PLSQL code , I am getting Error report - ORA-00904: "SRC"."PART_ID_CONSOLIDATED": invalid identifier ORA-06512: at line 37 , I tried debugging it by printing the values of I.item_no , I.PART_ID_CONSOLIDATED , getting values correctly printed but still its showing invalid identifier , unable to debug this , please guide .

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 ;

CodePudding user response：

The short answer is that because you are selecting from dual, which only has a single dummy column, you need to give aliases to all of the values you are selecting in your using clause:

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<>fiddle with a very simplified example. To some extent that also addresses the "how to debug" part of your question - break your failing block of code down into smaller and simpler parts to make it easier to see what's happening. And if you still can't work it out, you're much closer to a minimum reproducible example you can post without so much noise for others to wade through.

The long answer is to avoid the loops and do a single merge, as MTO suggested in a comment.