Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: indirect SQL execution - kind of 'backquoting'

Re: indirect SQL execution - kind of 'backquoting'

From: <sybrandb_at_yahoo.com>
Date: 22 Jul 2004 06:49:39 -0700
Message-ID: <a1d154f4.0407220549.654e5a5a@posting.google.com>


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 Received on Thu Jul 22 2004 - 08:49:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US