Home » RDBMS Server » Enterprise Manager » ORA-20000: ORU-10027:
ORA-20000: ORU-10027: [message #68276] Sun, 02 March 2003 08:14 Go to next message
snreddy
Messages: 16
Registered: January 2003
Junior Member
When I was running PL/sql script
Iam getting following errror
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 24

can anybody suggest me how to rectify this
thanks in advance

Cheers
snreddy
Re: ORA-20000: ORU-10027: [message #68279 is a reply to message #68276] Mon, 03 March 2003 05:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10697
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
ISSUE THIS before executing the pl/sql body.
u need to increase it
mag@itloaner1_local > exec dbms_output.enable('10000000000');

PL/SQL procedure successfully completed.

mag@itloaner1_local > 

Re: ORA-20000: ORU-10027: [message #68604 is a reply to message #68276] Wed, 07 April 2004 05:33 Go to previous messageGo to next message
Rama Chandra Prasad
Messages: 3
Registered: April 2004
Junior Member
Hi...

Pls issue the following before running your plsql script.

set serveroutput on size 1000000

Range for this size is from 2000 to 1000000
Re: ORA-20000: ORU-10027: [message #68674 is a reply to message #68604] Wed, 09 June 2004 07:07 Go to previous messageGo to next message
Jaymik
Messages: 1
Registered: June 2004
Junior Member
Hello All,
Thank you so much for the above solution. Can you please tell me what and how can i write the output to a file.

Thank You,
Jaymik
Re: ORA-20000: ORU-10027: [message #68681 is a reply to message #68674] Sun, 20 June 2004 06:19 Go to previous messageGo to next message
N Rukmangadha Reddy
Messages: 1
Registered: June 2004
Junior Member
Before executing the PL/SQL or SQL SCRIPT USE

SPOOL ON RukmaFile.txt;

EXECUTE THE SQL SCRIPT

SPOOL OFF;

Then exit the SQLPLUS and see the file RukmaFile.txt for the data.
Re: ORA-20000: ORU-10027: [message #68684 is a reply to message #68279] Wed, 23 June 2004 20:11 Go to previous messageGo to next message
Nermin
Messages: 3
Registered: June 2004
Junior Member
Hello,

But when we use
>exec dbms_output.enable('10000000000');
l get the following error:
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

Is there a way to overcome this problem?
Thank you
Stored Procedure to pass only a table name as parameter and fetch its all the values and display al [message #68706 is a reply to message #68604] Fri, 16 July 2004 02:19 Go to previous messageGo to next message
Devesh Joshi
Messages: 1
Registered: July 2004
Junior Member
create or replace procedure devtest_sp1 (strTableName in varchar2)
as
cur INTEGER ;
fdbk INTEGER;
v_rows integer;
aid number;
mnth date;
iCnt INTEGER;
strSQL varchar2(1000);
strColumn varchar2(32000);
l_cnt integer;
TYPE tArray IS TABLE OF CHAR(60) INDEX BY BINARY_INTEGER;
tString tArray;
BEGIN
--execute immediate 'set serveroutput on size 100000';
strsql:= 'SELECT count(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''' || upper(strTableName) || '''';
execute immediate strsql into l_cnt;
--dbms_output.put_line(l_cnt);
cur := dbms_sql.open_cursor;
strsql:= 'SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''' || upper(strTableName) || '''';
DBMS_SQL.PARSE(cur, strsql, DBMS_SQL.native);
iCnt:=1;
--for iCnt in 1 .. l_cnt
--loop
tString(iCnt):='0';
DBMS_SQL.DEFINE_COLUMN_CHAR(cur, iCnt, tString(iCnt),250);
-- end loop;
v_rows := DBMS_SQL.EXECUTE(cur);
LOOP
IF DBMS_SQL.FETCH_ROWS(cur)=0 THEN
exit;
end if;
--for iCnt in 1 .. l_cnt
--loop
DBMS_SQL.COLUMN_VALUE_CHAR(cur, iCnt, tString(iCnt));
strColumn := strColumn || trim(tString(iCnt)) || ',';
--end loop;
END LOOP;
--DBMS_OUTPUT.PUT_LINE (strcolumn);
cur := dbms_sql.open_cursor;
dbms_sql.close_cursor(cur);
cur := dbms_sql.open_cursor;
strsql:= 'SELECT ' || left(trim(strcolumn),length(trim(strcolumn))-1) || ' FROM ' || strTableName;
--DBMS_OUTPUT.PUT_LINE (strsql);
DBMS_SQL.PARSE(cur, strsql, DBMS_SQL.native);
iCnt:=1;
for iCnt in 1 .. l_cnt
loop
tString(iCnt):='0';
DBMS_SQL.DEFINE_COLUMN_CHAR(cur, iCnt, tString(iCnt),250);
end loop;
strColumn:=' ';
v_rows := DBMS_SQL.EXECUTE(cur);
LOOP
strColumn:=' ';
IF DBMS_SQL.FETCH_ROWS(cur)=0 THEN
exit;
end if;
for iCnt in 1 .. l_cnt
loop
DBMS_SQL.COLUMN_VALUE_CHAR(cur, iCnt, tString(iCnt));
strColumn := strColumn || trim(tString(iCnt)) || ' ';
end loop;
DBMS_OUTPUT.PUT_LINE (strColumn );
END LOOP;
END;
/
Re: ORA-20000: ORU-10027: [message #68763 is a reply to message #68276] Wed, 01 September 2004 01:10 Go to previous messageGo to next message
Jignesh
Messages: 6
Registered: April 2002
Junior Member
The DBMS_OUTPUT package stores all output from
PUT_LINE commands into a
buffer, and this error indicates you are overflowing
this buffer. The
default size is 2000. If you are executing from
SQL*Plus you can change the
size of the DBMS_OUTPUT buffer using the "SET
SERVEROUTPUT ON SIZE n", where
"n" is 1000000 or less...

Alternatively, if you are not using SQL*Plus, then
you can change the buffer
size using "DBMS_OUTPUT.ENABLE(n)", where "n" is the
size...

Hope this helps...
Re: Stored Procedure to pass only a table name as parameter and fetch its all t [message #68810 is a reply to message #68706] Thu, 07 October 2004 21:00 Go to previous messageGo to next message
ctranjith
Messages: 1
Registered: October 2004
Junior Member
Actually this is not what I need , Actually I need to pass values it may contain so many columns like saving a master details and item details with in a procedure
so we have more item details. these details can have more rows and columns i need to pass it to an sql server two options for this is using a temp table or by an array but I did't think is it is so good, that is why i am searching for a better solution

expecting reply
yours thankfully

C T Ranjith
Re: ORA-20000: ORU-10027: [message #68820 is a reply to message #68276] Thu, 21 October 2004 02:55 Go to previous messageGo to next message
Sunil Bhashetty
Messages: 1
Registered: October 2004
Junior Member
The DBMS_OUTPUT package stores all output from
PUT_LINE commands into a buffer, and this error indicates you are overflowing this buffer. The
default size is 2000. If you are executing from
SQL*Plus you can change the size of the DBMS_OUTPUT buffer using the "SET SERVEROUTPUT ON SIZE n", where
"n" is 1000000 or less...

Alternatively, if you are not using SQL*Plus, then
you can change the buffer size using "DBMS_OUTPUT.ENABLE(n)", where "n" is the
size...

Hope this helps...
Re: ORA-20000: ORU-10027: [message #68821 is a reply to message #68276] Thu, 21 October 2004 04:12 Go to previous messageGo to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Just set SET SERVEROUTPUT ON SIZE n", where
"n" is 1000000 or less...
Re: ORA-20000: ORU-10027: [message #68827 is a reply to message #68276] Wed, 27 October 2004 15:48 Go to previous messageGo to next message
G.V.SenthilKumar
Messages: 1
Registered: October 2004
Junior Member
change the the size of serveroutout.
i.e.,

Set serveroutput on 2000000
Re: Stored Procedure to pass only a table name as parameter and fetch its all t [message #68903 is a reply to message #68706] Mon, 24 January 2005 06:38 Go to previous message
Walter
Messages: 15
Registered: September 1999
Junior Member
Hi Rama

Thank you for writing this stored procedure. Can you perhaps help me. I need a stored procedure that will insert or update data in any table. It will take the table name as parameter and an array of data.

Thank you in advance
Walter
Previous Topic: Get Error 997 when start managment server service
Next Topic: OC4J Configuration issue on Windows
Goto Forum:
  


Current Time: Tue May 17 19:22:47 CDT 2022