Error(20,28): PL/SQL: ORA-00942: table or view does not exist

Question

I have ORACLE PROCEDURE, where I need to use dynamic table name in select query based on parameter "a_ID" passed. But I am getting error in creating procedure.

Error(20,28): PL/SQL: ORA-00942: table or view does not exist

Can anybody help resolving this? Below is the proc....

CREATE OR REPLACE 
PROCEDURE  IsTrue
(
  AppID IN VARCHAR2,
  a_ID IN VARCHAR2,
  l_ID VARCHAR2, 
  return_value out CHAR
)
AS

v_sql VARCHAR(3000);
v_tablename VARCHAR(30);

BEGIN

SELECT decode(a_ID,'LS','LSAPP','MR','MRAPP','RV','RVAPP','APP') INTO v_tablename FROM dual;

  SELECT CASE
    WHEN EXISTS (SELECT 1 
                      FROM v_tablename
                      WHERE APPID = AppID
                         AND LID <> l_ID) 
                THEN 'Y'
                ELSE 'N'
    END AS RECORD_EXISTS
    FROM DUAL;

EXECUTE IMMEDIATE v_sql INTO return_value;

END;

You cannot use variables, such as v_tablename in place of SQL identifiers. See if this helps. — mustaccio

Jeet Jeet · Accepted Answer · 2015-06-29T20:57:00

CREATE OR REPLACE PROCEDURE IsTrue (argAppID IN VARCHAR2, a_ID IN VARCHAR2, l_ID VARCHAR2, return_value out CHAR ) 
AS
v_sql VARCHAR(3000); 
v_tablename VARCHAR(30);
BEGIN
SELECT decode(a_ID,'LS','LSAPP','MR','MRAPP','RV','RVAPP','APP') INTO v_tablename FROM dual;
v_sql := 'SELECT CASE WHEN EXISTS (SELECT 1 FROM ' || v_tablename || ' WHERE APPID = '''|| argAppID ||''' AND LID <> '''|| l_ID || ''') THEN ''Y'' ELSE ''N'' END AS RECORD_EXISTS FROM DUAL';
EXECUTE IMMEDIATE v_sql INTO return_value;
END;

In your procedure the second line created as dynamic sql and stored in v_sql. This may help your procedure to execute as you need.

Error(20,28): PL/SQL: ORA-00942: table or view does not exist

1 Answers