7 Replies Latest reply: Jun 6, 2014 8:24 AM by Chrisjenkins-Oracle RSS

    Update in cursor loop broken.

    ilkinesrefli

      Hello, maybe there is a bug or mutation problem, this loop doesn't run and one update executed and loop broken. I've used bulk collect then loop. Is there any other solution? I am using TimesTen Release 11.2.2.5.0.

       

      DECLARE
           CURSOR c_address_book(p_user_id NUMBER) IS
                SELECT id, user_id, phone_number FROM address_book WHERE user_id = p_user_id;
           l_user_id NUMBER;
      BEGIN
           l_user_id := ...;
           FOR var_phones IN c_address_book(55)
           --SELECT id, user_id, phone_number FROM address_book WHERE user_id = 55;
           LOOP
                UPDATE address_book SET user_id = l_user_id WHERE id=var_phones.id;
           END LOOP;
          
           COMMIT;
      END;
      
        • 1. Re: Update in cursor loop broken.
          Chrisjenkins-Oracle

          It would be helpful to know what error(s) you are getting. Is this the exact code? Also, are you sure that you do not have a COMMIT inside the loop? Note that in TimesTen, unlike Oracle DB, COMMIT or ROLLBACK closes all open cursors for the session.

           

          Chris

          • 2. Re: Update in cursor loop broken.
            ilkinesrefli

            Yes, I am sure. I know commit inside the loop raises error, but this one the code doesn't raise error and only update one row and exit loop. You may test it.

            TimesTen Server is TimesTen Release 11.2.1.8.0

            • 3. Re: Update in cursor loop broken.
              Chrisjenkins-Oracle

              In order to test I need more information:

               

              1.    What is the definition, including constraints and indexes, of the 'address_book' table.

               

              2.    What data is present in the table.

               

              3.    How are you invoking/calling this PL/SQL code? Are you running it from ttIsql or from a program (Java, C, C++, ...)?

               

              Thanks,

               

              Chris

              • 4. Re: Update in cursor loop broken.
                ilkinesrefli

                1.    What is the definition, including constraints and indexes, of the 'address_book' table.

                CREATE TABLE address_book
                (
                  ID        NUMBER                              NOT NULL,
                  USER_ID   NUMBER                              NOT NULL,
                  PHONE_NUMBER    VARCHAR2(18 BYTE)                   NOT NULL
                );
                
                CREATE INDEX IDX_USERID_INT ON address_book
                (USER_ID);
                
                CREATE UNIQUE INDEX PK_ID_INT ON address_book
                (ID);
                
                
                

                 

                2.    What data is present in the table.

                This is sample data, there are millions of data in this table

                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560953, 1398675, '997408250089', 393241892);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560954, 1398675, '997408251010', 393241893);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560955, 1398675, '997408251512', 393241894);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560956, 1398675, '997408652119', 393241895);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560957, 1398675, '997408254141', 393241896);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560958, 1398675, '997408254554', 393241897);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560959, 1398675, '997438254888', 393241898);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560960, 1398675, '997408956628', 393241899);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560961, 1398675, '997408256711', 393241900);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560962, 1398675, '997428258222', 393241901);
                Insert into ADDRESS_BOOK
                   (ID, USER_ID, PHONE_NUMBER)
                Values
                   (398560963, 1398675, '997408259755', 393241902);
                COMMIT;
                
                
                

                 

                3.    How are you invoking/calling this PL/SQL code? Are you running it from ttIsql or from a program (Java, C, C++, ...)?

                Calling from Java with CallableStatement. Also from SQL Developer there is same problem



                • 5. Re: Update in cursor loop broken.
                  Chrisjenkins-Oracle

                  Can you please clarify what release you are using? You mention both 11.2.2.5.0 and 11.2.1.8.0 above.

                   

                  I just tested this out on 11.2.2.6.5 and it works just fine for me.

                   

                  Chris

                  • 6. Re: Update in cursor loop broken.
                    ilkinesrefli

                    Using TimesTen Release 11.2.1.8.0 - this is timesten server release.

                    • 7. Re: Update in cursor loop broken.
                      Chrisjenkins-Oracle

                      I haven't checked but this is probably a bug with repositioning on an index that is being used for a cursor based lookup and which is also begin updated (which is what you are doing here). I suggest you try with the very latest 11.2.1 release or better still move to 11.2.2.7.0.

                       

                      Chris