In case of drop table statement you will face an exception in oracle, but in mysql there is a if exists in drop statement. so we can follow the following to avoid this in Oracle.
--CREATE TABLE MENNAN.TABLE_EXAMPLE AS SELECT * FROM DUAL;
---SIMPLE WAY, if execute immediate fails, because table not exists, transaction will be committed
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MENNAN.TABLE_EXAMPLE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE <> -942 THEN
RAISE;
END IF;
END;
/
---- NAMED EXCEPTION, the same as SIMPLE WAY
DECLARE
ve_TableNotExists EXCEPTION;
PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);
vs_DynamicDropTableSQL VARCHAR2(1024);
vs_TableName VARCHAR2(64);
BEGIN
vs_TableName := 'MENNAN.TABLE_EXAMPLE';
vs_DynamicDropTableSQL := 'DROP TABLE ' || vs_TableName;
EXECUTE IMMEDIATE vs_DynamicDropTableSQL;
EXCEPTION
WHEN ve_TableNotExists THEN
dbms_output.put_line(vs_TableName || ' not exist, skipping....');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RAISE;
END;
/
---- NAMED EXCEPTION, the same as SIMPLE WAY but more general one
DECLARE
ve_TableNotExists EXCEPTION;
PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);
PROCEDURE DropTable(pis_TableName IN VARCHAR2) IS
vs_DynamicDropTableSQL VARCHAR2(1024);
BEGIN
vs_DynamicDropTableSQL := 'DROP TABLE ' || pis_TableName;
EXECUTE IMMEDIATE vs_DynamicDropTableSQL;
EXCEPTION
WHEN ve_TableNotExists THEN
dbms_output.put_line(pis_TableName || ' not exist, skipping....');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RAISE;
END DropTable;
BEGIN
DropTable('MENNAN.TABLE_EXAMPLE');
END;
/
---Yet another way, first check from dictionary, if exists drop the table. Checking from dictionary may take time but avoids unnecessary transactions
DECLARE
vs_TableName VARCHAR2(64);
vn_Count PLS_INTEGER;
BEGIN
vs_TableName := 'MENNAN.TABLE_EXAMPLE';
SELECT COUNT(*)
INTO vn_Count
FROM ALL_TABLES
WHERE OWNER || '.' || TABLE_NAME = vs_TableName;
IF vn_Count > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || vs_TableName;
END IF;
END;
/
