Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Mladen Gogala <gogala.mladen@gmail.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: how to select column outside of transaction
Date: Mon, 10 Feb 2014 14:24:51 +0000 (UTC)
Organization: A noiseless patient Spider
Lines: 65
Message-ID: <pan.2014.02.10.14.24.51@gmail.com>
References: <xn0iy3juc3z3clv000@news.aioe.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 10 Feb 2014 14:24:51 +0000 (UTC)
Injection-Info: mx05.eternal-september.org; posting-host="9329a606497bb546bacd1416e46e9713"; logging-data="15695"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18i92Ik3mnfTdaGSCiWIbfo"
User-Agent: Pan/0.139 (Sexual Chocolate; GIT bf56508 git://git.gnome.org/pan2)
Cancel-Lock: sha1:dqmgPji9XbSFFHm3HuLYCn9YP54=
Xref: csiph.com comp.databases.oracle.misc:804 comp.databases.oracle.server:5186

On Sun, 09 Feb 2014 22:09:48 +0000, remove ps wrote:

> My code does begin transaction, insert/delete/update parent row,
> insert/delete/update child rows.  In the trigger for the child rows I
> find I want to get the old value of a column in the parent row.  I have
> 3 solutions:
> 
> 
> (1) Create a trigger for the parent table, and when insert/update/delete
> then store the old value into a SYS_CONTEXT as in
> 
> create or replace trigger parent_general after delete or insert or
> update on parent for each row begin
>   old_values.set('parent.column', :old.id || :old.column);
> end;
> 
> where
> 
> create or replace package body old_values is
>   procedure set(s_name in varchar2, s_value in varchar2) is begin
>     dbms_session.set_context('old_values', s_name, s_value);
>   end;
> end;
> 
> create context old_values using old_values;
> 
> Then in the trigger on the child table we can use SYS_CONTEXT to get the
> old values.  But after the transaction is finished, the old values are
> still there.
> 
> 
> (2) Create a trigger for the parent table, and when insert/update/delete
> then store the old value into a global temporary table.
> 
> create global temporary table OldValues (s_table_name varchar2(30), id
> integer, s_value varchar2(100)) on commit delete rows;
> 
> The trigger is similar to the above, except instead of calling
> dbms_session.set_context we call insert into OldValues.  The nice thing
> is that when the transaction is finished then the rows from the temp
> table are deleted.
> 
> 
> (3) Somehow I came across the flashback feature of Oracle.
> 
> declare
>   oldColumnValue prevscn integer;
> begin
>   select ora_rowscn into prevscn from parent where id=:old.parentId;
>   select column into oldColumnValue from parent as of scn prevscn where
> id=:old.parentId;
> 
> Is this the best approach, or maybe there is a better way?  And is there
> a way to have just one select statement instead of two (ie. get rid off
> prevscn)?

Why not just design the table with the OLD_VAL column? That's the usual 
practice.



-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com