CODEWITHSUNDEEP
oracle-databaseplsqlrowid
PolyMorph
PolyMorph

Reputation: 83

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

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;

Upvotes: 2

Views: 4215

Answers (2)

Krishna Kant Shukla
Krishna Kant Shukla

Reputation: 1

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

Upvotes: 0

Michał M
Michał M

Reputation: 618

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.

Upvotes: 0

Related Questions