2 problems in using package........

From: Jimmy <c6635500_at_comp.polyu.edu.hk>
Date: Wed, 12 Aug 1998 19:09:16 -0700
Message-ID: <35D24ACC.4803@comp.polyu.edu.hk>

Hello all,

        I have 2 problems about using package.

        I have a table TTT and want to update column CCC (all rows) in table TTT. The no. of rows in table TTT is 15000. I create package as follows:

	create or replace package update_ccc is
           procedure p_update_ccc;
	end update_ccc;

	create or replace package body update_ccc is
	   procedure p_update_ccc is
              tmp_ID TTT.ID%type;
	      cursor c1 is select ID from TTT;
	   begin
	      open c1;
	      loop
	         fetch c1 into tmp_ID;
		 exit when c1%NOTFOUND;
		 if tmp_ID = 1 then
                    dbms_output.putline('Update 1 : ' || tmp_ID);
		    update TTT set CCC = 1 where ID = tmp_ID;
                    commit;
		 else
                    dbms_output.putline('Update 2 : ' || tmp_ID);
		    update TTT set CCC = 2 where ID = tmp_ID;
                    commit;
		 end if;
	      end loop;
	      close c1;
	   end;
	end update_ccc;

	Then I execute this package in SQL*PLUS as:
	SQL> set serveroutput on
	SQL> execute update_ccc.p_update_ccc;
	
	Then after somtime, following message show:
	ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
	ORA-06512: at "SYS.DBMS_OUTPUT", line 100
	ORA-06512: at "SYS.DBMS_OUTPUT", line 59
	ORA-06512: at "USER.UPDATE_CCC", line 64
	
	1) Could anyone tells me why such message show and how to solve this

problem?

        Then I remove the DBMS_OUTPUT.PUTLINE lines and execute this package again.

	After the package is finish running, following message show:
	ORA-01555:snapshot too old(rollback segment too small)
	ORA-06512: at "USER.UPDATE_CCC", line 14
	
	2) Could anyone tells me why such message show and how to solve this

problem?

Thanks,
Jimmy Received on Wed Aug 12 1998 - 21:09:16 CDT