| ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #46761] |
Tue, 29 June 2004 22:24  |
Vikash
Messages: 17
Registered: April 2002
|
Junior Member |
|
|
Hi every1,
After running a procedure in oracle sqlplus, it is executed successfully. But when i set serveroutput on to display messages i get the following error:
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 100
ORA-06512: at "SYS.DBMS_OUTPUT", line 59
ORA-06512: at line 22
Can anyone tell me wat action can be taken?
The procedure is as follows;
1 declare
2 cursor cur_street is
3 select STR_LOCA_CODE, STR_ZONE_CODE, STR_CODE,STR_DESC, STR_STATUS,
4 STR_COUNTRY_CODE
5 from cus_street
6 where str_desc is not null;
7 rec_street cur_street%rowtype;
8 p_firstpos number(5);
9 p_secondpos number(5);
10 p_charval number(2);
11 p_substring varchar2(255):=null;
12 p_string varchar2(255):=null;
13 p_startindex number(1):=1;
14 begin
15 open cur_street;
16 loop
17 -- count occurences of char , in string
18 fetch cur_street into rec_street;
19 exit when cur_street%notfound;
20 p_string := rec_street.str_desc;
21 p_firstpos := instr(p_string,',',1,1);
22 DBMS_OUTPUT.PUT_LINE('Desc: '||p_string|| ' - start pos-'||p_firstpos);
23 p_charval := 65;
24 p_startindex :=1;
25 while (p_firstpos>0) loop
26 p_secondpos := instr(p_string,',',1,2);
27 if p_startindex =1 then
28 p_secondpos := p_firstpos;
29 end if;
30 p_substring := substr(p_string,p_startindex,p_secondpos-1);
31 if p_secondpos = 0 then
32 p_substring := substr(p_string,p_startindex,length(p_string));
33 p_string := null;
34 end if;
35 DBMS_OUTPUT.PUT_LINE('2nd pos -'||p_secondpos||'-substring - '||p_substring);
40 p_charval:= p_charval + 1;
41 p_string := substr(p_string,p_secondpos,length(p_string));
42 DBMS_OUTPUT.PUT_LINE('new string - '||p_string);
43 p_firstpos := instr(p_string,',',1,1);
44 p_startindex :=2;
45 end loop;
46 end loop; -- cur_street
47 close cur_street;
48* end;
The function of the procedure is to find the comma ',' character, then to break the string into substrings wherever the comma is found.
Thanks.
|
|
|
|
|
|
|
|
| Re: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #47077 is a reply to message #47066] |
Wed, 21 July 2004 01:36  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
When WRAPPED is not enabled and truncate is enabled,
SQL*Plus truncates anything beyond LINESIZE.
examples:
scott@ORA92> variable g_test varchar2(66)
scott@ORA92> execute :g_test := 'This is for a test of set serveroutput on with and without wrapped'
PL/SQL procedure successfully completed.
scott@ORA92> -- without wrapped:
scott@ORA92> set serveroutput on format truncated
scott@ORA92> set linesize 10
scott@ORA92> execute dbms_output.put_line (:g_test)
This is fo
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 30
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serv
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 50
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serveroutput on with and
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 70
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serveroutput on with and without wrapped
PL/SQL procedure successfully completed.
scott@ORA92> -- with wrapped:
scott@ORA92> set serveroutput on format wrapped
scott@ORA92> set linesize 10
scott@ORA92> execute dbms_output.put_line (:g_test)
This is fo
r a test o
f set serv
eroutput o
n with and
without w
rapped
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 30
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serv
eroutput on with and without w
rapped
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 50
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serveroutput on with and
without wrapped
PL/SQL procedure successfully completed.
scott@ORA92> set linesize 70
scott@ORA92> execute dbms_output.put_line (:g_test)
This is for a test of set serveroutput on with and without wrapped
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
