Execute the next sql statement

Question

In my db, tables:

• test1--not exist
• test2--exist
• test3--exist

While running the below block, it's throwing a "table does not exist" error for test1. However, I want to run the below drop statement for test2 and test3 despite the error because, for my case "does not exist" is fine for me as i am going to create all the 3 tables in the next step.

set serveroutput ON;
BEGIN
    begin
    EXECUTE IMMEDIATE 'drop table test1';
    dbms_output.put_line('test1 dropeed successfully');
    EXECUTE IMMEDIATE 'drop table test2';
    dbms_output.put_line('test2 dropped successfully');
    EXECUTE IMMEDIATE 'drop table test3';
    dbms_output.put_line('test3 dropped successfully');
    exception
        when others then
            dbms_output.put_line('Error >> '|| SQLERRM ||' -->'|| dbms_utility.format_error_backtrace);
    end;
end;

expecting:- it should drop test2 and test3 as well , even if we got the table doesnot exist error for test1.

Answer 1

You need to capture the exception for each drop statement:

SET serveroutput ON SIZE 999999
CLEAR screen
DECLARE
  table_or_view_dne EXCEPTION;
  PRAGMA EXCEPTION_INIT (table_or_view_dne, -942);
BEGIN
   BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE test1';
   EXCEPTION WHEN table_or_view_dne THEN
     NULL;
   END;
   BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE test2';
   EXCEPTION WHEN table_or_view_dne THEN
     NULL;
   END;
   BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE test3';
   EXCEPTION WHEN table_or_view_dne THEN
     NULL;
   END;
  END;
/