Re: Buffer overflow error

The dbms_output buffer is by default 2K. It is only flushed on job completion. Try set serveroutput on size 16384. The maximum buffer is 1M. The buffer comes out of the shared pool and every user of the routine takes a buffer (or at least did with ver 7) even if they do not execute the dbms_output statement.

In article <8d7ahk$dq6$1_at_nnrp1.deja.com>,   alistair.thomson_at_sphinxcst.co.uk wrote:
> I am trying to run a procedure that re creates primary keys.
> I'm getting the following error in SQL*Plus:-
> ERROR at line 1:
> ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> ORA-06512: at "SYS.DBMS_OUTPUT", line 106
> ORA-06512: at "SYS.DBMS_OUTPUT", line 65
> ORA-06512: at "SYS.P_PRIMARYKEY", line 38
> ORA-06512: at line 1
>
> The procedure is:-
>
> set serveroutput on
> spool c:\buildp_k.sql
>
> create or replace procedure p_primarykey as
>
> v_previous_name dba_ind_columns.index_name%TYPE := '';
> column_list VARCHAR2(512) := ' ';
> v_first integer := 1;
>
> cursor c_pkey is
> select
> c.owner, c.table_name, i.index_name, column_name,
> initial_extent, tablespace_name
> from dba_constraints c, dba_ind_columns i, dba_indexes x
> where c.constraint_name = i.index_name
> and i.index_name = x.index_name
> and c.constraint_type ='P'
> and c.owner = 'DISCOVER'
> order by c.owner, c.table_name;
>
> v_save_pkey c_pkey%ROWTYPE;
>
> begin
>
> column_list := ' ';
>
> for v_pkey in c_pkey
> loop
>
> if v_first = 1
> then
> v_save_pkey := v_pkey;
> v_previous_name := v_pkey.index_name;
> v_first := 0;
> end if;
>
> if (v_pkey.index_name <> v_previous_name)
> then
> dbms_output.put_line('alter
> table '||v_save_pkey.owner||'.'||v_save_pkey.table_name||' add
> constraint '||v_save_pkey.index_name);
> dbms_output.put_line(' primary key '||column_list||')');
> dbms_output.put_line(' using index storage
> (initial '||v_save_pkey.initial_extent/1024||'k
> next '||v_save_pkey.initial_extent/1024||'k pctincrease 0)');
> dbms_output.put_line
> ('tablespace '||v_save_pkey.tablespace_name||';');
> dbms_output.put_line('#');
> column_list := ' ';
> end if;
>
> if column_list = ' '
> then
> column_list := '('||v_pkey.column_name;
> else
> column_list := column_list||','||v_pkey.column_name;
> end if;
>
> v_save_pkey := v_pkey;
> v_previous_name := v_pkey.index_name;
>
> end loop;
>
> if column_list <> ' '
> then
> dbms_output.put_line('alter
> table '||v_save_pkey.owner||'.'||v_save_pkey.table_name||' add
> constraint '||v_save_pkey.index_name);
> dbms_output.put_line(' primary key '||column_list||')');
> dbms_output.put_line(' using index storage
> (initial '||v_save_pkey.initial_extent/1024||'k
> next '||v_save_pkey.initial_extent/1024||'k pctincrease 0)');
> dbms_output.put_line
> ('tablespace '||v_save_pkey.tablespace_name||';');
> end if;
>
> /*
>
> using index storage (initial '||initial_extent/1024||'k
> next '||initial_extent/1024||'k pctincrease 0)
> tablespace '||tablespace_name||';'
>
> from user_constraints c, user_ind_columns i, user_indexes x
> where c.constraint_name = i.index_name
> and i.index_name = x.index_name
> and c.constraint_type ='P';
> */
>
> end p_primarykey;
> /
>
> spool off
> set serveroutput off
>
> If you can help please e-mail me at mark.thomson_at_sphinxcst.co.uk
> Many Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.