I'm trying to create Procedure where I'm passing dynamic table but I'm not able to achieve my goal and getting "PL/SQL: ORA-00942: table or view does not exist".Please let me know if doing wrong somewhere
CREATE OR REPLACE Procedure DE_DUP_PRO1 (Dy_Table_Name in varchar2)
--RETURN NUMBER
AS
v_hol varchar2(300);
CURSOR De_DUB_CUR IS
SELECT S.TRANS_GUID AS OLD_TRANS_GUID,
H.TRANS_GUID AS NEW_TRANS_GUID,
CASE
WHEN H.TRANS_GUID IS NULL
THEN 0
ELSE 1
END as TRN_STAT,
P.INTR_PHARMACY_ID as INT_PHARMACY_ID ,S.EXTRNL_PHARMACY_ID as EXT_PHARMACY_ID ,S.PHARMACY_NM as PHARMACY_NAME ,S.PHARMACY_ADDR as PHARMACY_ADDRESS,
S.SUPPLIERS_PSCR_DRUG_CD as SP_PSCR_DRUG_CD, S.PSCR_DRUG_IPU_CD as PS_DRUG_IPU_CD,'IPU' as IPU_Value, S.PSCR_DRUG_DESC as PS_DRUG_DESC,
S.DSPNSD_DRUG_PACK_SIZE as DS_DRUG_PACK_SIZE, S.RX_ID as R_ID, S.RX_ITEM_SEQ as R_ITEM_SEQ, S.RX_REPEAT_STATUS as R_REPEAT_STATUS, S.RX_TYP as R_TYPE,
S.EXMT_STATUS as EX_STATUS,S.PSCR_QTY as PS_QTY, S.NRSG_HM_IND as NR_HM_IND,S.RX_DSPNSD_DT as R_DSPNSD_DT, S.RX_DSPNSD_TM as R_DSPNSD_TM,
S.SUPPLIERS_DSPNSD_DRUG_CD as SP_DSPNSD_DRUG_CD, S.DSPNSD_DRUG_IPU_CD as DS_DRUG_IPU_CD, 'IPU' as IPU_Value2,S.DSPNSD_DRUG_DESC as DS_DRUG_DESC,
S.GENERIC_USE_MARKER as GC_USE_MARKER, S.DSPNSD_UNIT_OF_QTY as DS_UNIT_OF_QTY, S.DSPNSD_QTY as DS_QTY, 'EUR' as EUR_Value1,S.COST_OF_DSPNSD_QTY as CT_OF_DSPNSD_QTY ,
S.VERBOSE_DOSAGE as VER_DOSAGE
FROM (SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM Dy_Table_Name stg ) s
-- From( SELECT stg.TRANS_GUID,stg.EXTRNL_PHARMACY_ID,stg.PHARMACY_NM,stg.PHARMACY_ADDR,stg.SUPPLIERS_PSCR_DRUG_CD,stg.PSCR_DRUG_IPU_CD,stg.PSCR_DRUG_DESC,stg.DSPNSD_DRUG_PACK_SIZE,
-- stg.RX_ID,stg.RX_ITEM_SEQ,stg.RX_REPEAT_STATUS,stg.RX_TYP,stg.EXMT_STATUS,stg.PSCR_QTY,stg.NRSG_HM_IND,stg.RX_DSPNSD_DT,stg.RX_DSPNSD_TM,stg.SUPPLIERS_DSPNSD_DRUG_CD,
-- stg.DSPNSD_DRUG_IPU_CD,stg.DSPNSD_DRUG_DESC,stg.GENERIC_USE_MARKER,stg.DSPNSD_UNIT_OF_QTY,stg.DSPNSD_QTY,stg.COST_OF_DSPNSD_QTY,stg.VERBOSE_DOSAGE, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM Dy_File_Name stg ) s
LEFT JOIN ps_pharmacy p ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id LEFT JOIN ps_rx_hist H ON h.key_clmns_hash = s.key_clmnS_hash
AND h.rx_dspnsd_dt = s.rx_dspnsd_dt AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD AND s.detl_clmns_hash <> h.detl_clmns_hash WHERE S.RN = 1;
BEGIN
FOR De_Dub_rec IN De_DUB_CUR
LOOP
DBMS_OUTPUT.PUT_LINE ( De_Dub_rec.OLD_TRANS_GUID || '|' || De_Dub_rec.NEW_TRANS_GUID || '|' || De_Dub_rec.TRN_STAT || '|' || De_Dub_rec.INT_PHARMACY_ID || '|' ||
De_Dub_rec.EXT_PHARMACY_ID || '|' || De_Dub_rec.PHARMACY_NAME|| '|' || De_Dub_rec.PHARMACY_ADDRESS || '|' || De_Dub_rec.SP_PSCR_DRUG_CD || '|' ||
De_Dub_rec.PS_DRUG_IPU_CD || '|' || De_Dub_rec.IPU_Value || '|' || De_Dub_rec.PS_DRUG_DESC || '|' || De_Dub_rec.DS_DRUG_PACK_SIZE || '|' || De_Dub_rec.R_ID || '|' ||
De_Dub_rec.R_ITEM_SEQ || '|' || De_Dub_rec.R_REPEAT_STATUS || '|' || De_Dub_rec.R_TYPE || '|' || De_Dub_rec.EX_STATUS || '|' || De_Dub_rec.PS_QTY || '|' || De_Dub_rec.NR_HM_IND || '|' || De_Dub_rec.R_DSPNSD_DT
|| '|' || De_Dub_rec.R_DSPNSD_TM || '|' || De_Dub_rec.SP_DSPNSD_DRUG_CD || '|' || De_Dub_rec.DS_DRUG_IPU_CD|| '|' || De_Dub_rec.IPU_Value2 || '|' ||
De_Dub_rec.DS_DRUG_DESC|| '|' || De_Dub_rec.GC_USE_MARKER|| '|' || De_Dub_rec.DS_UNIT_OF_QTY|| '|' || De_Dub_rec.DS_QTY|| '|' || De_Dub_rec.EUR_Value1|| '|' ||
De_Dub_rec.CT_OF_DSPNSD_QTY|| '|' || De_Dub_rec.VER_DOSAGE);
END LOOP;
-- RETURN 0;
END DE_DUP_PRO1;
/
After executing I'm getting below
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/7 PL/SQL: SQL Statement ignored
21/106 PL/SQL: ORA-00942: table or view does not exist
36/6 PL/SQL: Statement ignored
36/29 PLS-00364: loop index variable 'DE_DUB_REC' use is invalid