?
Solved

SQL+ Buffer Overflow

Posted on 2004-08-24
6
Medium Priority
?
1,553 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

801 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