CODEWITHSUNDEEP
plsql
Swiss Jura
Swiss Jura

Reputation: 11

PL/SQL: ORA-00942: table or view does not exist

Am getting this error 'PL/SQL: ORA-00942: table or view does not exist' in Oracle 11G when I try to runt his portion of my script. It seems the select statement isn't parsing the name of the variable from the cursor. Need help to ensure this can read the table name variable from the cursor.

DECLARE
  ITEM_ERROR     NUMBER;
  CNT_SITE       VARCHAR2(46);

  CURSOR C_SITEID IS
     SELECT OBJECTNAME,ATTRIBUTENAME FROM MAXATTRIBUTE    WHERE ATTRIBUTENAME LIKE 'SITE%'   GROUP BY OBJECTNAME,ATTRIBUTENAME, COLUMNNAME;            
     SITEIDRec    C_SITEID%RowType;

 BEGIN
 BEGIN
 OPEN C_SITEID;
 LOOP
   FETCH C_SITEID into SITEIDRec;
   EXIT WHEN C_SITEID %NOTFOUND;
       BEGIN  
         SELECT COUNT(SITEID) into CNT_SITE FROM SITEIDRec.OBJECTNAME
          WHERE  SITEID  IN ('COLLEGE NANO SCI ENG-TGCM','FREESCALE-BALAZS','TGCM-GLOBAL FOUNDRIES','INTL RECTIFIER-TGM','TGCM-DMOS5','TGCM-IMFT','TGCM-TRIQUINT','GP-TRIQUINT');
       EXCEPTION
          WHEN NO_DATA_FOUND THEN NULL;
       END;
 END LOOP;
 --COMMIT;
 CLOSE C_SITEID;
 --COMMIT;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
 END;
 --COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
    MSG_TEXT := SUBSTR(SQLERRM,1,200);
  --COMMIT;
   raise;
END;

Upvotes: 1

Views: 4790

Answers (1)

Yogendra Singh
Yogendra Singh

Reputation: 34367

I think you have FROM TableName missing in your query and hence it's rightly complaining.

SELECT COUNT(SITEID) into SITEIDRec.OBJECTNAME
      WHERE SITEID IN 
             ('COLLEGE NANO SCI ENG-TGCM','FREESCALE-BALAZS',
               'TGCM-GLOBAL FOUNDRIES','INTL RECTIFIER-TGM','TGCM-DMOS5',
               'TGCM-IMFT','TGCM-TRIQUINT','GP-TRIQUINT');

Please correct your query by adding the From TableName.

EDIT: Try using EXECUTE IMMEDIATE as below

 EXECUTE IMMEDIATE 'SELECT COUNT(SITEID) into CNT_SITE FROM ' 
                    || SITEIDRec.OBJECTNAME ||
         ' WHERE SITEID IN (''COLLEGE NANO SCI ENG-TGCM'',''FREESCALE-BALAZS'',
               ''TGCM-GLOBAL FOUNDRIES'',''INTL RECTIFIER-TGM'',''TGCM-DMOS5'',
               ''TGCM-IMFT'',''TGCM-TRIQUINT'',''GP-TRIQUINT'')';

Upvotes: 4

Related Questions