ORA-04091 with table insert
in [Oracle]
|
Prev: Connect Solaris ldapclient to a Oracle internet directory
Next: unregistering old dbid's from rman catalog
From: steph on 19 Jun 2008 10:48 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
From: steph on 19 Jun 2008 12:58 On 19 Jun., 16:48, steph <stepha...(a)yahoo.de> wrote: > 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 solved this with an autonomous pl/sql function.
From: Mladen Gogala on 20 Jun 2008 03:25 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
From: steph on 23 Jun 2008 14:18
On Jun 20, 9:25 am, Mladen Gogala <mgog...(a)yahoo.com> wrote: > 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 Gogalahttp://mgogala.freehostia.com i'll keep that in mind - so that i know once we're there ... |