Solved

SQL+ Buffer Overflow

Posted on 2004-08-24
6
1,514 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 77
Queries 15 46
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
format dd/mm/yyyy parameter 16 30
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.  â€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

839 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