Oracle 23ai now has a long-requested fix to the ORA-942 "table or view does not exist" error message. The error message would not print out *which* table or view was missing -- this was particularly troublesome with very long SQL statements and multiple tables and views being referenced.
A demo of the fixed behaviour in 23ai (followed later by the same code in 19c 19.22)
SQL> select banner from v$version;
BANNER
---------------------------------------------------------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
SQL>
SQL> -- create the two tables
SQL> drop table employees;
drop table employees
*
ERROR at line 1:
ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL> drop table departments;
Table dropped.
SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30));
Table created.
SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12));
Table created.
SQL>
SQL> -- insert data
SQL> insert into departments values (1,'Human Resources');
1 row created.
SQL> insert into employees values (1,'Somebody',1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> -- test query
SQL> select d.dept_id, d.dept_name, e.emp_name
2 from departments d, employees e
3 where e.dept_id = d.dept_id
4 order by d.dept_id,e.emp_name
5 /
DEPT_ID DEPT_NAME EMP_NAME
---------- ------------------------------ ------------------------------------------------------
1 Human Resources Somebody
SQL>
SQL> -- drop a table
SQL> drop table employees;
Table dropped.
SQL>
SQL> -- RETEST query
SQL> select d.dept_id, d.dept_name, e.emp_name
2 from departments d, employees e
3 where e.dept_id = d.dept_id
4 order by d.dept_id,e.emp_name
5 /
from departments d, employees e
*
ERROR at line 2:
ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL>
So, now the error message text for ORA-942 includes the name of the missing <schema>.<table_or_view>
Here is the same code in 19c (19.22)
SQL> --select banner from v$version;
SQL> -- use BANNER_FULL in 19c to get RU level -- 19.22
SQL> select banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL>
SQL> -- create the two tables
SQL> drop table employees;
drop table employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table departments;
Table dropped.
SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30));
Table created.
SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12));
Table created.
SQL>
SQL> -- insert data
SQL> insert into departments values (1,'Human Resources');
1 row created.
SQL> insert into employees values (1,'Somebody',1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> -- test query
SQL> select d.dept_id, d.dept_name, e.emp_name
2 from departments d, employees e
3 where e.dept_id = d.dept_id
4 order by d.dept_id,e.emp_name
5 /
DEPT_ID DEPT_NAME EMP_NAME
---------- ------------------------------ ------------------------------------------------------
1 Human Resources Somebody
SQL>
SQL> -- drop a table
SQL> drop table employees;
Table dropped.
SQL>
SQL> -- RETEST query
SQL> select d.dept_id, d.dept_name, e.emp_name
2 from departments d, employees e
3 where e.dept_id = d.dept_id
4 order by d.dept_id,e.emp_name
5 /
from departments d, employees e
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL>
So, upto 19c, ORA-942 would simply say "table or view does not exist". In my example using sqlplus and a very short from list, the "*" does indicate where the error is -- but with other tools / clients and long FROM or sub-queries with FROM clauses, it may well be difficult to identify the missing table or view unless you trace the execution or run a test query against each table or view.