SQL+ Buffer Overflow

I have cronjob that executes a shell script and in it, it calls sqlplus and a SQL script. Here's a log of the file

The SQL script takes in 5 parameters. The first one is a date, the next 3 are strings, and the last one is a CHAR(1).

The error I get is at the bottom. Is this a Unix buffer issue or a SQL+ buffer issue? If so, how can I overcome this hard-limit

Thanks,
Gio


sqlplus / @cont_prc_cnd_list.sql 08/24/2004 ARANESP ALTSITEGPO,CRI,DEPOT,GOV-C/C/S,GOV-FED,GPO,GPO-DSH,HHC,HMO-IPA,HMO-STAFF,HOSP,IHS,INTL,LTC,MASTER,MCO,MDR
P,NHP,PARTNERS,PBM,PHARMACY,PHS,PHYS,PHYS_GPO,PPM,PRISON,PURCHASE,RETAIL,STOCK-FEE,WDA,WSA A-HOSP,A-LTC,A-MCO,A-NEPH,A-NEPHPLAT,A-NEPHSTD,A-PHYS,A-PROMO,A-RET
AIL,A-WSA,ALTSITEGPO,CONTR-BID,E-A-NATL,E-CMMA,E-DEPOT,E-FSDC,E-FSDC-GPO,E-FSDC-NOI,E-FSDC-OI,E-FSDC/CAP,E-FSDC/CDC,E-FSDC/H/V,E-FSDC/HBD,E-FSDC/VPD,E-GPO,E-H
~DIAL,E-HOSP,E-IPA-HMO,E-NMC,E-PPA,E-REIMBURS,E-RETAIL,E-WSA-FSDC,E-WSA-INT,E-WSA-RETL,HMO,I-DIRECT,I-IHS,I-MCO,I-PRISON,I-REBATE,K-HOSP,M-LTC,MCR~MCO,MULT-BL
NKT,MULT-DSH,MULT-DSH-A,MULT-DSH-B,MULT-DSH-N,MULT-HOSP,MULT-POA,MULT-PPM,MULTI-DP,MULTI-GPO,MULTI-IHS,MULTI-LTC,MULTI-MCO,N-BLANKET,N-GPO,N-GPO-SPCL,N-HHC,N-
HHC-NOI,N-HHC-OI,N-HHC/PHY,N-HOSP,N-MCO,N-PHY~CORP,N-PHYS,N-PHYS~COR,N-PPM,N-PURCH,N-RETAIL,N-WSA-PHY,NL-RETAIL,NO~AGREEMT,PBM,PORTFOLIO,QUOTE~FORM,RDA,SPECIA
L,STOCK-FEE,SYSTEM~SUP,WDA , >>./alert_bpmPROD.log

SQL*Plus: Release 8.0.6.0.0 - Production on Tue Aug 24 13:20:00 2004

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production
With the Partitioning option
JServer Release 8.1.6.2.0 - Production

string beginning "A-HOSP,A-L..." is too long. maximum size is 239 characters.

Logged in as OPS$BPMPROD

By the way I'm using K-Shell in a SunOS harley 5.8 Generic_108528-22 sun4u sparc SUNW,Ultra-Enterprise box.

Thanks,
Gio
LVL 3
jaramillAsked:
Who is Participating?
 
jaramillConnect With a Mentor Author Commented:
Well we have a table that is read by a Pro C job and it looks at certain columns (i.e. file name, file type, parameters, priority).  The filename would be my SQL script, file type is PL/SQL, parameters would be the actual parameters I pass in, and the priority in the job queue.  This Pro C* job then knows to call SQL+ and execute said script with parameters if any.  At that point that's where it chokes.

But I have come up with a work-around since there really is no solution.  Instead of trying to pass the parameter from inside the oracle environment of forms out to unix than back into the server-side of oracle, I just call the procedure directly from within a forms-trigger within the FORM.  The only downside is, is that the user's form screen is temporarily locked while this procedure is executed whereas if I could submit it as a separate job, then another SQL+ session would be opened while the user can still work.  But this solution is the one that works perfectly without any limitations from a calling the procedure aspect.

I will close this question out.

Gio
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
This is a SQLPlus buffer limitation. A job executing a stored procedure will probably solve your problem.
Let me know if you need more details.
0
 
jaramillAuthor Commented:
hey ploubier:  Here's the code in the SQL script which is a stored procedure.

EXECUTE Cont_Price_Candidate_List(TO_DATE('&1', 'MM/DD/RRRR'), '&2', '&3', '&4', '&5');
EXIT

Are you saying I should wrap the SQL script call in a shell script?  Is there a buffer limitation in Unix?  Any more details you have would be appreciated.

Thanks,
Gio
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
I was suggesting to create a job. How do you get the strings to pass to SP ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.