Data type question

[Quoted] I've been reading about various data types on Oracle.com and in Morgan's Library, trying to decide which is the better way to go with the project I'm coding now. (9.2.1.0, Forms 6i, W2K/XP)

We have almost 60K records in a temp table that are compared to a permanent table. These are records that have been marked as "existing" in that table, by comparing an ID field. The comparison involves looking at a subset of fields in each record and comparing the values in "temp" to the corresponding values in "perm" to see what changes could have been made. We could have anywhere from 0 to 15 changes per record in "temp", and each change is written as a new record in "temp_change", containing just the ID number of the "temp" record, the column name that changed, the old value, the new value, and a separate ID number for the primary key.

When finished running on the first set of data, we end up with over 4K changes on over 3K records. The next step is to validate the changes, and if valid, update the records that each change corresponds with. My question is about the process of updating. After each record validates, I could either update its record in the DB or I could store the record in a datatype for later updating "in bulk", so to speak.

What I'd like to do is build up an array of "record" datatype and save all the updating until last, but I'm unsure of how to do this in Oracle. I've done it many times in VB, and the documentation I've been reading shows it's not *too* much different in Oracle. One question is about the Varray: can I somehow change the size of it after the processing is finished and it's found out the number of records that need validation? Like VB's ReDim? I hate to take up lots of resources by declaring a Varray of 10K slots if I am only going to need 1K, or 3K, or whatever. Also, is this the better way to do this? I could also build up a SQL statement "on the fly", using string concatenation, which I can use in a FORMS_DDL() call. I could also put the statement into a Varray slot, and save all SQL until last.

What do you recommend?

Thanks!

-- 
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

 Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
    ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------        
                http://www.usenet.com