Home » SQL & PL/SQL » SQL & PL/SQL » How can I deal with this exception"buffer flow" when I use cursor. (oracle 10.2.0 , RHEL5)
How can I deal with this exception"buffer flow" when I use cursor. [message #306793] Sun, 16 March 2008 20:46 Go to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
Does somebody can help me?

the following is the codes:

DECLARE
CURSOR tabs_cur(b_owner VARCHAR2) IS
SELECT table_name FROM all_tables@rcdc WHERE owner = b_owner;
BEGIN
FOR cursor_rec IN tabs_cur('CDC') LOOP
dbms_output.put_line(cursor_rec.table_name);
END LOOP;
END;

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: 在"SYS.DBMS_OUTPUT", line 35
ORA-06512: 在"SYS.DBMS_OUTPUT", line 198
ORA-06512: 在"SYS.DBMS_OUTPUT", line 139
ORA-06512: 在line 6

SQL> select count(*) from all_tables@rcdc where owner = 'CDC';

COUNT(*)
----------
2124

thanks.
Re: How can I deal with this exception"buffer flow" when I use cursor. [message #306794 is a reply to message #306793] Sun, 16 March 2008 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>DECLARE
>CURSOR tabs_cur(b_owner VARCHAR2) IS
>SELECT table_name FROM all_tables@rcdc WHERE owner = b_owner;
>BEGIN
DBMS_OUTPUT.ENABLE(2000000);
Re: How can I deal with this exception"buffer flow" when I use cursor. [message #306795 is a reply to message #306793] Sun, 16 March 2008 20:51 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Add this line at the top of your PL/SQL
dbms_output.enable(null);


Ross Leishman
Re: How can I deal with this exception"buffer flow" when I use cursor. [message #306798 is a reply to message #306795] Sun, 16 March 2008 20:58 Go to previous message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
thanks, the "over flow" exception took place in dbms_output, not CURSOR, I know it.
thanks you all.
Previous Topic: Exceptions
Next Topic: PLS-00201: identifier 'TO_NCHAR' must be declared
Goto Forum:
  


Current Time: Tue Feb 18 12:51:35 CST 2025