Home » SQL & PL/SQL » SQL & PL/SQL » Returning Old value during update (Oracle 10g)
| 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
|
|
|
|
|
|
| Re: Returning Old value during update [message #537903 is a reply to message #537868] |
Mon, 02 January 2012 13:56  |
 |
Barbara Boehmer
Messages: 9105
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.
|
|
|
|
| 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 #538005 is a reply to message #537920] |
Tue, 03 January 2012 07:57 |
|
Barbara Boehmer
Messages: 9105
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.
|
|
|
|
|
|
| Re: Returning Old value during update [message #538028 is a reply to message #538005] |
Tue, 03 January 2012 11:31 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Tue, 03 January 2012 08:57I 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.
|
|
|
|
| Re: Returning Old value during update [message #538041 is a reply to message #538028] |
Tue, 03 January 2012 12:26 |
|
Barbara Boehmer
Messages: 9105
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.
|
|
|
|
| Re: Returning Old value during update [message #538067 is a reply to message #538041] |
Tue, 03 January 2012 14:00 |
Solomon Yakobson
Messages: 3305
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.
|
|
|
|
| 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;
/
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: 13966
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.
|
|
|
|
| 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;
/
[Updated on: Tue, 09 May 2017 10:39] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Apr 21 15:11:02 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
