How to Drop a table if exists using execute immediate in a PL/SQL Block

 

Safe Drop Table in PL/SQL

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE MAIN_SUB_LOC_NZ_BKP PURGE';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE = -942 THEN
         NULL; -- table does not exist, ignore
      ELSE
         RAISE;
      END IF;
END;
/

Explanation
EXECUTE IMMEDIATE → Required for DDL inside PL/SQL
-942 → ORA-00942 (table does not exist)
PURGE → Skips recycle bin

Better Version (Checking USER_TABLES First)
Cleaner and more controlled:
DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*)
   INTO v_count
   FROM user_tables
   WHERE table_name = 'MAIN_SUB_LOC_NZ_BKP';

   IF v_count > 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE MAIN_SUB_LOC_NZ_BKP PURGE';
   END IF;
END;
/