Search This Blog

Drop Table If Exists in Oracle


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 VARCHAR2IS
    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;
/

No comments:

Post a Comment

comments are welcome