Unix shell script drops chars sent to sqlplus - here document, etc.

Does anyone know why this would happen?

Chars are sometimes dropped, resulting in an Oracle error.

This Ksh script runs on a Sun, SunOS 5.7, in the background. It connects to a local Oracle 8i (8.1.7) server.

This fails in one out of about 8 sqlplus calls, not always the same one (4 are in the script.)

"Pipe to" flavor:

   print "
   ... <sql statements>
   ...
   " | sqlplus

"Here document" flavor:

   sqlplus <<EOF
   ... <sql statements>
   ...
   EOF I wonder why it fails with obviously garbled input, e.g. I wonder about the order-of-operations Korn Shell uses ... And - most of all - I wonder about i/o settings on this box, for this login, buffering ...

Err msg:
  ERROR at line 12:

         LTRIM(ovtc.carrierpa, ' ') ASr.rrname   ORA-00923: FROM keyword not found where expected

This is because chars were - apparently - dropped from approximately positions 275-495 in the group of strings sent to sqlplus.

I.e., dropped text apparently began in the middle of line 12, ended in midst of line 19.

This runs in the background, >logfile 2>&1

I know that tcl and expect are preferred tools for this, but I've used ksh so many times that I'm inclined to believe it can be fixed.

In advance, thanks for any insights,
Chris Barr

Relevant code - one of 4 calls - is below.

• - - - - - - - - - - - - - - - - - - - - - - - - - - - spoolFile=$spoolDir/drillProdTemp.lst print "$L_USER_NAME/$pw CREATE TABLE drill_prod_temp AS SELECT prod.surr, LTRIM(RTRIM(prod.prodcd)) AS prodcd, LTRIM(RTRIM(prod.typeId)) AS typeId, prod.stdt, prod.eddt, xsr.rrname, xsr.riidnm, xsr.amt, xsr.frdt, LTRIM(ovtc.carrierpa, ' ') AS carrierpa, ovtc.ampa, LTRIM(ovtc.carrierre, ' ') AS carrierre, ovtc.amre FROM denorm_prod_t prod, denorm_ovtc_t ovtc, (SELECT xsr.prsurr, LTRIM(xsr.RRNAME) AS rrname, LTRIM(xsr.RIIDNM) AS riidnm, xsr.AMT, to_date(dorm.FRDT,'yyyy-mm-dd') AS frdt FROM denorm_sr sr, denorm_dorm dorm WHERE sr.frdt <= dorm.frdt AND sr.todt >= dorm.frdt AND sr.riidnm = 'HTH' AND sr.amtidnm = 'E' AND sr.ogsw = 'Y' AND dorm.name1 = 'PASSDATE' AND dorm.todt = 19010101 ) xsr WHERE prod.todt = 19010101 AND ovtc.todt = 19010101 AND prod.surr = ovtc.PRSURR(+) AND prod.surr = sr.prsurr(+); COMMIT; CREATE INDEX drill_prod_temp_idx ON drill_prod_temp(surr) TABLESPACE martin_index; ANALYZE TABLE drill_prod_temp ESTIMATE STATISTICS; SELECT 'count drill_prod_temp: '||COUNT(1)||', '|| to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') AS \"Count\" FROM drill_prod_temp; QUIT
  " |sqlplus >$spoolFile 2>&1