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 ;
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.