6 Replies Latest reply on Jun 26, 2011 6:41 PM by William Robertson

    PL/SQL Updating cursor where current of

    361137
      I am trying to use a cursor to load a query with a multi table join and then use where current of to update each result, but its not working. It executes extremely fast (way too fast to actually do anything) and when I select the field I expect is update I dont find any updates.

      So it's something like this:

      cursor my_cur is
      select A.ID as AID, B.ID as BID
      FROM A,B,C
      WHERE ... complicated join on the 3 tables
      for update;
      begin
      for r in my_cur loop
      update A set AID = BID where current of my_cur;
      end loop;
      commit;
      end;

      Any clue why this wouldn't work? I am totally inexperience with pl/sql so I am sure I missed something trivial or completely misunderstood something!

      Could it have to do with my "complicated join on the 3 tables" ?
        • 1. Re: PL/SQL Updating cursor where current of
          Solomon Yakobson
          Issue:
          select count(*)
          FROM A,B,C
          WHERE ... complicated join on the 3 tables
          /
          And see how namy rows it returns. Based on "I dont find any updates" most likely your query returns no rows. If so, examine join condition logic.

          SY.
          • 2. Re: PL/SQL Updating cursor where current of
            361137
            Yeah that's what I thought initially, so I ran that select query by itself and found 106 records. But then when I checked the see if the actual field is updated, I only see NULLs. I double checked to make sure I am updating the correct column with the correct values - none of the values I pull are NULL.

            Then I tried this:

            begin
            for m in ( <Same exact select as the one above, minus the "for update"> ) loop
            update A set A.ID = B.ID where <repeat of where caluse in the join>
            end loop;
            commit;
            end;

            And this works fine. So I cant understand why it didnt work if I tried it with using a cursor
            • 3. Re: PL/SQL Updating cursor where current of
              Solomon Yakobson
              trant wrote:

              And this works fine.
              Oops, missed current of. You cannot use the CURRENT OF clause with a cursor declared with a join* since internally, the CURRENT OF mechanism uses the ROWID pseudocolumn and there is no way to specify which table the ROWID relates to:
              SQL> DECLARE
                2     v_sal NUMBER(8,2);
                3     CURSOR c1 IS SELECT sal FROM emp e,dept d where e.deptno = d.deptno FOR UPDATE;
                4  BEGIN
                5     OPEN c1;
                6     LOOP
                7        FETCH c1 INTO v_sal;
                8        UPDATE emp SET sal = sal * 1.02 WHERE CURRENT OF c1;
                9        EXIT WHEN c1%NOTFOUND;
               10     END LOOP;
               11  END;
               12  /
              DECLARE
              *
              ERROR at line 1:
              ORA-01410: invalid ROWID
              ORA-06512: at line 8
              
              
              SQL> 
              So something doesn't add up. In any case, you need to add A.ROWID to cursor and use it in update:
              SQL> DECLARE
                2     v_sal NUMBER(8,2);
                3     v_rid rowid;
                4     CURSOR c1 IS SELECT sal,e.rowid FROM emp e,dept d where e.deptno = d.deptno FOR UPDATE;
                5  BEGIN
                6     OPEN c1;
                7     LOOP
                8        FETCH c1 INTO v_sal,v_rid;
                9        UPDATE emp SET sal = sal * 1.02 WHERE rowid = v_rid;
               10        EXIT WHEN c1%NOTFOUND;
               11     END LOOP;
               12  END;
               13  /
              
              PL/SQL procedure successfully completed.
              
              SQL> 
              SY.
              • 4. Re: PL/SQL Updating cursor where current of
                361137
                Ah great, now I understand - thank you very much for that !

                Edit: can you tell me, which of these approaches would be more efficient? using the cursor to loop and update on rowid or doing that in line select in for loop followed by update on repeated where clause? I tend to think its the former but just wanted to make sure

                Edited by: trant on Jun 26, 2011 8:49 AM
                • 5. Re: PL/SQL Updating cursor where current of
                  Solomon Yakobson
                  Most efficient would be plain update:
                  update A A1 set A.ID = (
                                          select B.ID
                                             FROM A,B,C
                                             WHERE ... complicated join on the 3 tables
                                               AND A.ROWID = A1.ROWID
                                         )
                  where rowid in (
                                  select  a.rowid
                                    FROM  A,B,C
                                    WHERE ... complicated join on the 3 tables
                                 )
                  /
                  SY.
                  • 6. Re: PL/SQL Updating cursor where current of
                    William Robertson
                    As Solomon says, I would always try a plain UPDATE first.

                    Occasionally however, it can be more efficient to use updateable view syntax:
                    update ( select a.x as oldvalue, c.y as newvalue
                             from   a,b,c
                             where ... complicated join on the 3 tables )
                    set    oldvalue = newvalue
                    This requires foreign key constraints to be in place so that the result set is deterministic. If they are not, you might try the equivalent MERGE statement.

                    For small numbers of rows (i.e. where the cost is in retrieving the rows and the number of actual updates is trivial), a PL/SQL loop could be OK as well.