Multiple "execute immediately" queries in Oracle

Question

I have this sequence of queries:

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

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

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

but when I try to execute it in SQL Scratchpad it says "encountered the symbol begin" which pointed me that all the queries must be in one begin... if I remove all the begin end exept for the first begin and last end it gives me "invalid SQL statement" how to perform multiple drop table or multiple create table with the upper pattern and to check if the tables exist? I know that my style with exception when others then null; is considered bad practice similar to empty catch()'es in other languages but thats the easiest way for me to check if a table exists/not exists in oracle

Answer 1

BEGIN
  EXECUTE IMMEDIATE 'drop table mytable1';

  EXECUTE IMMEDIATE 'drop table mytable2';

  EXECUTE IMMEDIATE 'drop table mytable3';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

Works fine.

---

If you ask me, exception when others then null - should be be avoided. If you want to check if a table exists - query USER_TABLES

DECLARE
  V_EXISTS NUMBER;

BEGIN

SELECT 1 INTO V_EXISTS FROM USER_TABLES WHERE TABLE_NAME = 'TEST';

IF V_EXISTS = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEST';
END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Table not found');
END;

---

Regd: your comment, if you still want to go about using the method in your question, wrap it in a outside anonymous block

BEGIN

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable1';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable2';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable3';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

END;

Result:

SQL> BEGIN
  2  
  3    BEGIN
  4      EXECUTE IMMEDIATE 'drop table mytable1';
  5    EXCEPTION
  6      WHEN OTHERS THEN
  7        NULL;
  8    END;
  9  
 10    BEGIN
 11      EXECUTE IMMEDIATE 'drop table mytable2';
 12    EXCEPTION
 13      WHEN OTHERS THEN
 14        NULL;
 15    END;
 16  
 17    BEGIN
 18      EXECUTE IMMEDIATE 'drop table mytable3';
 19    EXCEPTION
 20      WHEN OTHERS THEN
 21        NULL;
 22    END;
 23  
 24  END;
 25  
 26  /

PL/SQL procedure successfully completed.

SQL>