PL/SQL block for dropping a table only if it exists

PL/SQL block for dropping a table only if it exists.

It safely ignores the error ORA-00942 (table or view does not exist).

Here is the clean version with proper formatting:

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

What this does

• EXECUTE IMMEDIATE → Executes dynamic SQL.
• DROP TABLE ... PURGE → Drops the table permanently (no recycle bin).
• If table does not exist (ORA-942) → it ignores the error.
• If any other error occurs → it raises the exception.

---

More Elegant Method (Recommended in Newer Oracle)

Instead of exception handling, you can check first:

BEGIN
   FOR t IN (
      SELECT table_name
      FROM user_tables
      WHERE table_name = 'CO_DATA_NZ_BKP'
   ) LOOP
      EXECUTE IMMEDIATE 'DROP TABLE CO_DATA_NZ_BKP PURGE';
   END LOOP;
END;
/

---

Here is a generic reusable PL/SQL procedure to safely drop any table in Oracle. You only pass the table name, and it will drop it only if it exists.

---

Generic Procedure to Drop Any Table Safely

CREATE OR REPLACE PROCEDURE drop_table_if_exists(p_table_name IN VARCHAR2)
IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM user_tables
    WHERE table_name = UPPER(p_table_name);

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name || ' PURGE';
        DBMS_OUTPUT.PUT_LINE('Table ' || p_table_name || ' dropped successfully.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Table ' || p_table_name || ' does not exist.');
    END IF;
END;
/

---

How to Use It

BEGIN
   drop_table_if_exists('CO_DATA_NZ_BKP');
END;
/

---

Advantages

✔ Reusable for any table
✔ No ORA-00942 error
✔ Cleaner than repeated exception blocks
✔ Useful in deployment scripts / ETL jobs / data refresh programs

---

Advanced Version (Even Better – Works for Any Schema)

CREATE OR REPLACE PROCEDURE drop_table_if_exists(
    p_owner      IN VARCHAR2,
    p_table_name IN VARCHAR2
)
IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM all_tables
    WHERE owner = UPPER(p_owner)
    AND table_name = UPPER(p_table_name);

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 
        'DROP TABLE '||p_owner||'.'||p_table_name||' PURGE';
    END IF;
END;
/

Usage:

BEGIN
   drop_table_if_exists('SCOTT','EMP_TEMP');
END;
/

---