0

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...

Cleptus
  • 3,446
  • 4
  • 28
  • 34
AS400Jockey
  • 21
  • 2
  • Don't use "when others then null" because there are other exceptions that could occur. Instead, catch the exception you are expecting: https://stackoverflow.com/a/1801453/103295 – Jeffrey Kemp Oct 12 '18 at 07:42

2 Answers2

1

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...

AS400Jockey
  • 21
  • 2
0

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;
/
Chris Allwein
  • 2,498
  • 1
  • 20
  • 24
  • Thanks for that. I tried it... and I tried with a ; after the first END, also. No joy... Error starting at line : 1 in command - begin execute immediate 'drop table X'; exception when others then null; end; begin execute immediate 'drop table Y'; exception when others then null; end; Error report - ORA-06550: line 5, column 1: PLS-00103: Encountered the symbol "BEGIN" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: – AS400Jockey Oct 12 '18 at 11:07