// Oracle error reference

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
Error message
table or view does not exist

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 employees instead of hr.employees.
  • The table was created with a quoted mixed-case name such as "Employees". Unquoted names are stored in upper case, so employees looks for EMPLOYEES and doesn't find it.
  • You have no SELECT (or INSERT, 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

  1. 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';
  2. Qualify the name with its owner, or switch the default schema for the session.

    SELECT * FROM hr.employees;
    ALTER SESSION SET CURRENT_SCHEMA = hr;
  3. 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;
  4. If the name was created in mixed case, quote it exactly as created.

    SELECT * FROM "Employees";
  5. 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

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

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.