Re: indirect SQL execution - kind of 'backquoting'

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 22 Jul 2004 17:56:47 +0200
Message-ID: <cdoo40$b44$1@news.BelWue.DE>

sybrandb_at_yahoo.com wrote:

> Christoph Kukulies <kuku_at_accms33.physik.rwth-aachen.de> wrote in message news:<2m9dhrFkl5v6U1_at_uni-berlin.de>...
> 

>>I must admit, I'm not very familar with SQL. I was trying to empty
>>a database instance from all tables that were created by a
>>specific creator using
>>
>>select ' drop table' || name || ';' from sys.systables
>>where creator='SOMEONE' ;
>>
>>The output of that command, when executed, produces the desired result.
>>
>>Is there a way to "backquote" this commans, i.e. like in a Unix shell,
>>as you are doing something like rm `cat file` and file contains a
>>list of files.
>>
>>By now I'm redirecting this to another file and grep through it
>>to fish out the drop commands.
>>
>>I'm sure there are more elegant methods.

> 
> 
> begin
> for i in (select table_name from dba_tables where owner = 'SOMEONE' ) loop
> execute immediate 'drop table '||i.table_name';
> end loop;
> end;
> /
> 
> works like a charm, you'll only need to open those manuals for that.
> 
> Sybrand Bakker
> Senior Oracle DBA

Sorry Sybrand, but that won't work since when querying DBA_TABLES you're most likely *not* 'SOMEONE', so you'll have to preceede the table name with the owner. Or set the current schema with alter session.

Besides that, the OP is not clear if this exercise is because there is actually something in the schema that he want's to keep. If not, why not just drop user cascade?

Cheers,

Holger Received on Thu Jul 22 2004 - 10:56:47 CDT