WHERE CURRENT OF STATEMENT

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature. Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

EXAMPLE

UPDATING USING THE WHERE CURRENT OF STATEMENT.

DECLARE
CURSOR C1 IS SELECT empno,ename, sal
from emp
where comm IS NULL
FOR UPDATE OF comm;

var_comm NUMBER (10,2);
BEGIN
FOR rec in C1 LOOP 
  IF rec.sal < 500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 1500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 3000 THEN
      var_comm :=rec.sal*0.15;
    ELSE
   var_comm :=  rec.sal*0.12;
  END IF;
  
 UPDATE emp 
 SET comm =var_comm
 WHERE CURRENT OF C1;
 END LOOP;
 END;


SYNTAX

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;


NOTE

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

DELETING USING THE WHERE CURRENT OF STATEMENT

--DROP TABLE EMP;
--CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

DECLARE
   v_empno   emp.empno%TYPE;
   v_ename   emp.ename%TYPE;
   v_sal     emp.sal%TYPE;

   CURSOR c1
   IS
          SELECT empno, ename, sal
            FROM emp
           WHERE comm IS NULL
          FOR UPDATE OF comm;

BEGIN
  OPEN C1;

   LOOP
      FETCH C1  INTO v_empno, v_ename, v_sal;

      EXIT WHEN c1%NOTFOUND;

      DELETE FROM emp
      WHERE CURRENT OF C1;

      --UPDATE emp
      --SET comm = 9999
         END LOOP;
    COMMIT;
   CLOSE C1;
END;

FOR UPDATE OF CLAUSE

Learn how to use the Oracle/PLSQL SELECT FOR UPDATE statement with syntax and examples.

DESCRIPTION

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

SYNTAX

The syntax for the SELECT FOR UPDATE statement in Oracle/PLSQL is:

CURSOR cursor_name

IS

   select_statement

   FOR UPDATE [OF column_list] [NOWAIT];

PARAMETERS OR ARGUMENTS

cursor_name is the name of the cursor.

select_statement is a SELECT statement that will populate your cursor result set.

column_list are the columns in the cursor result set that you wish to update.

NOWAIT is optional. The cursor does not wait for resources.

EXAMPLE

For example, you could use the SELECT FOR UPDATE statement as follows:

CURSOR c1

IS

  SELECT course_number, instructor

  FROM courses_tbl

  FOR UPDATE OF instructor;

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

Finding orphan sessions in oracle

It happened  when one of the  job get killed after reaching its max time, but it's thread processes are running, so we need a monitoring sql to check such process who does not have any parent. 

When joining v$session to v$process  the v$process.spid column should have a value (node process ID).  If this is null, that is generally an orphaned session.

--orphan_session.sql

col username heading 'USERNAME' format a10
col sessions heading 'SESSIONS'
col sid heading 'SID' format 999
col status heading 'STATUS' format a10
col machine format a10 head 'MACHINE' 
col client_program format a20 head 'CLIENT PROGRAM'
col server_program format a20 head 'SERVER PROGRAM'
col spid format a5 head 'SRVR|PID'
col serial# format 99999 head 'SERIAL#' 
col client_process format 999999 head 'CLIENT|PID'
col osuser format a7
col logon_time format a17 head 'LOGON TIME'
col idle_time format a11 head 'IDLE TIME'
col ppid format 999 head 'PID' 

set recsep off term on pause off verify off echo off
set line 200
set trimspool on

clear break
break on username skip 1

select
        s.username,
        s.sid,
        s.serial#,
        p.pid ppid,
        s.status,
        s.machine,
        s.osuser,
        substr(s.program,1,20) client_program,
        s.process client_process,
        substr(p.program,1,20) server_program,
        p.spid spid,
        to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
        -- idle time
        -- days added to hours
        --( trunc(LAST_CALL_ET/86400) * 24 ) || ':'  ||
        -- days separately
        substr('0'||trunc(LAST_CALL_ET/86400),-2,2)  || ':'  ||
        -- hours
        substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
        -- minutes
        substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) ||
':' ||
        --seconds
        substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2)
idle_time 
from v$session s, v$process p
where s.username is not null
        -- use outer join to show sniped sessions in
        -- v$session that don't have an OS process
        and p.addr(+) = s.paddr
        -- uncomment to see only your own session 
        --and userenv('SESSIONID') = s.audsid
order by username, sid
/

Cheers
Rajani