ORA-04091 with table insert

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 Received on Thu Jun 19 2008 - 09:48:32 CDT