3 Replies Latest reply: Oct 1, 2011 2:41 AM by DanyC RSS

    Incosistency across the for loop update

    DanyC
      Hi,

      I have the following issue: i'm trying to update 10k rows out of 20k in a table using a for loop but for some reason i don't get the expected output.

      I have the following table:
       
      desc table_test 
      Name         Null     Type          
      ------------ -------- ------------ 
      value1_id    NOT NULL VARCHAR2(50) 
      value2_ID                VARCHAR2(20) 
      
      
      SQL> select * from table_test; 
      
      value1_ID            value2_ID 
      -------------------- -------------------- 
      4732000001           94000 
      4732000002           94001 
      4732000003           94002 
      4732000004           94003 
      4732000005           94004 
      
      SQL> 
      My goal is to update value2_id where value1_id is within a specific range. The output should look like - Please note that i need to keep the leading 0.
       
      SQL> select * from table_test; 
      
      value1_ID            value2_ID 
      -------------------- -------------------- 
      4732000001           000094000 
      4732000002           000094001 
      4732000003           000094002 
      4732000004           000094003 
      4732000005           000094004 
      
      SQL> 
      I have used the following code where i get

       
      SQL> ed 
      Wrote file afiedt.buf 
      
        1  DECLARE 
        2   x1 varchar2(10) := '4732000000'; 
        3   x2 varchar2(9)  := '000094000'; 
        4  BEGIN 
        5   for j in 1 .. 5 
        6    LOOP 
        7     x1 := x1 + j; 
        8     x2 := x2 + j -1; 
        9     --EXECUTE immediate 'update table_test set value2_id = ' || x2 || ' where value1_id = ' || x1 ; 
      10      dbms_output.put_line(x1 || ' and ' || x2 ); 
      11      dbms_output.put_line(sql%rowcount || ' rows updated '); 
      12    END LOOP; 
      13    COMMIT ; 
      14* END; 
      SQL> / 
      4732000001 and 94000 
      rows updated 
      4732000003 and 94001 
      rows updated 
      4732000006 and 94003 
      rows updated 
      4732000010 and 94006 
      rows updated 
      4732000015 and 94010 
      rows updated 
      
      PL/SQL procedure successfully completed. 
      
      SQL> 
      What i don't understand is:

      1) why the output is not sequential like 1,2,3,4,5 but is 1,3,6,10,15 ?? How it does get the values?
      2) same as #1 + how can i keep the leading 0?
      3) when i've uncommented the execute dml, the sql%rowcount doesn't display any rows , it's 0 all the time. Am i doing something wrong?


      Thank you,
      Dani
        • 1. Re: Incosistency across the for loop update
          sybrand_b
          What I don't understand is

          #1) Why do you store numbers as varchar2s? You are asking for trouble
          #2) Why do you add a number to a character string?
          #3) Why do you use execute immediate, where you don't need it at all?
          #4) Why do you expect this junk to work properly?

          Please
          #1 drop the table and redesign
          #2 drop this pathetic code

          ---------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Incosistency across the for loop update
            AndreyN
            Hi,
            DECLARE 
              x1 number := 4732000000; 
              x2 number := 94000 - 1; 
            BEGIN 
              for j in 1 .. 5 
              LOOP 
                  x1 := x1 + 1; 
                  x2 := x2 + 1; 
                  --
                  update table_test 
                     set value2_id = to_char(x2)
                   where value1_id =  to_char(x1); 
                  -- 
                  dbms_output.put_line(to_char(x1) || ' and ' || lpad(to_char(x2),9,'0')); 
                  dbms_output.put_line(sql%rowcount || ' rows updated '); 
                  --
              END LOOP; 
              COMMIT ; 
            END; 
            / 
            • 3. Re: Incosistency across the for loop update
              DanyC
              @user6806750 - many thanks for your help, it does work with a minor correction
              DECLARE 
                x1 number := 4732000000; 
                x2 number := 94000 - 1; 
              BEGIN 
                for j in 1 .. 5 
                LOOP 
                    x1 := x1 + 1; 
                    x2 := x2 + 1; 
                    --
                    update table_test 
                       set value2_id = lpad(to_char(x2),9,'0')
                     where value1_id =  to_char(x1); 
                    -- 
                    dbms_output.put_line(to_char(x1) || ' and ' || lpad(to_char(x2),9,'0')); 
                    dbms_output.put_line(sql%rowcount || ' rows updated '); 
                    --
                END LOOP; 
                COMMIT ; 
              END; 
              / 
              @Sybrand - being an active memeber here, i'd expected a different answer from you. I do take your criticism but ...i won't comment anything else.

              Thanks,
              Dani