|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
December 31, 2006: On finding changed values in a table with flashback
The as of clause in a select statement allows to see the data as of a past SCN or past point in time. So, it's possible, for example, to compare the current data in a table with a previous data set. This is what I try to do.
The following table is needed for this simple experiment:
create table flashback_ex ( id number not null, txt varchar2(7) );
For a reason I don't really understand, the as of clause cannot be used on a table within the first five minutes after its creation. Otherwise, Oracle reports an ORA-01466 unable to read data - table definition has changed. I want to prevent that:
Inserting some records:
insert into flashback_ex values (1,'one' ); insert into flashback_ex values (2,'two' ); insert into flashback_ex values (3,'three'); insert into flashback_ex values (4,'four' ); insert into flashback_ex values (5,'five' ); insert into flashback_ex values (6,'six' ); insert into flashback_ex values (7,'seven'); insert into flashback_ex values (8,'eight'); insert into flashback_ex values (9,'nine' ); commit;
A bind variable is created ...
variable v_scn number
... and assigned the current SCN. (The value of this variable could now be printed with
A few records are updated:
insert into flashback_ex values (10, 'ten' ); insert into flashback_ex values (20, 'twenty'); update flashback_ex set id = 40, txt='fourty' where id = 4; update flashback_ex set id = 50, txt='fifty' where id = 5; delete from flashback_ex where id = 7; delete from flashback_ex where id = 8; commit;
In order to find changed values in the table, I full join the values as of now with the values that were valid at the SCN stored in the variable :v_scn.
The where conditions makes sure only records are returned that have changed.
set numf 99999 select case when prv_id is null then 'deleted' when cur_id is null then 'inserted' else 'updated' end operation, prv_id, cur_id, prv_txt, cur_txt from ( select rowid r, id prv_id, txt prv_txt from flashback_ex as of scn :v_scn ) full join ( select rowid r, id cur_id, txt cur_txt from flashback_ex ) using (r) where prv_id != cur_id or prv_txt != cur_txt or prv_id is null or cur_id is null;
OPERATIO PRV_ID CUR_ID PRV_TX CUR_TX -------- ------ ------ ------ ------ updated 4 40 four fourty updated 5 50 five fifty inserted 8 eight inserted 7 seven deleted 20 twenty deleted 10 ten
More on Oracle
This is an on Oracle article. The most current articles of this series can be found here.