Oracle & 'Delete if exists'

Why is this such a pain in the ass to do without erroring your sript for one of the most advanced databases in the world?

Well it is. Life's a bitch sometimes.

Heres how to do it...

The best way I've found is to create a procedure and call this when needed. Because you can 'create or replace' a procedure its not so bad, at least you can create it from your script when needed and if its there who cares, it'll just get overwritten, without complaining.

Send this to your database... (take the numbers off!!!) ;)

1. create or replace procedure drop_table (tab_name in varchar2)
2. is
3. qry_string varchar2(4000);
4. cnt number;
5. begin
6. select count(1) into cnt from user_tables where table_name = tab_name;
7. if cnt > 0 then
8. qry_string := 'DROP TABLE ' tab_name;
9. begin
10. EXECUTE IMMEDIATE qry_string;
11. exception
12. when others then
13. dbms_output.put_line('Error occured while dropping ' sqlerrm);
14. end;
15. else
16. dbms_output.put_line('TABLE ' tab_name ' does not exist');
17. end if;
18. end;
19. };

explanation...

1. creates or replaces the procedure
2. start defining procedure
3. create a string to put result of quiery in
4. set variable cnt to number
5. start procedure
6. add 1 to cnt if your table name matches in the users tables
7. if cnt is greater than 0 do this...
8. drop your table
9. start execute
10. do it now
11. error trapping
12. if returns other value except success
13. print error message
14. end execute
15. otherwise
16. return table not existing
17. finish otherwise
18. finish script
19. close script

call the procedure from your script

in perl use

call drop_table('TABLENAME')

make sure you use caps for the table name. how you call will depend on your programming language.

To achieve the same thing with sequences replace all your table references with SEQUENCE, seq_name, sequence_name.

Have fun!!!

Yes, you do need all that for a simple 'drop if exists'... hopefully someone at Oracle decides to fix this...