Is there more than one reason to use PL/SQL WHERE CURRENT OF ?

I can’t remember using the PL/SQL construct where current of  in code I have written so along with my usual article style of how use to use this feature, this is also an experiment to find out if there are any other reasons to use where current of.

The code examples in this post have been developed using the Oracle Database App Development VM for Oracle VirtualBox which at the time of this post was using Oracle database (Although nothing in this post is 12c specific)

First I create a table that can be used for the experiment

create table t
select *
from all_objects

With the table created, I then use it in the following example which I will explain below.  At this point I would like to say that I appreciate the example could be rewritten as a single SQL statement however I wanted to concentrate on illustrating the use of where current of  without an overtly complicated example where the point of this post is lost in a forest of code.


  cursor c 
  select t.object_name
    from t
   where t.object_type in ('PACKAGE', 'PACKAGE BODY')
     and t.owner = 'APEX_040200'
     for update of object_name; 

  ln_count pls_integer := 1;


  for i in c
    update t
       set t.object_name = 'X - ' || ln_count
     where current of c;
    ln_count := ln_count + 1;
  end loop;



At line 3 I declare a cursor that identifies the records of interest, which are the package specifications and bodies owned by the APEX user.  Note the use of the for update clause. This is mandatory when using where current of . If you remove it and try to run the example you will see the following error message

PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF

Moving on to the execution section,  for each iteration of the cursor for loop,  the object name is updated (to something not very useful!) and you can see at line 20 what I think is the primary benefit of using where current of is that instead of repeating the where clause used by the cursor I have used where current of c. So when the cursor has to change to return different records the only change that needs to be made is to the cursor.

The final part of the example is once the loop completes I release the lock on the table by committing the changes.

The primary benefit of where current of is the reduction in duplicated code which is a good thing but am I missing anything else? Please use the comments to let me know.


In this post I have demonstrated how to use the PL/SQL construct where current of along with what I believe is it’s primary benefit, the reduction of duplicated code.


The inspiration for this post came from Steven Feuerstein’s book, Oracle PL/SQL Programming  5th Edition.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.