Re: PL/SQL limitation while updating table A with a cursor on Table A

Shivakumar Gopalakrishnan wrote:
>
> I am doing a financial data warehouse currently. We have the Fact Table
> Balances by Period and the Account(which is a composit of 9 fields). I have
> a kind of period opening process. What this process does is read the
> previous month records and create the current month records with the ending
> balance of privious month as opening balance of the current month.
>
> While running this procedure, I always get a ORA-01555, which says snapshot
> is too old in rollback segment. We have increased the extent size of the
> rollback segment, decreased the no of records after which I commit, Doubled
> the tablespace of the rollback segment. Still this error is persisting.
>
> Currently we have decided to write the balance to a flat file and load it
> using sql/loader. Does anyone know of a better way.
>
> The problem we found is that, Oracle tries to maintain a copy of the
> database as it was before, or as of the open of the cursor. SQL/Loader by
> passes this somehow. Does anyone know of a way to turn this switch off, as
> is done by the sql/loader.
>
> Any help or suggestion is appreciated. Pls mail them to shiva_at_one.net

The problem here is that you have a select cursor open on a table *at* *the* *same* *time* as you are trying to update / insert the same table. Note that this is *not* the case when you are using SQL*Loader, since there is no active select cursor in this case.

The same problem can be observed in a single update statement of the type:
update X
set col = (select ... from X where ...)
where ...

The answer is to avoid what I call "reflexive" operations.

In your case, it might be simpler to define a work table, populate it with the new values within your select cursor loop, close the select cursor and then update / insert the original table with values from your work table.

If the work table is small, you could even use a set of PL/SQL tables (or a table of rowtypes in V7.3), but I prefer the first method as being easier to apply transaction control.

Hope this helps.

Chrysalis. Received on Thu Jun 19 1997 - 00:00:00 CDT