Second 'exception when others' fails

0
votes

I am trying to use EXCEPTION WHEN OTHERS to catch non-existent tables that I try to DROP, as follows:

begin
execute immediate 'drop table X';
exception when others then null;
end;

If table x exists and I run this, the table is dropped and all is well. If I run it again, there is no table to be dropped, but the EXCEPTION thing results in the script proceeding happily. So far, so good.

The problem appears if I try to do this more than once.

If I run this script to drop tables X and Y:

begin
execute immediate 'drop table X';
exception when others then null;

execute immediate 'drop table Y';
exception when others then null;
end;

I get the following error information:

Error starting at line : 1 in command -

begin
execute immediate 'drop table X';
exception when others then null;

execute immediate 'drop table Y';
exception when others then null;
end;

Error report - ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

( begin case declare end exit for goto if loop mod null pragma raise return select update when while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge ORA-06550: line 7, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

end not pragma final instantiable order overriding static member constructor map 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

What am I missing here? If I removed the second EXCEPTION WHEN statement, the script fails if table Y doesn't exist... I need to catch this error...

2
sqloracleexception-handling
Don't use "when others then null" because there are other exceptions that could occur. Instead, catch the exception you are expecting: stackoverflow.com/a/1801453/103295 - Jeffrey Kemp

2 Answers

0
votes

I found my answer here:

Two PLSQL statements with begin and end, run fine separately but not together?

Apparently I need

begin
    begin
        some stuff
    end;
    begin
        some other stuff
    end;
end;

or to put a / after each of the two inner block END; 's...

0
votes

exception when others then null; applies to a single Begin/End block. It's not really valid to have multiples in a single block. You can get around this by having multiple blocks that are individually caught.

begin
execute immediate 'drop table X';
exception when others then null;
end;
/

begin
execute immediate 'drop table Y';
exception when others then null;
end;
/