Solved

SQL+ Buffer Overflow

Posted on 2004-08-24
6
1,499 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:jaramill
  • 2
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11894152
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
 
LVL 3

Author Comment

by:jaramill
ID: 11895087
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11899978
I was suggesting to create a job. How do you get the strings to pass to SP ?
0
 
LVL 3

Accepted Solution

by:
jaramill earned 0 total points
ID: 11906573
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question