Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: arathorn101@hotpop.com (CBarr)
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.tools,comp.databases.oracle.server
Subject: Unix shell script drops chars sent to sqlplus - here document, etc.
Date: 3 Mar 2003 12:16:00 -0800
Organization: http://groups.google.com/
Lines: 98
Message-ID: <969841ec.0303031216.67e204fc@posting.google.com>
NNTP-Posting-Host: 158.171.31.18
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1046722561 19761 127.0.0.1 (3 Mar 2003 20:16:01 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 3 Mar 2003 20:16:01 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:94302 comp.databases.oracle.tools:57253 comp.databases.oracle.server:178299
X-Received-Date: Mon, 03 Mar 2003 13:15:54 MST (news.easynews.com)

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