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

Ed Stevens wrote:

> 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:
>

[...]
>
> 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.
>
> - Ed Stevens
>
> 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.

I suspect it has to do with the shell and sqlplus wrestling over STDOUT.

Here's what does work:

1. remove the two spool statements
2. add -S option to sqlplus command
3. re-direct the output of the shell script, for example test.sh > x_drop_mvxjdta_temp.sql

Also, it might be a good idea to put the EOF marker in quotes (on the sqlplus command line) to prevent the shell from trying to interpret characters like "\" in the "here document".

-Mark Bole Received on Fri Feb 11 2005 - 15:35:20 CST