Solved

DBMS_OUTPUT.PUT_LINE limit

Posted on 2002-03-19
9
9,657 Views
Last Modified: 2008-03-03
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 "SYS.DBMS_OUTPUT", line 65
ORA-06512: at "SCOTT.FOOL", line 9
ORA-06512: at line 1
Yes, is 255 bytes per line the upper limit. Actaully the column value is in number of lines but package treats in one line. How to increase this display over 255 bytes in SQL%Plus.
---sesh2002
0
Comment
Question by:sesh2002
9 Comments
 

Expert Comment

by:cgilbert78fr
ID: 6879540
Did you try setting "serveroutput wrapped", which should wrap lines within the linesize value, as needed for your output ?
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6880000
The ENABLE procedure in DBMS_OUTPUT sets the size of the output buffer while executing.  The maximum size is 1,000,000.  Just use this line in your procedure before calling PUT_LINE:

DBMS_OUTPUT.ENABLE(1000000);

You can use any buffer size you want.

Andrew
0
 

Author Comment

by:sesh2002
ID: 6881843
cqilbert78fr, serveroutput wrapped is not there in SQL*PLus. But set wrap on/off it is there but using it, still the error persists.
asimovsky, In SQL*Plus, even if we enable greater size, the error persists. But writing to a file on server, it is okay. Do we have any other packages or SQL*Plus parameter whereby we can avoid this error. --- sesh 2002
0
 

Author Comment

by:sesh2002
ID: 6881850
asimovsky, what i did was to create a table with single varchar2(300) column and then inserted a string of > 255 characters. When i retreive in PL/SQL and try to display using DBMS_OUTPUT.PUT_LINE, I get this error. -- sesh2002
0
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.

 

Author Comment

by:sesh2002
ID: 6881853
asimovsky, what i did was to create a table with single varchar2(300) column and then inserted a string of > 255 characters. When i retreive in PL/SQL and try to display using DBMS_OUTPUT.PUT_LINE, I get this error. -- sesh2002
0
 
LVL 11

Expert Comment

by:pennnn
ID: 6882923
You can't avoid that limit of 255 bytes per line.
In fact (I hope the other experts will agree) DBMS_OUTPUT is one of the worst thing created by Oracle. And unfortunately there are no alternative packages that can be used instead.
What you can do is create a simple procedure/package which you'll use instead of DBMS_OUTPUT.
What you have to do is parse the string that needs to be displayed, break it in smaller chunks and display it using DBMS_OUTPUT.
You can even make it even more flexible by overloading your procedures to accept whatever types of variables you want. DBMS_OUTPUT.PUT_LINE accepts only VARCHAR2, DATE and NUMBER.
The easier (and more expensive) way is to purchase such a package. I think that the RevealNet PL/Vision package created by Steven Feuerstein includes that functionality. Unfortunately after Quest Software took over Revelnet evrything on that great site has moved and I can't find anything there!
You can search the site for the PL/Vision package or maybe some other expert knows where to get it from, or knows some other similar tool...
Hope that helps!
0
 
LVL 9

Expert Comment

by:konektor
ID: 6885882
see sqlplus help

set linesize xxx - sets limit of line length of output
set serveroutput on xxx - sets limit of bytes printed to output
0
 
LVL 11

Expert Comment

by:pennnn
ID: 6886494
konektor, the set linesize is a SQL*Plus setting and it doesn't change the limit of the DBMS_OUTPUT. You can set the linesize to several thousand, but you still won't be able to display more than 255 bytes using DBMS_OUTPUT.
set serveroutput on xxx - sets limit of bytes printed to output - that is true, but it sets the total buffer size of DBMS_OUTPUT messageS. It again doesn't affect the DBMS_OUTPUT.PUT_LINE limit.
0
 
LVL 5

Accepted Solution

by:
sora earned 30 total points
ID: 6888053
No problem - use procedure SORA_PUT_LINE instead of DBMS_OUTPUT.PUT_LINE   !!!!


create or replace procedure
sora_put_line( line in varchar2, limit in integer)
is
x varchar2(4000);
n pls_integer := null;
s pls_integer := 1;
begin
--
n:=length(line);
x:=line;
Loop
 x := substr(line,s,least(limit,(length(line)-s+1) ));
 s := s + length(x);
 --
 dbms_output.put_line(x);
 --
 if s > length(line)
 then
   exit;
 end if;
 --
end loop;
--
end sora_put_line;
/



Usage example:
--------------

 SORA_PUT_LINE( string  IN VARCHAR@,
                char_per_line IN INTEGER);

****Note: the char_per_line must be less than 255, but the string can be much much much more than 255 chars******

declare
 w varchar2(1000) := '12345678901234567890123456789012345678901234567890';
begin
 w:=w||w||w||w||w||w||w||w||w||w||w||w;
 sora_put_line(w,255);
end;
/


As you can see the above example is able to output a string which is 50 x 12 = 600 characters

In fact, there is no limit on this one:



sora
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 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

13 Experts available now in Live!

Get 1:1 Help Now