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

Alternatively, since dbms_output is a simple stream -- move to a file using utl_file package.
The file size limit is the operating system limit -- not a 1M characters.

mike krolewski.

Jimmy 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
Received on Tue Aug 25 1998 - 00:00:00 CEST