26 March 2013

Oracle - Drop Table If Exists

Oracle does not have a drop table statement to drop a table if exists, like some other RDBMSes does. If you do not want get error messages for a non-existent table drop operation, you can follow workarounds as I showed below. It may be useful, during setup processes, especially you don't want to generate error message to the user,  if the table that you are dropping does not exist.



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





1 comment:

Terek said...

thank you, it was very useful