Re: pl/sql tables and cursors

John Strange wrote:
>
> I had to load 3.6 million rows.
> Used a loop with a cursor and had to do a
> commit every 20,000 records.
> Picked 20,000 based on the rollback segment sizes.
>
> This was requried becuase I would fill up the rollback logs.
> You seem to have a slight varient of the same problem.
> Your database hang is a space problem on some system/data/log file.
>
> A. Davidson (dradave_at_ionet.net) wrote:
> : I am attempting to load 15000 + records from a temporary table
> : declared as 25 columns of varchar2(60) into a pl/sql table for later
> : processing into production tables.
 

> : I'm creating a cursor with select * from the temporary table to
> : populate the pl/sql table using a cursor for loop.
 

> : I do this twice, once with a cursor in a select statement using where
> : exists against the temporary table and production table to populate an
> : update pl/sql table and a select where not exists to populate a add
> : new record pl/sql table.
 

> : This method seems to work fairly well using small amounts of test
> : data...2000 records or less. But when I do a full scale test using
> : all 15000 plus records, it hangs the DB and I have to shut it down
> : then restart it. Sometimes it will work properly on the add routine
> : and sometimes on the update routine but never both. I need to do the
> : update this way to make sure I don't update an existing value with a
> : blank from the update file, so I have to perform null checks on the
> : incoming data. This is essentially a reload every day so all 15000 +
> : records are updated daily. (I can't drop and reload).
 

> : I'm using the default setup for the database when it was installed.
 

> : I guess my question is:
 

> : 1. How many rows can a cursor hold?
> : 2. How many rows can a pl/sql table hold?
 

> : I know this also depends on number of columns and their datatypes, I
> : can't seem to find this information in the documentation.
 

> : Thanx for any info supplied...
 

> : A. Davidson
> : dradave_at_ionet.net
>
> --
> This posting represents the personal opinions of the author. It is not the
> official opinion or policy of the author's employer. Warranty expired when you
> opened this article and I will not be responsible for its contents or use.

Hi,

why don't you use a psecial adequate sized rollback segemnt for your job?

Then set transction to use this segment.

SET TRANSACTION USE ROLLBACK SEGMENT <segment name>;

-- 
Regards

Matthias Gresz    :-)