Accesing the new values of the column in an update statement

[srinivast]

Hi,

Is there a way to access the new value of an updated column in the same update statement, just like in triggers, which allows us with the :new and ld references.

Srinivas.

[jmodic]

Use RETURNING clause of the UPDATE ststement. For example:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
With the Partitioning option
JServer Release 8.1.7.1.1 - Production

SQL> set serveroutput on
SQL> DECLARE
  2    v_sal NUMBER;
  3  BEGIN
  4    SELECT sal INTO v_sal FROM emp WHERE ename = 'KING'; 
  5    dbms_output.put_line('Salary before the update: ' || v_sal);
  6    UPDATE emp SET sal = sal*2 WHERE ename = 'KING'
  7      RETURNING sal INTO v_sal;
  8    dbms_output.put_line('Salary after the update: ' || v_sal);
  9  END;
 10  /
Salary before the update: 8450
Salary after the update: 16900

PL/SQL procedure successfully completed.

[srinivast]

Thanks jurij