Wednesday, January 7, 2009

PL/SQL : Scripting for table drop

I came from MS SQLServer and I miss a lot of nice functionalities, like for instance, If exists.
Why am I talking about this? Simple, I want to run a script that "only" drops a few tables that I'don't no if they already exists.

Easy to say, hard to find. So here's a script for doing that, without returning any errors.

set echo off;
set heading off;
spool run.sql;
select 'drop table 'table_name';' from dba_tables where UPPER(table_name) in ('TABLEA', 'TABLEB', 'TABLEC');
spool off;
@run.sql;

Enjoy.

No comments:

All Rights Reserved