The syntax that you are looking for is
docs.oracle.com/…/drop-table.html
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Wait, this does not work !
SQL> drop table if exists t;
drop table if exists t
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Okay. It was the Oracle MySQL book 😉
In the Oracle database, I have created my own droptableifexists script.
I went for a SQL*Plus no-plsql approach. PL/SQL is also possible but it generated different error messages (ORA-06512: at line 1) and different feedback (PL/SQL procedure successfully completed.)
So I check the dictionary, put a command to drop in the sqlplus buffer if a table exists, then run that command first.
droptableifexists.sql
set feed off ver off pages 0 newp none
def cmd="select 'OK: Table does not exist' from dual"
col cmd new_v cmd nopri
select 'drop table "'||table_name||'"' cmd
from user_tables
where table_name='&1';
1 select
del *
1 &cmd
set feedb 6 head off
/
set head on
del *
undef cmd
col cmd clear
Ok, let’s try
SQL> create table t(x number);
Table created.
SQL> @droptableifexists T
Table dropped.
SQL> @droptableifexists T
OK: Table does not exist
A PL/SQL approach could be
for f in (
select 'drop table "'||table_name||'"' cmd
from user_tables where table_name='T')
loop
execute immediate f.cmd;
end loop;
Try it :
SQL> create table t(x number);
Table created.
SQL> exec for f in (select 'drop table "'||table_name||'"' cmd from user_tables where table_name='T')loop execute immediate f.cmd;end loop
PL/SQL procedure successfully completed.
SQL> exec for f in (select 'drop table "'||table_name||'"' cmd from user_tables where table_name='T')loop execute immediate f.cmd;end loop
PL/SQL procedure successfully completed.
A bit easier to read. Same has to be done for USER, VIEW and so on.
PS: there are also other versions around catching for ORA-942, but ORA-942 may be ORA-943 in next release, try drop table t;; in 11g and 12c to see those things really happen !