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 -> SQL*Plus code in ksh script, in-line vs. separate file

SQL*Plus code in ksh script, in-line vs. separate file

From: Ed Stevens <nospam_at_noway.nohow>
Date: Fri, 11 Feb 2005 14:45:56 -0600
Message-ID: <7r5q015t6r01jdfjmte4bj707dj72vjegk@4ax.com>


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 /
SQL> spool off

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

Original text of this message

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