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

/

WIP_DISCRETE_JOBS.STATUS_TYPE values in Oracle WIP ( work in process)

The following lookup table can be query to find the status_type of a discrete job in oracle applications discrete manufacturing work in process.

SELECT *
FROM apps.fnd_lookup_values
WHERE lookup_type LIKE ‘%WIP_JOB_STATUS%’

SELECT status_type FROM wip_discrete_jobs WHERE wip_entity_id = 1743670;

 

Value Meaning
7 Cancelled
8 Pending Bill Load
9 Failed Bill Load
10 Pending Routing Load
11 Failed Routing Load
12 Closed
13 Pending – Mass Loaded
14 Pending Close
15 Failed Close
1 Unreleased
3 Released
4 Complete
5 Complete – No Charges
6 On Hold