当用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: Release9.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 过程已成功完成。