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 -> Re: sqlplus and here document

Re: sqlplus and here document

From: Robert Klemme <bob.news_at_gmx.net>
Date: Fri, 24 Feb 2006 17:55:39 +0100
Message-ID: <468s4cF9qgvgU1@individual.net>


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 Received on Fri Feb 24 2006 - 10:55:39 CST

Original text of this message

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