To avoid the overhead of checking if the table exists twice or not” and to avoid the exception which is raised to the caller due to the DROP failure for some other reason. The most efficient way is to catch the exception “table not found”.
Here is an informative video for you on Exception Handling in SQL.
Code:
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
For other object types, you can refer to these blocks of code given below:
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2289 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' || viewName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER ' || triggerName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4080 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX ' || indexName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1418 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || Mytable
|| ' DROP COLUMN ' || columnName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -904 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblinkName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2024 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mviewName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TYPE ' || typeName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || Mytable
|| ' DROP CONSTRAINT ' || constraintName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2443 THEN
RAISE;
END IF;
END;
BEGIN
DBMS_SCHEDULER.drop_job(jobName);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -27475 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP USER ' || userName;
/* you can add CASCADE */
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1918 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP PACKAGE ' || packageName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedureName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION ' || functionName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
For Tablespace:
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespaceName;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -959 THEN
RAISE;
END IF;
END;