Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 2 problems in using package........

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

From: CaseMan <droth_at_tiac.net>
Date: Wed, 12 Aug 1998 19:53:40 GMT
Message-ID: <35d1f24e.33011107@news.tiac.net>


The problem is with dbms_output.

use

                        set serveroutput on size 20000

Good Luck
Dave Roth

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
Received on Wed Aug 12 1998 - 14:53:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US