PL/SQL "WHERE CURRENT OF" vs "ROWID"

Question

Why Oracle made "where current of" syntax when you can use "rowid"? Example:

BEGIN
  FOR rec IN (SELECT t.column1, t.rowid rid FROM test_table) LOOP
    UPDATE test_table tb SET column1 = some_function(rec.column1) WHERE tb.rowid = rec.rid;
  END LOOP;
  COMMIT;
END;

DECLARE 
  CURSOR cur IS SELECT t.column1 FROM test_table;
  param1 test_table.column1%TYPE;
BEGIN
  LOOP
    FETCH cur INTO param1;
    UPDATE test_table tb SET tb.column1 = some_function(param1) WHERE CURRENT OF cur;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  COMMIT;
END;

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session. — Arkadiusz Łukasiewicz, Apr 25 '16 at 13:20

score 0 · Accepted Answer · answered Apr 25 '16 at 07:42

0

Where Current Of is used to identify LAST FETCHED ROW in cursor. It's more safe, because You have 100% confidence, that f.e. You updating LAST FETCHED ROW from curosr. With Rowids there's danger, because it's really easy to mess up something.

answered Apr 25 '16 at 07:42

Michał M

618
5
13

And what you can say about cost? – PolyMorph Apr 25 '16 at 07:48
LAST FETCHED ROW - so cost SUPPOSED to be 1 (Don't wanna give bad intel :) ) ROWID - I explain on libarary example. If You want to find a book without ROWID (address), You go through all books begining from 1st untill You find what You want. WITH rowid You have info, where to go, which alley, which shelf and position so You just skip all the search and go to address. In that case it's also SUPPOSED to be cost 1. But it depends very much on your code. – Michał M Apr 25 '16 at 09:08

score 0 · Answer 2 · answered Jul 12 '19 at 05:14

Using WHERE CURRENT OF clause without having FOR UPDATE clause mentioned in your SELECT statement could be risky. Reason behind that is when you are not applying FOR UPDATE clause then you are not exclusively placing Row level lock to those rows which you are intending to update in the following UPDATE DML. And hence it opens an opportunity for outside world of violating data consistency i.e. some other user from different session may be looking to UPDATE same rows of your targeted table. Also, in you learn more about WHERE CURRENT OF clause you will notice that during this clause Oracle internally makes use of ROWID's only to reach/identify the rows which needs to be updated. Hope it helps !! Happy Programming

PL/SQL "WHERE CURRENT OF" vs "ROWID"

2 Answers2