WHERE CURRENT OF in oracle plsql

WHERE CURRENT OF Clause inside the cursor
======================================================

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data.

The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you do not have to code in two (or more) places the criteria used to uniquely identify a row in a table. Without WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATEs and DELETEs. As a result, if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. If you use WHERE CURRENT OF, on the other hand, you only have to modify the WHERE clause of the SELECT statement.

This might seem like a relatively minor issue, but it is one of many areas in your code where you can leverage subtle features in PL/SQL to minimize code redundancies. Utilization of WHERE CURRENT OF, %TYPE, and %ROWTYPE declaration attributes, cursor FOR loops, local modularization, and other PL/SQL language constructs can have a big impact on reducing the pain you may experience when you maintain your Oracle-based applications.

FOR UPDATE NOWAIT and ORA-00054: Resource Busy and Acquire with NOWAIT Specified

FOR UPDATE IN SELECT STATEMENT
==============================
IF you use FOR UPDATE in a select statement, the selected rows will Busy and Acquire lock until any commit or rollback execute.

In this time if another session try to any DML in those selected rows, they will face ora-00054 error. description are written in the following lines.


When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is a wonderful feature because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records which have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the "before image" of the data).

There are times, however, when you will want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking.

When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records "for your changes only" as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.

You can use the FOR UPDATE clause in a SELECT against multiple tables. In this case, rows in a table are locked only if the FOR UPDATE clause references a column in that table.

The OF list of the FOR UPDATE clause does not restrict you to changing only those columns listed. Locks are still placed on all rows; the OF list just gives you a way to document more clearly what you intend to change. If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, then the database will then lock all identified rows across all tables listed in the FROM clause.

Furthermore, you do not have to actually UPDATE or DELETE any records just because you issued a SELECT...FOR UPDATE -- that act simply states your intention to be able to do so.

Finally, you can append the optional keyword NOWAIT to the FOR UPDATE clause to tell Oracle not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program so that you can perform other work or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available. There is no limit to the wait time unless the table is remote. For remote objects, the Oracle initialization parameter, DISTRIBUTED_LOCK_TIMEOUT (distributed/remote situation/use of dblink), is used to set the limit.


example:=

session one
===========

SQL>
SQL> select * from dept
2 where deptno =10
3 for update nowait ;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

SQL>



session-2
============================

SQL> set time on
18:02:06 SQL>
18:02:07 SQL>
18:02:07 SQL> set timing on
18:02:25 SQL>
18:02:25 SQL> select * from dept
18:02:30 2 where deptno =10
18:02:30 3 for update wait 5
18:02:33 4 /
select * from dept
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired


Elapsed: 00:00:05.10
18:02:40 SQL>
18:02:42 SQL>
18:02:42 SQL> ed
Wrote file afiedt.buf

1 select * from dept
2 where deptno =10
3* for update skip locked
18:03:00 SQL> /

no rows selected

Elapsed: 00:00:00.06
18:03:02 SQL>
18:03:04 SQL>
18:03:04 SQL> ed
Wrote file afiedt.buf

1 select * from dept
2 where deptno =10
3* for update nowait
18:03:21 SQL> /
select * from dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


Elapsed: 00:00:00.10
18:03:23 SQL>
18:03:24 SQL>
18:03:24 SQL> ed
Wrote file afiedt.buf

1 select * from dept
2 where deptno =10
3* FOR UPDATE NOWAIT SKIP LOCKED
18:05:33 SQL> /

no rows selected

Elapsed: 00:00:00.04
18:05:35 SQL>
18:05:36 SQL>
18:05:36 SQL>



By default, the transaction waits until the requested row lock is acquired. If you are not willing to wait to acquire the row lock, use either the NOWAIT clause of the LOCK TABLE statement (see Choosing a Locking Strategy) or the SKIP LOCKED clause of the SELECT FOR UPDATE statement.

If you can lock some of the requested rows, but not all of them, the SKIP LOCKED option skips the rows that you cannot lock and locks the rows that you can lock.

SELECT
FROM
FOR UPDATE NOWAIT SKIP LOCKED;