Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SQL*Plus code in ksh script, in-line vs. separate file
Platform: Oracle 9.2 on Solaris 9
Trying to cobble together a housekeeping script to clean up after an app that doesn't know how to wash its own hands ...
Given this script:
#!/bin/ksh
sqlplus mvxjdta/mvxjdta <<EOF
set echo off feedback off heading off
spool x_drop_mvxjdta_temp.sql
select 'DROP TABLE ' || owner || '.' || table_name ||';'
from all_tables
where owner='MVXJDTA'
and table_name like 'T\_%'escape '\'
/
spool off
-- @x_drop_mvxjdta_temp.sql
exit;
EOF
I would expect the spool file (x_drop_mvxjdta_temp.sql ) to contain a
series of DROP TABLE commands. Instead, it gets the SELECT statement
that was to generate those commands:
$ cat x_drop_mvxjdta_temp.sql
SQL> select 'DROP TABLE ' || owner || '.' || table_name ||';'
2 from all_tables
3 where owner='MVXJDTA' 4 and table_name like 'T\_%'escape '\'5 /
If, instead of putting the SQL*Plus code in the ksh script I place it in a separate sql file and do this:
$ cat BounceMovex
#!/bin/ksh
sqlplus mvxjdta/mvxjdta @drop_tables.sql
everything works as advertised. I'm pretty sure what's messing up the first example is something in the way the shell is handling the in-line code, but am not deep enough in that area to understand it. Thanks.
Cohn's Law: The more time you spend in reporting on what you are doing, the less time you have to do anything. Stability is achieved when you spend all your time doing nothing but reporting on the nothing you are doing. Received on Fri Feb 11 2005 - 14:45:56 CST