sqlplus and here document

From: EdStevens <quetico_man_at_yahoo.com>
Date: 24 Feb 2006 08:31:30 -0800
Message-ID: <1140798690.060300.178960@i39g2000cwa.googlegroups.com>

Platform: Oracle 9.2.0 on Solaris 9

This is probably as much a shell script question as Oracle, but it involves SQL*Plus with a here document, and since there is a lot of unix expertise here ...

Writing a ksh script, placing sqlplus with a here document inside a for loop. I appears that the EOF is not being recognized, as commands that follow it are not executed and thing just fall to the bottom of the loop. Code looks like this:

<snip some preliminaries>

    17 set -x
    18 IMPFILE=/local/home/oracle/scripts/edstevens.dmp     19 echo `date +'%D %T %Z'` "- target database is $ORACLE_SID"     20 echo `date +'%D %T %Z'` "- source data is $IMPFILE" <snip some comment lines>

    27 for SCHEMA in EDSTEVENS EDSTEVENS2 ; do     28 #--- begin for block ---
    29 echo `date +'%D %T %Z'` "- clearing schema $SCHEMA"     30 sqlplus -s /nolog <<EOF
    31 connect / as sysdba
    32 spool _xoit.sql
    33 set pagesize 0 trimspool on head off verify off feedback off     34 select 'drop table ' || owner || '.' || table_name || ' cascade constraints;'

    35 from dba_tables
    36 where owner = '${SCHEMA}';
    37 select 'exit' from dual;
    38 spool off
    39 exit
    40 EOF
    41 #
    42 echo `date +'%D %T %Z'` "- finished sqlplus here doc for $SCHEMA"
    43 echo `date +'%D %T %Z'` "- sqlplus to run genned script for $SCHEMA"
    44 sqlplus "/ as sysdba" @_xoit.sql     45 echo `date +'%D %T %Z'` "- importing schema $SCHEMA"     46 imp system/$ORAPSWD \

    47     parfile=imp_batch.txt \
    48     FILE=$IMPFILE  \
    49     FROMUSER=$SCHEMA \
    50     TOUSER=$SCHEMA \
    51     LOG=imp_$SCHEMA.log

    52 echo `date +'%D %T %Z'` "- gathering stats for $SCHEMA"     53 sqlplus -s /nolog <<EOF
    54 connect / as sysdba
    55 begin
    56 dbms_stats.delete_schema_stats(ownname=>'${SCHEMA}');     57 dbms_stats.gather_schema_stats('${SCHEMA}',33,cascade => TRUE);
    58 end;
    59 /
    60 exit
    61 EOF
    62 #--- end for block ---
    63 done
    64 #
    65 echo `date +'%D %T %Z'` "- End $0"     66 exit 0

It does go thru the loop twice (for each value at line 27), but appears that nothing between lines 41 and 62 is executing. When I redirect stdout to a log file, this is what it shows. Note that we get the 'echo' msgs from line 29, but not from 42, 43, etc. Then we get the final exit msg from line 65

$>:cat eds2.log

02/24/06 08:44:52 MST - Begin eds2.sh
02/24/06 08:44:52 MST - target database is CMDM1240
02/24/06 08:44:52 MST - source data is

/local/home/oracle/scrips/edstevens.dmp
02/24/06 08:44:52 MST - clearing schema EDSTEVENS Connected.
drop table EDSTEVENS.EDS_TEST cascade constraints; exit
02/24/06 08:44:52 MST - clearing schema EDSTEVENS2 Connected.
drop table EDSTEVENS2.EDS_TEST cascade constraints; exit
02/24/06 08:44:52 MST - End eds2.sh

One last bit .... if I replace the here document with a reference to an external .sql script, as in

sqlplus "/ as sysdba" @gen_script.sql

Everything works as expected. I could go that way, but would like to learn why this isn't working as I expected. Received on Fri Feb 24 2006 - 10:31:30 CST