Re: sqlplus and here document

From: EdStevens <quetico_man_at_yahoo.com>
Date: 24 Feb 2006 10:22:29 -0800
Message-ID: <1140805349.794296.311270@p10g2000cwp.googlegroups.com>

Robert Klemme wrote:
> EdStevens wrote:
> > 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.

>

> Trailing white space in line 40? To be on the safe side I'd not reuse the
> same terminator sequnce, so better use EOF1 and EOF2.

>

> HTH

>
> robert

BINGO! There was, indeed, a single trailing space. Thanks for the assist. Received on Fri Feb 24 2006 - 12:22:29 CST