Oracle SQL CURSOR Update with Join and Commit

Question

i want to update a table with the value of another table. But: I need a commit after every 50000 rows. I don't want to discuss why and I know the tip to create a new table instead of update, but this is not an option. I need only help with the query.

For update after x rows I found this:

DECLARE
  i NUMBER := 0;
  CURSOR G1 IS SELECT * FROM test_new 
               FOR UPDATE;
BEGIN
  FOR c1 IN S1 LOOP
      UPDATE test SET col1 = 'somevalue1'
             WHERE CURRENT OF G1;

      i := i + 1;              -- Commits after every X number of records
      IF i > 1000 THEN
         COMMIT;
         i := 0;
      END IF;

  END LOOP;
  COMMIT;
END;

To Update a table with another table this code works:

DECLARE
  l_r_id test_new.id_customer%type;
  l_r_name test_new.name%type;
  i NUMBER := 0;
  CURSOR CUR is select tnw.id_customer, tnw.name
                from test_new tnw
                   , test tes
                where tnw.id_customer = tes.id_customer
    FOR UPDATE;
BEGIN
    OPEN cur;
    LOOP 
      FETCH cur
       INTO l_r_id, l_r_name;
      UPDATE test
         set name = l_r_name
       where test.id_customer = l_r_id;
      i := i+1;
      EXIT WHEN cur%notfound;
    END LOOP;
    commit;

END;

But I don't know how to get

  IF i > 50000 THEN
     COMMIT;
     i := 0;
  END IF;

into the code. It seems there is a problem with FETCH and Commit. I get the Error Message from Oracle:

2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error.

Does anybody have an idea? I know there have been a way to join without "FETCH" but I don't know how. Like I said earlier, please only help with the code, no discussion about update and commit.

[Tom Kyte about frequent commits](https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4951966319022): "**is wrong, wrong, wrong. So wrong.... So very very wrong.**" — , Aug 21 '18 at 09:31
Something like this maybe? https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/static-sql.html#GUID-BC564905-51F4-4ADB-B300-9F15B1AC3099 — , Aug 21 '18 at 09:36
What error? Also, in your first example your `g1` cursor is `select from test_new for update` but then you update a different table `where current of g1` which I would expect to give `ORA-01410: invalid ROWID`. In your second example I am not clear why you switched from a Cursor FOR loop to the more verbose and less efficient OPEN-FETCH-EXIT-CLOSE syntax. — William Robertson, Aug 21 '18 at 10:33
To be honest, I didn't found out how to join with the first example and the second one worked perfectly. I know Tom Kyte have an opinion on this topic, he makes it very clear. I wouldn't use it in an ETL-Job, but for a good and well tested onetime Update it's very helpful. — Mirjam, Aug 21 '18 at 11:23
@WilliamRobertson is correct.. `for update` and `current of` should be on same table.. — Nikhil Misal, Aug 21 '18 at 12:51

hotfix · Answer 1 · 2018-08-21T11:17:49.803

Exit must be after fetch, if the cursor has no data, the loop will beterminated.

At the end of the loop, you can then query your commit as follows. So your loop can look like this

LOOP 
  FETCH cur
   INTO l_r_id, l_r_name;
  EXIT WHEN cur%notfound;

  UPDATE test
     set name = l_r_name
   where test.id_customer = l_r_id;
  i := i+1;
  if mod(i,50000) = 0 then
    commit;
  end if;

END LOOP;

Another suggestion would be to define a collection of records. Fill the collection with the data from the table test_new. then to iterate over this collection and make an update.

without testing it, the solution could look like this.

DECLARE

  TYPE tabTest IS TABLE OF test_new%ROWTYPE;
  t_test tabTest;
  i NUMBER := 0;

BEGIN
  select tnw.* 
    bulk collect into t_test
    from test_new tnw
       , test tes
    where tnw.id_customer = tes.id_customer

  for indx  in 1 .. t_test.count()
  loop
    UPDATE test
       set name = t_test(indx).name
     where test.id_customer = t_test(indx).id;

    i := i+1;
    if mod(i,50000) = 0 then
      commit;
    end if;

  end loop;
  commit;
END;

The first example get to the same error: 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error." — Mirjam, Aug 21 '18 at 13:50
But with the second and some changes it works. t_test.count() doesn't work, but I set a variable with a count. — Mirjam, Aug 21 '18 at 13:51

score 0 · Answer 2 · answered Aug 21 '18 at 13:54

My solution with the help of @hotfix:

DECLARE
  TYPE tabTest IS TABLE OF test_new%ROWTYPE;
       t_test tabTest;
  testcount number;
  i NUMBER := 0;
BEGIN
  select count(*) into testcount from test_new;

  select tnw.* 
    bulk collect into t_test
    from test_new tnw
       , test tes
    where tnw.id_customer = tes.id_customer;  

  FOR cur IN 1..testcount LOOP
      UPDATE test 
        set name = t_test(cur).name
        where test.id_customer = t_test(cur).id_customer;

      i := i + 1;              -- Commits after every X number of records

      IF i > 50000 THEN
         COMMIT;
         i := 0;
      END IF;

  END LOOP;
  COMMIT;
END;

Oracle SQL CURSOR Update with Join and Commit

2 Answers2