RE: Buffer overflow error

From: Jakobs, Jef <JJakobs_at_HARTIS.com>
Date: 2000/04/21
Message-ID: <552D929F31ECD211A5DB00A0C9D1C4DCDADD62@hartisexch.hartis.com>#1/1

Try "set serveroutput on size 10000"

It will give dbms_output a bigger buffer to store the output.

Jef Jakobs

-----Original Message-----
From: alistair.thomson_at_sphinxcst.co.uk
[mailto:alistair.thomson_at_sphinxcst.co.uk] Sent: Friday, April 14, 2000 9:39 AM
To: comp.databases.oracle.server_at_list.deja.com Subject: Buffer overflow error

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. Received on Fri Apr 21 2000 - 00:00:00 CDT