Home » SQL & PL/SQL » SQL & PL/SQL » Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #48533] Fri, 26 November 2004 05:48 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Dear frends,

Please let me out of this prob.

SQL> Declare
  2  cursor c1 is select table_name,column_name from table_columns;
  3  --where table_name = 'DEMO_DH';
  4  cnt1 number(10);
  5  cnt2 number(10);
  6  cnt3 number(10);
  7  Begin
  8  for i in c1 loop
  9  /*execute immediate
 10  ('select count(*) from '||i.table_name||'where upper('||i.column_name||')=lower('||i.column_name||')');*/
 11  execute immediate(
 12  'select COUNT(*)  from '||i.table_name||' where '||'upper('||i.column_name||')'||' = '||'lower('||i.column_name||')') INTO CNT1
 13  ;
 14  execute immediate(
 15  'select COUNT(*)  from '||i.table_name||' where '||'upper('||i.column_name||')'||' = '||'lower('||i.column_name||')') INTO CNT2
 16  ;
 17  --DBMS_OUTPUT.PUT_LINE(CNT1);
 18  /*select count(*) into cnt2  from (select table_name from table_columns where table_name=tab_name)  where upper(i.column_name)!= lower(i
 19  if (cnt1 >0 and cnt2> 0) then
 20  dbms_output.put_line ('Table_name: '||i.table_name ||' '||'Column Name: '|| i.column_name);
 21  end if;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
Table_name: T_ROSTER_EMP_PREF_OFF Column Name: EMP_CODE
Table_name: T_ROSTER_EMP_PREF_OFF Column Name: INSERTED_BY
Table_name: T_ROSTER_OFFS Column Name: INSERTED_BY
Table_name: T_ROSTER_PERFORMANCE_MIX Column Name: INSERTED_BY
Table_name: M_TL_AGENT Column Name: TL_EMP_CODE
Table_name: M_TL_AGENT Column Name: AGENT_EMP_CODE
Table_name: M_VEHICLE_TYPE Column Name: INSERTED_BY
Table_name: M_VEHICLE_TYPE Column Name: UPDATED_BY
Table_name: M_VENDOR Column Name: VENDOR_CODE
Table_name: M_VENDOR Column Name: PINCODE
Table_name: M_VENDOR Column Name: PHONE1
Table_name: M_VENDOR Column Name: PHONE2
Table_name: M_VENDOR Column Name: PHONE3
Table_name: M_VENDOR Column Name: INSERTED_BY
Table_name: M_VENDOR Column Name: UPDATED_BY
Table_name: T_ROSTER_REP_TIME Column Name: INSERTED_BY
Table_name: T_ROSTER_SHIFT_DETAILS Column Name: INSERTED_BY
Table_name: T_ROSTER_SHIFT_REQ Column Name: INSERTED_BY
Table_name: M_PHYSICAL_INFO Column Name: VEHICLE_REG_NO
Table_name: M_PHYSICAL_INFO Column Name: CONTACT
Table_name: M_PROGRAM Column Name: INSERTED_BY
Table_name: M_PROGRAM Column Name: UPDATED_BY
Table_name: T_PLAN_REQ Column Name: INSERTED_BY
Table_name: T_PLAN_SHIFT_DETAILS Column Name: INSERTED_BY
Table_name: T_PLAN_SHIFT_REQ Column Name: INSERTED_BY
Table_name: T_PLAN_SLA Column Name: INSERTED_BY
Table_name: M_SUB_PROGRAM Column Name: INSERTED_BY
Table_name: M_SUB_PROGRAM Column Name: UPDATED_BY
Table_name: M_SYSTEM_PROFILE Column Name: NUMBER_FORMAT
Table_name: T_PLAN_OFF_DETAILS Column Name: INSERTED_BY
Table_name: T_CHG_REQ Column Name: EMP_CODE
Table_name: T_EMP_ROSTER Column Name: EMP_CODE
Table_name: T_LEAVE Column Name: EMP_CODE
Table_name: T_LOGIN Column Name: USERNAME
Table_name: T_ROSTER Column Name: INSERTED_BY
Table_name: T_ROSTER_AGENT_OFF_DETAILS Column Name: INSERTED_BY
Table_name: T_ROSTER_BLOCK_DETAILS Column Name: SHIFTNUM
Declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 34
ORA-06512: at "SYS.DBMS_OUTPUT", line 118
ORA-06512: at "SYS.DBMS_OUTPUT", line 81
ORA-06512: at line 20
Re: Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #48540 is a reply to message #48533] Fri, 26 November 2004 10:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
change:

set serveroutput on

to:

set serveroutput on size 1000000
Re: Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #48763 is a reply to message #48540] Tue, 14 December 2004 19:17 Go to previous messageGo to next message
sailaja
Messages: 11
Registered: October 1999
Junior Member
hi,

i had a problem..

PROCEDURE insertcategory( pPcteId
CATEGORYTYPE.cte_id%TYPE,
pcetId
ATEGORYELEMENT.cet_id%TYPE
) IS

-- here we are using the cursor...!

CURSOR categoryCur IS SELECT "category_id"
categoryId
, "parent_category_id"
parCatId
,( select "name"
from categories
where "category_id"
= gct."category_id"
) "name"
FROM globalscategorytree gct
START WITH
"parent_category_id" = 0
CONNECT BY PRIOR
"category_id" = "parent_category_id"

categoryCurRec categoryCur%ROWTYPE;
BEGIN
OPEN categoryCur;
LOOP
FETCH categoryCur INTO categoryCurRec;
EXIT WHEN categoryCur%NOTFOUND;
/* INSERT INTO CATEGORY ( cay_id
, cay_name
, parent_cay_id
, cay_fromdate
, cte_id
) VALUES ( SEQ_CATEGORY.NEXTVAL
, categoryCurRec."name"
, categoryCurRec. parCatId
, SYSDATE
, pPcteId
); */
DBMS_OUTPUT.put_line('INSIDE THE LOOP : ');

END LOOP;
CLOSE categoryCur;

END insertcategory;

in the above procedure...i am getting that error..

the query that i used..in cursor results....a set of coulmns..like 4 columns and 30 rows.
Re: Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #48776 is a reply to message #48763] Wed, 15 December 2004 13:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Did you try?:

set serveroutput on size 1000000

Did you try removing the dbms_output line?

If you are saying that you are getting the same error as the previous poster, then it appears to be a result of attempting to output more than dbms_output can handle. So, you can increase the size of the buffer that it uses or not use dbms_output, or add another procedure to handle the excess. The error that the other user received is actually a user-defined error, but the text suggests an overflow of the buffer used by dbms_output.
Re: Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #49360 is a reply to message #48776] Fri, 04 February 2005 09:50 Go to previous messageGo to next message
GY Liew
Messages: 1
Registered: February 2005
Junior Member
How about this problem: the package will read some data from a text file, which we will not know how many lines of data inside, and the package will print out the data with DBMS_OUTPUT. I am looking for something to flush the memory in DBMS_OUTPUT to free the buffer, but I don't seem to see any.

CREATE OR REPLACE PROCEDURE MY_TEST AS
fhandle UTL_FILE.FILE_TYPE;
mydata VARCHAR2(1024);
BEGIN
DBMS_OUTPUT.enable;
DBMS_OUTPUT.PUT_LINE('This is a test');
fhandle := UTL_FILE.FOPEN('e:20050112','dba_band.del','R');
LOOP
UTL_FILE.GET_LINE(fhandle, mydata);
DBMS_OUTPUT.PUT_LINE(mydata);
DBMS_OUTPUT.NEW_LINE;
-- UTL_FILE.FFLUSH(fhandle);
END LOOP;
UTL_FILE.FCLOSE(fhandle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data');
END;
Re: Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #49380 is a reply to message #49360] Sat, 05 February 2005 00:27 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Please click on the link below for some methods by Tom Kyte.

Previous Topic: SQL script for deleting the first digit
Next Topic: using clob in Oracle 9i
Goto Forum:
  


Current Time: Sat Jul 26 22:00:07 CDT 2025