Home » SQL & PL/SQL » SQL & PL/SQL » Error on buffer size (oracle 8i)
icon9.gif  Error on buffer size [message #282169] Wed, 21 November 2007 01:11 Go to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Hello experts.

Can you please help on this.

create or replace procedure week_swap_app(cuid in varchar2) is
stuffdate date;
custname varchar2(28);
expno varchar2(8);
custpono varchar2(20);
pino varchar2(8);
itmdes varchar2(57);
itcode varchar2(8);
uom varchar2(2);
shipqty varchar2(13);
netwt varchar2(15);
contsize varchar2(8);
contno varchar2(20);
portn1 varchar2(30);
portn2 varchar2(30);
finaldes varchar2(20);
cuname varchar2(28);
cino varchar2(8);
bolno varchar2(24);
boldt date;
vesselname varchar2(30);
voageno varchar2(20);
etd date;
eta date;
rowCount number := 0;
cursor swdata is select g.stuffing_date,
d.customer_name,
e.pk_export_order_no,
e.customer_order_no,
upper(b.pk_pi_no),
UPPER(trim(c.item_description1)) ||' '|| UPPER(trim(c.item_Description2)),
upper(c.PK_ITEM_CODE),
UPPER(a.fk_uom_id),
round(a.actual_shipping_quantity,3),
round((a.actual_shipping_quantity * c.net_wt)/1000,3),
g.container_size,
UPPER(a.pk_container_no),
upper(h.port_name),
upper(k.port_name),
UPPER(b.FINAL_DESTINATION),
UPPER(r.country_name),
upper(q.pk_ci_no),
upper(p.pk_bol_no),
p.bol_date,
p.vessel_name,
p.voyage_no,
p.departure_date,
p.expected_arrival_date
from export_order e,
export_order_dtls f,
container_item_ref a,
proforma_invoice b,
item_mstr c,
customer_mstr d,
pi_container_ref g,
port_mstr h,
port_mstr k,
bol_pi_ref o,
bill_of_lading p,
commercial_invoice q,
country_mstr r
where e.pk_export_order_no=f.pk_export_order_no
and e.FK_FINAL_DESTINATION_COUNTRY_I=r.pk_country_id
and f.pk_export_order_no=a.pk_export_order_no
and f.pk_line_no=a.pk_line_no
and f.fk_item_code=c.pk_item_code
and a.pk_pi_no=g.pk_pi_no
and a.pk_container_no=g.pk_container_no
and a.pk_pi_no=b.pk_pi_no
and b.fk_loading_port_id=h.pk_port_id
and b.fk_discharge_port_id=k.pk_port_id
and b.pk_pi_no=o.pk_pi_no
and o.pk_bol_no=p.pk_bol_no
and p.pk_bol_no=q.pk_bol_no
and b.fk_customer_id=d.pk_customer_id
and d.pk_customer_id=e.fk_customer_id
and e.pk_business_code=f.pk_business_code
and e.pk_business_code=r.pk_business_code
and f.pk_business_code=c.pk_business_code
and f.pk_business_code=a.pk_business_code
and a.pk_business_code=g.pk_business_code
and a.pk_business_code=b.pk_business_code
and b.pk_business_code=o.pk_business_code
and o.pk_business_code=p.pk_business_code
and p.pk_business_code=q.pk_business_code
and g.pk_business_code=h.pk_business_code
and b.pk_business_code=k.pk_business_code
and q.pk_business_code='hpc'
and q.ci_status='a'
and p.bol_date between '01-NOV-07' and '04-NOV-07'
and e.fk_customer_id='xuiugf01';
begin
if swdata%isopen then
close swdata;
end if;
OPEN swdata;
DBMS_OUTPUT.ENABLE(100000000);
loop
fetch swdata into stuffdate,custname,expno,custpono,pino,itmdes,itcode,uom,shipqty,netwt,contsize,contno,portn1,portn2,finaldes,cuname,cino,bolno,boldt ,
vesselname,voageno,etd,eta;
exit when swdata%notfound;
DBMS_OUTPUT.PUT('"'||stuffdate||'","'||custname||'","'||expno||'","'||
custpono||'","'||pino||'","'||itmdes||'","'||itcode||'","'||' '||'","'||uom||'","'||
shipqty||'","'||netwt||'","'||contsize||'","'||contno||'","'||
portn1||'","'||portn2||'","'||finaldes||'","'||cuname||'","'||cino||'","'||bolno||'","'||boldt||'",,');
DBMS_OUTPUT.NEW_LINE();
rowCount := rowCount + 1;
END LOOP;
close swdata;
end;

This procedure is throwing below error.

ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 99
ORA-06512: at "COSMOSAPP.WEEK_SWAP_APP", line 106
ORA-06512: at line 1


Can Anybody please help me.
Re: Error on buffer size [message #282177 is a reply to message #282169] Wed, 21 November 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't want to read and follow the guide.
You will not have my help.

Regards
Michel
Re: Error on buffer size [message #282187 is a reply to message #282177] Wed, 21 November 2007 02:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You cannot have more than 255 characters per line in DBMS_OUTPUT.

Bust it up into more statements.

Ross Leishman
Re: Error on buffer size [message #282350 is a reply to message #282169] Wed, 21 November 2007 11:30 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
There are two separate limits on dbms_output. One is the total size and the other is the size of each line, which is limited to 255 characters. You are hitting the 255 character per line limit. For a handy method for getting around these limitations, see the following by Tom Kyte:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:146412348066
Previous Topic: Output to Excel in 2 different Cell
Next Topic: UPDATE...WHERE CURRENT OF with variable table name
Goto Forum:
  


Current Time: Fri Dec 02 21:02:25 CST 2016

Total time taken to generate the page: 0.11049 seconds