OraFAQ Forum: Enterprise Manager » ORA-20000: ORU-10027:

Home » RDBMS Server » Enterprise Manager » ORA-20000: ORU-10027:

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-20000: ORU-10027: [message #68276]

Sun, 02 March 2003 08:14

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

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68279 is a reply to message #68276]

Mon, 03 March 2003 05:57

Mahesh Rajendran
Messages: 10708
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 >

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68604 is a reply to message #68276]

Wed, 07 April 2004 05:33

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

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68674 is a reply to message #68604]

Wed, 09 June 2004 07:07

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

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68681 is a reply to message #68674]

Sun, 20 June 2004 06:19

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.

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68684 is a reply to message #68279]

Wed, 23 June 2004 20:11

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

Report message to a moderator

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

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;
/

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68763 is a reply to message #68276]

Wed, 01 September 2004 01:10

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...

Report message to a moderator

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

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

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68820 is a reply to message #68276]

Thu, 21 October 2004 02:55

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...

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68821 is a reply to message #68276]

Thu, 21 October 2004 04:12

Sunil
Messages: 132
Registered: September 1999

Senior Member

Just set SET SERVEROUTPUT ON SIZE n", where
"n" is 1000000 or less...

Report message to a moderator

Re: ORA-20000: ORU-10027: [message #68827 is a reply to message #68276]

Wed, 27 October 2004 15:48

G.V.SenthilKumar
Messages: 1
Registered: October 2004

Junior Member

change the the size of serveroutout.
i.e.,

Set serveroutput on 2000000

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Get Error 997 when start managment server service
Next Topic:	OC4J Configuration issue on Windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 07:42:30 CST 2024