Oracle – Quick Tip – Replace Explicit Cursors With FOR LOOP In PL/SQL


If your cursor logic has a “FOR UPDATE” clause, please leave it in place as it locks the records in the Resultset for the update (until next COMMIT or ROLLBACK) and you will open up your code for unnecessary race conditions by replacing that with FOR LOOP (like two people reserving the same seat on an airplane!).

I hate don’t like OPEN/FETCH/CLOSE

Back in the day, the most commonly used method to loop through and process “a small number of records” in PL/SQL was to use the WHILE/OPEN/FETCH/CLOSE explicit cursor operations.

Not only is the syntax cryptic and cumbersome, it can also lead to errors if you miss something really simple inside your loop.

The easier way to do this is using implicit cursors. By the way, this is a fully working anonymous block.

    FOR v_Rec IN (SELECT * FROM My_Table) LOOP

Here are a few things to note above

  • V_REC variable is not even declared. PL/SQL implicitly declared it for
  • There is no explicit CURSOR definition. Inline SQL was used
  • The looping logic is as straightforward as it can get.

Please note that this mechanism is good only when you want to loop through a small number of records. There are other ways to do the same if you will work with a large number of records like using set based SQL statements and by using BULK operations.


One thought on “Oracle – Quick Tip – Replace Explicit Cursors With FOR LOOP In PL/SQL

Leave a Reply

Fill in your details below or click an icon to log in: 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 )

Google+ photo

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

Connecting to %s