Re: Data type question

Teresa Robinson wrote:
> Hello,
>
> 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
>
What is the business need?
[Quoted] Must all the changes go as one transaction? Or are they independent? If they are independent, you may want a way to checkpoint where your updates left off so that you do not need to go back and reprocess the temp table.

The best solution solves the business need first.   Ed Received on Fri Aug 19 2005 - 04:22:06 CEST