Re: 2 problems in using package........
Date: 1998/08/12
Message-ID: <35d4b619.9997675_at_192.86.155.100>#1/1
A copy of this was sent to Jimmy <c6635500_at_comp.polyu.edu.hk> (if that email address didn't require changing) On Wed, 12 Aug 1998 19:09:16 -0700, you wrote:
>Hello all,
> I have 2 problems about using package.
>
[snip]
> 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;
[snip]
>
> 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?
>
dbms_output defaults to a buffer of 2,000 characters. you can increase this upto 1meg
SQL> set serveroutput on size 1000000
> 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?
>
to solve -- don't commit inside the fetch loop.
Basically you have opened a cursor in the outside loop. This cursor needs a consistent view of the table it is reading from (the results of the cursor are 'preordained' when you open it). As you update inside the loop, you are changing the table and index structures. Oracle needs to read the rollback segments to get the consistent read view of the table (for the outer loop). As you commit inside the loop however, you are allowing Oracle to reuse the rollback that was generated by the update. The table data that Oracle needs to read from the rollback segment no longer exists -- we need that rollback data not only to rollback transactions when they fail but to construct read consistent views of the data. We need the rollback to stay in place, you are releasing it by committing.
A much faster way to do this would simply be to execute:
update TTT set CCC = decode( id, 1, 1, 2 );
that will update CCC to be the value 1 when the id is 1 and 2 otherwise.
>Thanks,
>Jimmy
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Aug 12 1998 - 00:00:00 CEST