Re: indirect SQL execution - kind of 'backquoting'

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.

I might be missing something, but perhaps you merely need to surround your select like so:

set head off
set pages 0
set verify off
set feed off
set echo off
spool xyz.sql
select ' drop table' || name || ';' from sys.systables where creator=&someone ;
spool off
@xyz
!rm xyz.sql

Or are you asking how to do it in a shell script with passed in username password parameters?

echo "
connect \$1/\$2
(above commands)
" | sqlplus

jg

--
@home.com is bogus.
SQL> @home.com
SP2-0310: unable to open file "home.com"
SQL>