>PROBLEM
Oracle doesn't have "if exists" but it may be replaced by a procedure.
>SOLUTION
1. Run the following procedure on the target database.
create or replace procedure drop_table
(tablename in varchar2)
IS
dropcmd varchar(300);
table_exists NUMBER;
drop_ignored varchar(100);
dropped_msg varchar(100);
tabledropped varchar(100);
BEGIN
dropcmd := 'DROP TABLE ' || tablename;
drop_ignored := 'table ' || tablename || ' not found - dropping skipped';
dropped_msg := 'dropped table ' || tablename;
select COUNT(*) into table_exists from user_tables where table_name = upper(tablename);
IF table_exists > 0 THEN
-- DBMS_OUTPUT.PUT_LINE('dropping ' || tablename);
EXECUTE IMMEDIATE dropcmd;
commit;
select table_name into tabledropped from user_tables where table_name = upper(tablename);
ELSE
DBMS_OUTPUT.PUT_LINE(drop_ignored);
END IF;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE(dropped_msg);
when others then
DBMS_OUTPUT.PUT_LINE(dropped_msg);
END;
/
show errors;
2. To test the procedure, create a table:
CREATE TABLE TESTONE.BIN017_BIDS (
BID_ID NUMBER(19) NOT NULL,
BID_DATE TIMESTAMP(6) ,
BID_PRICE NUMBER(19,4) ,
BIDDER_ID VARCHAR2(255) ,
ITEM_ID NUMBER(19) ,
CONSTRAINT SYS_C0023242 PRIMARY KEY ( BID_ID )
);
3. Run the procedure to test it:
exec drop_table('BIN017_BIDS')
>ENV
Oracle 12g
pssql
sqlplus
