ORA-04091 with table insert

[steph]

Hi Group,

Oracle 10G - This is my problem:

There's a table containing a time series like

date,val
1.1.08,2
2.1.08,1
3.1.08,4
5.1.08,-1

Now I need to advance this table in time, the new records come from a
query so I have something like:

insert into timeseriestable
select date,val from timeseriesquery
/

Now in this query I need to access the last value of the time-series
as a kind of a starting value for calculating the new records. (In
this example -1).

Unfortunately this leads to a ORA-04091. I understand the reason for
the problem, but I didn't find a good way to circumvent this. One idea
is to create a materialized view containing the last values for each
time series (yes, there's more than one time series involved ...). But
this solution is less flexible as I need to refresh the materialized
view before each update. The other idea is to use some pl/sql code
with an autonomous transaction to access the last value of the time
series. What else could i do here?

Thanks,
Stephan

[steph]

solved this with an autonomous pl/sql function.

[Mladen Gogala]

On Thu, 19 Jun 2008 09:58:39 -0700, steph wrote:

> solved this with an autonomous pl/sql function.

Oracle11 has compound triggers precisely to deal with this kind of
situation.

--
Mladen Gogala
http://mgogala.freehostia.com

[steph]

i'll keep that in mind - so that i know once we're there ...