Re: pl/sql update problem

Hi Ralf,

I'll have a closer look a bit later, but a first impression is your commit within the loop...select for update will lock your rows, but your commit will break that....do you have a development (smaller) version of the table that you could test the code on without trying to commit every 1000 records?

You say your "ltrim(rtrim(to_char(to_number(c1. KTO_NR_AUFTRG),'0999999999'))) " syntax is valid ? If that's the case, I'm betting the culprit has something to do with that first commit..

Best regards,
Steve

Ralf Bender wrote:

> hello
>
> i want to update many records with pl/sql statement. KTO_NR_AUFTRG is a CHAR
> Field. I want to set all with trailing '0' until Field length = 10 and commit
> every 2000 records, because there too much records, so it will run into rbs error.
> here is my statement, but it won't update anything:
>
> declare
> i number := 0;
> cursor s1 is SELECT * FROM tbl_belege
> WHERE length(KTO_NR_AUFTRG) < 10
> FOR UPDATE;
> begin
> for c1 in s1 loop
> update tbl_belege
> set KTO_NR_AUFTRG = ltrim(rtrim(to_char(to_number(c1.
> KTO_NR_AUFTRG),'0999999999')))
> where current of s1;
> i := i + 1; -- Commit after every X records
> if i > 1000 then
> commit;
> i := 0;
> end if;
>
> end loop;
> commit;
> end;
> /
>
> a select :
> select ltrim(rtrim(to_char(to_number(KTO_NR_AUFTRG),'0999999999')))
> from tbl_belege
>
> will bring all what i want to have...
>
> what's wrong???
>
> thx
>
> ralf
>
> --
> _____________________________________________________________
> NewsGroups Suchen, lesen, schreiben mit http://netnews.web.de
Received on Thu Apr 12 2001 - 06:32:48 CDT