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

CBarr wrote:

> 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

Don't have time to analyze this but ... what is: AS \"Count\"

Looks like a potential nightmare to me ... especially with Count being a reserved word. And I have no idea what you think you are doing with the slashes and quotation marks.

And what is a commit doing here? This is DDL not DML.

Unless you have LMTs you should be specifying pctfree, pctused, pctincrease, and other parameters for both your table and your index.

Daniel Morgan Received on Mon Mar 03 2003 - 19:46:21 CST