ORA-04091 with table insert

#1

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

#2

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#3

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#4

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#5

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#6

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#7

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.

#8

On 19 Jun., 16:48, steph <stepha... (AT) yahoo (DOT) de> wrote:

Quote:

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.