玉面飞龙的BLOG

declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at “SYS.DBMS_OUTPUT”, line 35
ORA-06512: at “SYS.DBMS_OUTPUT”, line 133
ORA-06512: at line 34

在Oracle9I中碰见如上错误 DBMS_OUTPUT每行有255个字符的限制，挺不爽的。尤其是在Exception中输出sqlerrm的时候，sqlerrm经常超过255个字符，需要修改为如下的方式

exception
when others then
dbms_output.put_line(’Error happen! ‘);
l_count := length(sqlerrm)/255;
FOR j IN 0..l_count LOOP
dbms_output.put_line(substr(sqlerrm,j*255+1,255));
END LOOP;
– dbms_output.put_line(’Error happen! ‘ || substr(sqlerrm,0,254));
end;

10g中这个限制就没有了，而且默认有无限大的buffer size.
Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package

The declaration of the package type DBMS_OUTPUT.CHARARR is enhanced to support the increased maximum of 32,767 bytes. Previously, the VARCHAR2 constraint was 255 bytes. This determines the maximum line size that can be written by the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE procedures. The procedure DBMS_OUTPUT.ENABLE (buffer_size integer defaults to 20000) has a new allowed value of NULL for the buffer_size. In this case, NULL means “unlimited.” SQL*Plus, through the SET SERVEROUTPUT ON command, enables you to take advantage of the new “unlimited” meaning.

PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle Database 10g Release 1 and earlier, were constrained by the 255 byte limit. When using SQL*Plus, most programmers are regularly caught by the small default overall limit and sometimes by the current 1,000,000 maximum overall limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether.