ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

    當用cmd視窗執行procedure的時候，有時候會出現螢幕沒有輸出或者螢幕輸出時溢位的情況，解決方法如下 set serveroutput on ：
    1. 在螢幕顯示輸出內容
SQL> conn / as sysdba
 已連線。
SQL> begin
2  dbms_output.put_line('hello');
3  end;
4  /
PL/SQL 過程已成功完成。/*螢幕無輸出*/

SQL> set serveroutput on /*開啟螢幕顯示輸出*/
SQL> begin
  2  dbms_output.put_line('hello');
  3  end;
  4  /
hello  /*螢幕輸出*/              
PL/SQL 過程已成功完成。

2. ORU-10028: line length overflow, limit of 255 chars per line
     當螢幕輸出位元組長度超過255位元組，會報錯，但是這種限制只侷限於9i，oracle 10g release2中取消了255個位元組的限制。
1) 9i
C:\Documents and Settings\zhanglei>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 25 14:19:31 2009
Copyright (c) 1982, 2002, Oracle.  All rights reserved.
SQL> set serveroutput on
SQL> declare
  2  v_string varchar2(500 char);
  3  begin
  4  v_string := 'five yeares of programming. This is th time it '
  5  || 'tables for most parogrammers to feel as if they ''ve '
  6  || 'com of age as a professional. Traditional '
  7  || 'progamming concepts are second nature, like '
  8  || 'driving a car or typing on a keyboard. Employers '
  9  || 'recongnize this , often ..................';
 10  dbms_output.put_line(v_string);
 11  end;
 12  /
declare
*
ERROR 位於第 1 行:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: 在"SYS.DBMS_OUTPUT", line 35
ORA-06512: 在"SYS.DBMS_OUTPUT", line 133
ORA-06512: 在line 10

網上查到解決辦法調整快取，但是無效
SQL> set serveroutput on size 100000
SQL> declare
  2  v_string varchar2(500 char);
  3  begin
  4  v_string := 'five yeares of programming. This is th time it '
  5  || 'tables for most parogrammers to feel as if they ''ve '
  6  || 'com of age as a professional. Traditional '
  7  || 'progamming concepts are second nature, like '
  8  || 'driving a car or typing on a keyboard. Employers '
  9  || 'recongnize this , often ..................';
 10  dbms_output.put_line(v_string);
 11  end;
 12  /
declare
*
ERROR 位於第 1 行:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: 在"SYS.DBMS_OUTPUT", line 35
ORA-06512: 在"SYS.DBMS_OUTPUT", line 133
ORA-06512: 在line 10

使用SUBSTR函式來解決這個問題
SQL> set serveroutput on
SQL> declare
  2  v_string varchar2(500 char);
  3  begin
  4  v_string := 'five yeares of programming. This is th time it '
  5  || 'tables for most parogrammers to feel as if they ''ve '
  6  || 'com of age as a professional. Traditional '
  7  || 'progamming concepts are second nature, like '
  8  || 'driving a car or typing on a keyboard. Employers '
  9  || 'recongnize this , often ..................';
 10  dbms_output.put_line(substr(v_string,1,255));
 11  dbms_output.put_line(substr(v_string,256));
 12  end;
 13  /
five yeares of programming. This is th time it tables for most parogrammers to 
feel as if they 've com of age as a professional. Traditional progamming       
concepts are second nature, like driving a car or typing on a keyboard.        
Employers recongnize this , oft                                                
en ..................                                                          
PL/SQL 過程已成功完成。

2) 10.2.0.1       (10g release2中取消了255個位元組的限制)
C:\Documents and Settings\zhanglei>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 25 15:19:31 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn / as sysdba
已連線。
SQL> spool c:\output.sql
SQL> set serveroutput on
SQL> declare
  2  v_string varchar2(500 char);
  3  begin
  4  v_string := 'five yeares of programming. This is th time it '
  5  || 'tables for most parogrammers to feel as if they ''ve '
  6   || 'com of age as a professional. Traditional '
  7  || 'progamming concepts are second nature, like '
  8  || 'driving a car or typing on a keyboard. Employers '
  9  || 'recongnize this , often ..................';
 10  dbms_output.put_line(v_string);
 11  end;
 12  /
five yeares of programming. This is th time it tables for most parogrammers to
feel as if they 've com of age as a professional. Traditional progamming
concepts are second nature, like driving a car or typing on a keyboard.
Employers recongnize this , often ..................
PL/SQL 過程已成功完成。