Why doesn't Oracle tell you which table or view does not exist?

sql

error-handling

debugging

sql-queries

byNikita Barsukov·Jan 28, 2025

Oracle can be tricked into revealing the name of the elusive missing table or view by nestling your query within a PL/SQL block outfitted with an exception handler. The SQLERRM function then becomes the hero of the hour, capturing the complete error message, including the name of the MIA object.

Here’s your handy piece of code:

DECLARE
  mind_your_own_business NUMBER;  -- Pseudo-paparazzi for missing_table
BEGIN
  EXECUTE IMMEDIATE 'SELECT 1 FROM am_i_here' INTO mind_your_own_business;  -- paparazzi on lookout for missing_table
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Look! We Found: ' || SUBSTR(SQLERRM, 14, INSTR(SQLERRM, ' ') - 14));  -- Capturing the sneaky missing_table
END;

Run this mini-blockbuster script, and you've got the name of the elusive table or view. Mystery solved!

Peek into Oracle's detective kit

To take your detective work a notch higher, ignite the EVENT="942 trace name errorstack level 12" parameter. This magic spell unveils a trace file that steps you through the query execution, leading you to the culprit.

In dynamic SQL, it's like sorting through a shape-shifter parade -- the forms change during runtime, making it hard to pin down the identity. Here, trace files act as your behind-the-scenes access to the parade.

Similarly, in complex join operations, identifying the missing performer can be daunting. Oracle's SQL*Plus commands or your favourite third-party apps like TOAD or TORA can prove handy.

In simpler scenarios, a SQL query can quickly cross-reference the suspect table against the ALL_TABLES witness list. Chase with caution, as potential compatibility issues could be lurking in the shadows due to Oracle's longstanding implementation choices.

Handling dynamic SQL: The ghost tables

Dynamic SQL** could be like dealing with wilful spirits -- they inhabit your code but evaporate during runtime. Combat these paranormal activities with DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, revealing the breadcrumbs to the trigger point of the error.

More tools for your detective toolkit

Time to equip yourself with some advanced tools:

Enable object access auditing: A CCTV of sorts monitoring object access.
SQL execution plan : Oracle's roadmap uncovers details as it tries to deliver your query.
Regular expressions: Analyze complex queries, extract table names, compare, and contrast with your database objects.

Design decisions and error messages

In the mystifying world of error message design, Oracle's decision to stick to static text, even in dynamic SQL, promotes stability over verbosity. When every error message becomes a potential bug, adding extras like object names to messages seems like an unnecessary risk.

explain-codes / Sql / Why doesn't Oracle tell you which table or view does not exist?

Linked

Get list of all tables in Oracle?



How do I find records that are not joined?



When is a good situation to use a full outer join?



Show all triggers in a MySQL database



Proper way of checking if row exists in table in PL/SQL block

