Home » SQL & PL/SQL » SQL & PL/SQL » how to i ncrease dbms_ouput buffer
how to i ncrease dbms_ouput buffer [message #226154] Thu, 22 March 2007 12:24 Go to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

my query
set serveroutput on
DECLARE
err_msg VARCHAR2(32767);
BEGIN
/* Get a few Oracle error messages. */
FOR err_num IN 1..28 LOOP
err_msg := SUBSTR(SQLERRM(-err_num),1,100);
dbms_output.put_line('Error number = ' || err_num);
dbms_output.put_line('Error message = ' || err_msg);
END LOOP;
END;
/

iam getting error like below after 21
Error number = 21
DECLARE
*

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 8

my question is how to increase dbms_output buffer limit
Re: how to i ncrease dbms_ouput buffer [message #226159 is a reply to message #226154] Thu, 22 March 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
DBMS_OUTPUT.ENABLE(10000000);
Re: how to i ncrease dbms_ouput buffer [message #226167 is a reply to message #226159] Thu, 22 March 2007 12:54 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

thank you i think 10g is unlimited iam using 10g
it works after using below one also
set serveroutput on 100000.

what is difference between set serveroutput on 100000
and dbms_output.enable(100000);
i know
set serveroutput on prints on screen
enable increases increases buffer size .
any thing more.?

[Updated on: Thu, 22 March 2007 12:54]

Report message to a moderator

Re: how to i ncrease dbms_ouput buffer [message #226183 is a reply to message #226167] Thu, 22 March 2007 14:39 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
One difference is that set serveroutput on is a SQL*Plus command, hence can only be run at a SQL prompt, while dbms_output.enable is an Oracle package.

[Updated on: Thu, 22 March 2007 14:40]

Report message to a moderator

Previous Topic: ORA-08177: in a query this dblink
Next Topic: Updating one table with data from other table..
Goto Forum:
  


Current Time: Sun Dec 04 06:12:18 CST 2016

Total time taken to generate the page: 0.14462 seconds