Unix shell script drops chars sent to sqlplus - here document, etc.
Date: 3 Mar 2003 12:16:00 -0800
Message-ID: <969841ec.0303031216.67e204fc_at_posting.google.com>
[Quoted] 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