OraFAQ Forum: SQL & PL/SQL » Returning Old value during update

Home » SQL & PL/SQL » SQL & PL/SQL » Returning Old value during update (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Returning Old value during update [message #537867]

Mon, 02 January 2012 04:31

mani_rbs
Messages: 11
Registered: January 2011
Location: Chennai

Junior Member

Hi,
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.

Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.

Thanks & Regards,
Manikandan V

Report message to a moderator

Re: Returning Old value during update [message #537868 is a reply to message #537867]

Mon, 02 January 2012 04:36

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

AFAIK, there is no way to get the old values on an UPDATE.
You have to do it by yourself with a trigger that sets a package or context variable.

Regards
Michel

Report message to a moderator

Re: Returning Old value during update [message #537903 is a reply to message #537868]

Mon, 02 January 2012 13:56

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

I can think of two ways to do this. One method would be to loop through a cursor, storing and updating one row at a time. Another method would be to do a delete of the rows to be updated, storing them bulk collect, then do an insert using forall to accomplish the update. If you have a large amount of data, then the second method should be faster. I have demonstrated both methods below. I have looped through the variables to display them, but you could put them in an out parameter or store them to a table or whatever you want.

-- loop through cursor, storing and updating one row at a time:

-- procedure:
SCOTT@orcl_11gR2> create or replace procedure update_proc
  2  as
  3    cursor c_test is
  4    select *
  5    from   test_tab
  6    where  deptno <= 30
  7    for    update;
  8    v_old_row  test_tab%rowtype;
  9    type	  t_old_tab is table of test_tab%rowtype;
 10    v_old_tab  t_old_tab := t_old_tab();
 11  begin
 12    -- update looping through cursor one row at a time:
 13    open c_test;
 14    loop
 15  	 fetch c_test into v_old_row;
 16  	 exit when c_test%notfound;
 17  	 v_old_tab.extend;
 18  	 v_old_tab(v_old_tab.last) := v_old_row;
 19  	 update test_tab
 20  	 set	deptno = deptno + 1,
 21  		dname = 'NEW ' || dname,
 22  		loc = 'NEW ' || loc
 23  	 where	current of c_test;
 24    end loop;
 25    -- display old values:
 26    dbms_output.put_line ('-----------------');
 27    for i in 1 .. v_old_tab.count loop
 28  	 dbms_output.put_line ('deptno:  ' || v_old_tab(i).deptno);
 29  	 dbms_output.put_line ('dname:	 ' || v_old_tab(i).dname);
 30  	 dbms_output.put_line ('loc:	 ' || v_old_tab(i).loc);
 31  	 dbms_output.put_line ('-----------------');
 32    end loop;
 33  end update_proc;
 34  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
-- values before udpate:
SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

-- update and display of old values:
SCOTT@orcl_11gR2> exec update_proc
-----------------
deptno:  10
dname:   ACCOUNTING
loc:     NEW YORK
-----------------
deptno:  20
dname:   RESEARCH
loc:     DALLAS
-----------------
deptno:  30
dname:   SALES
loc:     CHICAGO
-----------------

PL/SQL procedure successfully completed.

-- values after update:
SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        11 NEW ACCOUNTING NEW NEW YORK
        21 NEW RESEARCH   NEW DALLAS
        31 NEW SALES      NEW CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SCOTT@orcl_11gR2> rollback
  2  /

Rollback complete.

-- delete using bulk collect to store old values, then insert using forall:

-- procedure:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE update_proc
  2  AS
  3    TYPE t_deptno_tab IS TABLE OF test_tab.deptno%TYPE;
  4    v_deptno_tab	    t_deptno_tab;
  5    TYPE t_dname_tab  IS TABLE OF test_tab.dname%TYPE;
  6    v_dname_tab	    t_dname_tab;
  7    TYPE t_loc_tab  IS TABLE OF test_tab.loc%TYPE;
  8    v_loc_tab	    t_loc_tab;
  9  BEGIN
 10    -- delete, storing old values using bulk collect:
 11    DELETE FROM test_tab
 12    WHERE  deptno <= 30
 13    RETURNING deptno, dname, loc
 14    BULK COLLECT INTO v_deptno_tab, v_dname_tab, v_loc_tab;
 15    -- insert using forall:
 16    FORALL i IN 1 .. v_deptno_tab.COUNT
 17  	 INSERT INTO test_tab (deptno, dname, loc)
 18  	 VALUES
 19  	   (v_deptno_tab(i) + 1,
 20  	    'NEW ' || v_dname_tab(i),
 21  	    'NEW ' || v_loc_tab(i));
 22    -- display old values:
 23    dbms_output.put_line ('-----------------');
 24    for i in 1 .. v_deptno_tab.count loop
 25  	 dbms_output.put_line ('deptno:  ' || v_deptno_tab(i));
 26  	 dbms_output.put_line ('dname:	 ' || v_dname_tab(i));
 27  	 dbms_output.put_line ('loc:	 ' || v_loc_tab(i));
 28  	 dbms_output.put_line ('-----------------');
 29    end loop;
 30  END update_proc;
 31  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> show errors
No errors.
-- values before udpate:
SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

-- update and display of old values:
SCOTT@orcl_11gR2> exec update_proc
-----------------
deptno:  10
dname:   ACCOUNTING
loc:     NEW YORK
-----------------
deptno:  20
dname:   RESEARCH
loc:     DALLAS
-----------------
deptno:  30
dname:   SALES
loc:     CHICAGO
-----------------

PL/SQL procedure successfully completed.

-- values after update:
SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        11 NEW ACCOUNTING NEW NEW YORK
        21 NEW RESEARCH   NEW DALLAS
        31 NEW SALES      NEW CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

Report message to a moderator

Re: Returning Old value during update [message #537919 is a reply to message #537903]

Tue, 03 January 2012 00:40

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Btw, the second option can be simplified in Oracle 11g by using the single collection as given below.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> create or replace
  2  PROCEDURE update_proc
  3  AS
  4      TYPE T_DEPT_TAB IS TABLE OF DEPT%ROWTYPE;
  5      v_dept_tab         t_dept_tab;
  6  BEGIN
  7     -- delete, storing old values using bulk collect:
  8      DELETE FROM DEPT D
  9      WHERE  deptno <= 30
 10      RETURNING deptno,dname,loc
 11      BULK COLLECT INTO v_dept_tab;
 12      -- insert using forall:
 13      FORALL I IN 1 .. v_dept_tab.COUNT
 14      INSERT INTO DEPT (deptno, dname, loc)
 15      VALUES
 16        (v_dept_tab(I).deptno + 1,
 17         'NEW ' || v_dept_tab(I).dname,
 18         'NEW ' || v_dept_tab(i).loc);
 19      -- display old values:
 20      DBMS_OUTPUT.PUT_LINE ('-----------------');
 21      FOR I IN 1 .. V_DEPT_TAB.COUNT LOOP
 22      DBMS_OUTPUT.PUT_LINE ('deptno:  ' || V_DEPT_TAB(I).deptno);
 23      DBMS_OUTPUT.PUT_LINE ('dname:   ' || V_DEPT_TAB(I).dname);
 24      dbms_output.put_line ('loc:     ' || v_dept_tab(i).loc);
 25      dbms_output.put_line ('-----------------');
 26      end loop;
 27  END UPDATE_PROC;
 28  /

Procedure created.

SQL> EXEC update_proc;
-----------------
deptno:  10
dname:   ACCOUNTING
loc:     NEW YORK
-----------------
deptno:  20
dname:   RESEARCH
loc:     DALLAS
-----------------
deptno:  30
dname:   SALES
loc:     CHICAGO
-----------------

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        11 NEW ACCOUNTING NEW NEW YORK
        21 NEW RESEARCH   NEW DALLAS
        31 NEW SALES      NEW CHICAGO

SQL> rollback;

Rollback complete.

SQL>

[Updated on: Tue, 03 January 2012 00:42]

Report message to a moderator

Re: Returning Old value during update [message #537920 is a reply to message #537919]

Tue, 03 January 2012 00:56

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Some comments:
1/ OP is in 10g
2/ It is not a solution on UPDATE statement
3/ Between DELETE and INSERT someone may have insert the same row and so you may not be able to insert yours (so application inconsistencies)
4/ I think the purpose to get the old values is to handle them in a program and so the dbms_output solution may not be the most appropriate one
5/ If OP is not allowed to create a trigger, I doubt it is allowed to create a procedure

Now it depends on the actual and exact need of OP and above all in which context (program, script...) it has to handle this issue.

Regards
Michel

Report message to a moderator

Re: Returning Old value during update [message #538005 is a reply to message #537920]

Tue, 03 January 2012 07:57

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Michel Cadot wrote on Mon, 02 January 2012 22:56

1/ OP is in 10g

I believe both my solutions work in 10g.

Michel Cadot wrote on Mon, 02 January 2012 22:56

2/ It is not a solution on UPDATE statement

My first solution does an update. The second one does a delete and insert, which accomplishes an update.

Michel Cadot wrote on Mon, 02 January 2012 22:56

3/ Between DELETE and INSERT someone may have insert the same row and so you may not be able to insert yours (so application inconsistencies)

True. You might need to add some locking, as in first solution.

Michel Cadot wrote on Mon, 02 January 2012 22:56

4/ I think the purpose to get the old values is to handle them in a program and so the dbms_output solution may not be the most appropriate one

As I said, it could be handled in many other ways. I did not know what the original poster plans to do with the variable, so I just used dbms_output to show what was stored in the variable.

Michel Cadot wrote on Mon, 02 January 2012 22:56

5/ If OP is not allowed to create a trigger, I doubt it is allowed to create a procedure

Now it depends on the actual and exact need of OP and above all in which context (program, script...) it has to handle this issue.

The original poster's post began with, "In a pl/sql procedure,", so obviously he is using a procedure.

Report message to a moderator

Re: Returning Old value during update [message #538015 is a reply to message #538005]

Tue, 03 January 2012 08:39

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I believe both my solutions work in 10g

This was an answer to "the second option can be simplified in Oracle 11g".

Quote:

You might need to add some locking, as in first solution.

How do you lock a deleted row? You have to lock the whole table.

Solution(s) depends on what OP can or cannot do and want to do in the end. I'm waiting for this information...

Regards
Michel

Report message to a moderator

Re: Returning Old value during update [message #538028 is a reply to message #538005]

Tue, 03 January 2012 11:31

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara Boehmer wrote on Tue, 03 January 2012 08:57

I believe both my solutions work in 10g.
My first solution does an update. The second one does a delete and insert, which accomplishes an update.

Hi Barbara,

Neither solution is equivalent to UPDATE. FOR CURSOR LOOP + UPDATE of one row at a time != single UPDATE. They can produce different results by definition. Plus possible triggers: statement level triggers will be executed multiple times. And your second solution will call DELETE/INSERT triggers while single UPDATE will call UPDATE triggers. But again, main reason is FOR CURSOR LOOP + UPDATE of one row at a time != single UPDATE.

SY.

Report message to a moderator

Re: Returning Old value during update [message #538041 is a reply to message #538028]

Tue, 03 January 2012 12:26

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

syakobson wrote on Tue, 03 January 2012 09:31

...FOR CURSOR LOOP + UPDATE of one row at a time != single UPDATE. They can produce different results by definition....

Bearing in mind that I used "cursor c_test ... where deptno <= 30 for update" and "update ... where current of c_test", how can that produce different results than a single update using, "update ... where deptno <= 30"? In other words, how can the following two return different results?

-- cursor for update with update where current of:

SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SCOTT@orcl_11gR2> declare
  2    cursor c_test is
  3    select *
  4    from   test_tab
  5    where  deptno <= 30
  6    for    update;
  7  begin
  8    for r_test in c_test loop
  9  	 exit when c_test%notfound;
 10  	 update test_tab
 11  	 set	deptno = deptno + 1,
 12  		dname = 'NEW ' || dname,
 13  		loc = 'NEW ' || loc
 14  	 where	current of c_test;
 15    end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        11 NEW ACCOUNTING NEW NEW YORK
        21 NEW RESEARCH   NEW DALLAS
        31 NEW SALES      NEW CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

-- single update:

SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SCOTT@orcl_11gR2> update test_tab
  2  set    deptno = deptno + 1,
  3  	    dname = 'NEW ' || dname,
  4  	    loc = 'NEW ' || loc
  5  where  deptno <= 30
  6  /

3 rows updated.

SCOTT@orcl_11gR2> select * from test_tab order by deptno
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        11 NEW ACCOUNTING NEW NEW YORK
        21 NEW RESEARCH   NEW DALLAS
        31 NEW SALES      NEW CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

Report message to a moderator

Re: Returning Old value during update [message #538067 is a reply to message #538041]

Tue, 03 January 2012 14:00

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara Boehmer wrote on Tue, 03 January 2012 13:26

Bearing in mind that I used for update" and "update ... where current of

Sorry Barbara,

I missed FOR UPDATE. Yes, using FOR UPDATE will cause SELECT to mini-rollback if needed.

SY.

Report message to a moderator

Re: Returning Old value during update [message #662677 is a reply to message #537867]

Tue, 09 May 2017 08:42

Olivier Comte
Messages: 2
Registered: May 2017

Junior Member

I have found a tricky weird way to do it but I don't think it is very reliable.

create table TOTO(A integer, B integer);

declare
    type v_A_t is table of TOTO.A%type;
    type v_B_t is table of TOTO.B%type;
    v_A v_A_t;
    v_B v_B_t;
begin
    insert into TOTO values (2, 3);
    insert into TOTO values (1, 2);
    insert into TOTO values (3, 4);
    update
        (select TOTO.*, A as dup_A from TOTO)
        set dup_A = A
        , A = 10*A  -- new value is computed and set here.
        returning A, B bulk collect into v_A, v_B;  -- Here, it seems that Oracle returns the first update value instead of the final one!
    for i in v_A.first .. v_A.last
    loop
        dbms_output.put_line('Old value of TOTO.A was ' || to_char(v_A(i)) || ' for B=' || to_char(v_B(i)));
    end loop;
end;
/

My Oracle version is 12c. I have not tested this with other versions yet.

Regards,
*BlackSwan added {code} tags. Please do so yourself in the future
How to use {code} tags and make your code easier to read

[Updated on: Tue, 09 May 2017 08:55] by Moderator

Report message to a moderator

Re: Returning Old value during update [message #662683 is a reply to message #662677]

Tue, 09 May 2017 09:55

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the returning clause doesn't return the current value of the column then that's a bug.
The way you've gone about making it happen means it's an obscure bug, but it's still a bug.
If you write code that relies on a bug then you've basically stopped yourself from ever upgrading your DB, cause at some point oracle might just fix the bug.

Report message to a moderator

Re: Returning Old value during update [message #662688 is a reply to message #537867]

Tue, 09 May 2017 10:37

Olivier Comte
Messages: 2
Registered: May 2017

Junior Member

OK, you are right. But, I have a far better way to do the same. And I think, this time it is not a bug:

declare
    type v_A_t is table of TOTO.A%type;
    v_A     v_A_t;
    v_OLD_A v_A_t;
begin
    insert into TOTO values (2, 3);
    insert into TOTO values (1, 2);
    insert into TOTO values (3, 4);
    update
        (select TOTO.*
             , (select TOTO.A from dual) as OLD_A
             from TOTO
        )
        set A = 10*A
        returning A, OLD_A bulk collect into v_A, v_OLD_A;
    for i in v_A.first .. v_A.last
    loop
        dbms_output.put_line('Old is ' || to_char(v_OLD_A(i)) || ', new is ' || to_char(v_A(i)));
    end loop;
end;
/

Nice and simple, isn't it?

[Updated on: Tue, 09 May 2017 10:39]

Report message to a moderator

Previous Topic:	Outer Join on Table
Next Topic:	Produce output not having any or all inputs (products)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 03:55:59 CDT 2024