table or view does not exist : ORA-00942

Question

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

Praveen Praveen · Accepted Answer · 2015-08-16T07:58:19

You have to use dynamic sql.

for example;

CREATE OR REPLACE Procedure DE_DUP_PRO1 (
    Dy_Table_Name in varchar2
) 
AS
    v_sql varchar2(1500);
    v_clm1 number; -- if clm1 datatype is number
    v_clm1 number; -- if clm2 datatype is number
begin
    v_sql := 'select clm1, clm2 from ' || Dy_Table_Name;
    open cur for v_sql;

    loop
        FETCH cur INTO v_clm1, v_clm2;
        EXIT WHEN cur%NOTFOUND;
        -- process v_clm1, v_clm2
    end loop; 
end DE_DUP_PRO1;
/

table or view does not exist : ORA-00942

1 Answers