Solved

SQL+ Buffer Overflow

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query 15 63
T-SQL Convert to PL/SQL 23 61
null value 15 65
VB.Net - CSV to Oracle table 4 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now