How to catch all exceptions in PL/SQL script?

Question

Currently I'm writing an update script with error handling. A part of this script contains a call to a table that might not exists. So I'd like to catch the exception. However, when running the script in SQL Developer, it does not seem to catch the exception.

Script (simplified):

BEGIN
    SELECT VersionNumber
    FROM DbVersion;
  EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(SQLCODE);
END;

SQL Developer Script Output:

Error starting at line 2 in command:
BEGIN
    SELECT VersionNumber
    FROM DbVersion;
  EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(SQLCODE);
END;
Error report:
ORA-06550: line 3, column 10:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Why does it not simply display the error code? Thanks!

Answer 1

If You are using non-existing table - that's syntax error - plsql block is invalid, and cannot be run. Exception handling works when the block is running.

If You need to use table that may not exists, use can use dynamic sql (execute immediate 'select ... from non_existing_table') to avoid compile errors.

Or check for table existance in all_tables view.