Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql tables and cursors

Re: pl/sql tables and cursors

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 1997/11/30
Message-ID: <65s335$5ms@camelot.dsccc.com>#1/1

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.

  1. 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.
Received on Sun Nov 30 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US