ORA-00942: table or view does not exist
Oracle can't find the table or view, or you have no privilege on it. Usually a missing schema prefix, a typo, quoted mixed-case names or a missing grant.
- Error code
ORA-00942- Category
- Names and objects
What it means
Oracle resolves a table name in the current schema first, then through private and public synonyms. If nothing matches, or the object exists but you have no privilege on it, the statement fails with ORA-00942. Oracle deliberately reports a table you can't see the same way as one that doesn't exist.
In Oracle 23ai and later the message names the object: table or view "HR"."EMPLOYEES" does not exist.
Common causes
- The table is in another schema and you didn't qualify it, for example
employeesinstead ofhr.employees. - The table was created with a quoted mixed-case name such as
"Employees". Unquoted names are stored in upper case, soemployeeslooks forEMPLOYEESand doesn't find it. - You have no
SELECT(orINSERT,UPDATE,DELETE) privilege on the object, or the privilege came through a role, which doesn't apply inside definer-rights PL/SQL. - A synonym points to a table that was dropped or renamed.
- You are connected to a different database, PDB or schema than you think.
How to fix it
-
Check which schema you're in and find the object you can see.
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual; SELECT owner, object_name, object_type FROM all_objects WHERE UPPER(object_name) = 'EMPLOYEES'; -
Qualify the name with its owner, or switch the default schema for the session.
SELECT * FROM hr.employees; ALTER SESSION SET CURRENT_SCHEMA = hr;
-
If the object exists but you can't see it, ask its owner (or a DBA) for a direct grant. Grant directly, not through a role, if a stored procedure needs it.
GRANT SELECT ON hr.employees TO app_user;
-
If the name was created in mixed case, quote it exactly as created.
SELECT * FROM "Employees";
-
If a synonym is involved, check where it points.
SELECT owner, synonym_name, table_owner, table_name FROM all_synonyms WHERE synonym_name = 'EMPLOYEES';
Questions
Why do I get ORA-00942 inside a procedure when the query works in SQL*Plus?
Privileges granted through a role are not used inside definer-rights PL/SQL. Grant the privilege directly to the procedure's owner, or define the procedure with AUTHID CURRENT_USER.
Related errors
ORA-00904invalid identifierORA-01031insufficient privilegesORA-00955name is already used by an existing object208SQL Server: Invalid object name42P01PostgreSQL: relation does not exist1146MySQL: Table doesn't exist
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.