CODEWITHSUNDEEP
sqloracle-databaseplsqlexception
TomB
TomB

Reputation: 275

Exception When Others Then Not Catching ORA Error

My 'Exception when others then' block isn't catching an error when trying to update a table that doesn't exist. Am I missing something?

BEGIN

UPDATE made_up_table
SET made_up_column = 1;

exception 
WHEN OTHERS THEN 
    dbms_output.put_line('123');
end;

Upvotes: 0

Views: 1578

Answers (1)

Aleksej
Aleksej

Reputation: 22969

Trying to use a non existing table, you get a compile-time error, not a run-time error. For example, here you have no exception Handling:

SQL> BEGIN
  2
  3  UPDATE made_up_table
  4  SET made_up_column = null;
  5
  6  exception
  7  WHEN OTHERS THEN
  8      dbms_output.put_line('123');
  9  end;
 10  /
UPDATE made_up_table
       *
ERROR at line 3:
ORA-06550: line 3, column 8:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored

If you create the table with a not null constraint

SQL> create table made_up_table(made_up_column number not null);

Table created.

SQL> insert into made_up_table values (0);

1 row created.

and then you try to do an update that violates the constraint, you have an exception raised, and your error message shown:

SQL> BEGIN
  2
  3  UPDATE made_up_table
  4  SET made_up_column = null;
  5
  6  exception
  7  WHEN OTHERS THEN
  8      dbms_output.put_line('123');
  9  end;
 10  /
123

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions