Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql tables and cursors
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.
: 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.Received on Sun Nov 30 1997 - 00:00:00 CST