| Pl Help:ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes [message #48533] |
Fri, 26 November 2004 05:48  |
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 #48763 is a reply to message #48540] |
Tue, 14 December 2004 19:17  |
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 #49360 is a reply to message #48776] |
Fri, 04 February 2005 09:50 |
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;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
