Re: 2 problems in using package........

The buffer problem is corrected by either: SET SERVERTOUTPUT ON SIZE 1000000
- or -
DBMS_OUTPUT.ENABLE(1000000) The max buffer size is 1,000,000 bytes.

This does not guarantee it will work, but it will run a long time be failure. You would be better off to insert the message into a table, then getting the info from the table.

If the ID is not unique, you are getting the second error due to the updates that are being applied to the same table you are reading. Oracle performs a "read with integrity" and the snapshot has been forced out of the rollback. Try this without the commits. It may help.

HTH
   James

In article <35D24ADA.51F5_at_comp.polyu.edu.hk>, Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:

>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

-- 
lorenzen_at_tiny.net             | Life is complex; it has
                              |   real and imaginary parts