Tools for Oracle database - Data type question

Author

Data type question

Teresa Robinson

2005-08-15, 8:23 pm

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

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

Ed Prochak

2005-08-19, 3:23 am

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?
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

Teresa Robinson

2005-08-19, 1:23 pm

"Ed Prochak" <ed. prochak@magicinterfa
ce.com> said on 18 Aug 2005
19:22:06 -0700, in comp.databases.oracle.tools...:

>What is the business need?
>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

The program is for my boss or myself to run, or some other who works
with us who would be able to run the data loads to the db. This is
the process:

1) Raw data load into one table, this being a permanent dump for all
data.

2) Process raw data from dump table, insert data into other tables
according to what the data is.

3) Errors in this part are written to two error tables: one gets the
whole row, the other gets details.

4) Process errors; either correct error and insert into other tables,
or save for processing later.

5) A few times a year we will get a huge file with all raw data that
had been supplied before, with some exceptions:
a) Existing records that have changed from what we have in our db,
b) New records, and
c) Deleted records, which are records we have in the raw dump table
that are not in the huge file.

This is where my question comes in. During processing of this huge
file, the changed records must be validated on various points (such as
what the table/columns require, datatype, null; also on reference
points such as valid geographic data) either in the code or by the
user (myself or my boss). During this process, we will have a subset
of records that will have data that will be used to update one
particular other table, one of the ones in (2) above. No way to tell
how many records this subset will be, it could be as many as several
thousand. Some of these records will go together, such as
city/state/zip. They would be separate records, but they would all
update one record in the receiving table. All others would each
update one record each.

In my VB code, I would usually hold this info in an array of type that
matches the tables the info comes out of and goes into. I would loop
through this array and do the insertions/updates that way, making it
all go at once.

I don't know how to do this with Forms (6i), but I have seen examples
of the Record datatype and also the Varray datatype which look like
they may work. The other alternatives, building a SQL statement as
the process goes or doing each update as the user goes from one record
to the next, just don't appeal to me. There's also the possibility of
using one column in the table that's being processed as a flag of
sorts, which I am doing for records that are marked as error. I could
set that to 'Y', meaning to update this record, and then write a
routine to just go update if UPDATE_RECORD = 'Y'.

I want to put as little stress as possible on the db, and also have
this be as short in processing time as possible. I'm not sure if I've
answered your question about the business need, but I'll be happy to
respond further. Sorry to be so long-winded...

Thanks!

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

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

Ed Prochak

2005-08-22, 1:23 pm

Teresa Robinson wrote:
> "Ed Prochak" <ed. prochak@magicinterfa
ce.com> said on 18 Aug 2005
> 19:22:06 -0700, in comp.databases.oracle.tools...:
>
>
> The program is for my boss or myself to run, or some other who works
> with us who would be able to run the data loads to the db. This is
> the process:
>
> 1) Raw data load into one table, this being a permanent dump for all
> data.
>
> 2) Process raw data from dump table, insert data into other tables
> according to what the data is.
>
> 3) Errors in this part are written to two error tables: one gets the
> whole row, the other gets details.
>
> 4) Process errors; either correct error and insert into other tables,
> or save for processing later.
>
> 5) A few times a year we will get a huge file with all raw data that
> had been supplied before, with some exceptions:
> a) Existing records that have changed from what we have in our db,
> b) New records, and
> c) Deleted records, which are records we have in the raw dump table
> that are not in the huge file.
>
> This is where my question comes in. During processing of this huge
> file, the changed records must be validated on various points (such as
> what the table/columns require, datatype, null; also on reference
> points such as valid geographic data) either in the code or by the
> user (myself or my boss). During this process, we will have a subset
> of records that will have data that will be used to update one
> particular other table, one of the ones in (2) above. No way to tell
> how many records this subset will be, it could be as many as several
> thousand. Some of these records will go together, such as
> city/state/zip. They would be separate records, but they would all
> update one record in the receiving table. All others would each
> update one record each.
>
> In my VB code, I would usually hold this info in an array of type that
> matches the tables the info comes out of and goes into. I would loop
> through this array and do the insertions/updates that way, making it
> all go at once.
>
> I don't know how to do this with Forms (6i), but I have seen examples
> of the Record datatype and also the Varray datatype which look like
> they may work. The other alternatives, building a SQL statement as
> the process goes or doing each update as the user goes from one record
> to the next, just don't appeal to me. There's also the possibility of
> using one column in the table that's being processed as a flag of
> sorts, which I am doing for records that are marked as error. I could
> set that to 'Y', meaning to update this record, and then write a
> routine to just go update if UPDATE_RECORD = 'Y'.
>
> I want to put as little stress as possible on the db, and also have
> this be as short in processing time as possible. I'm not sure if I've
> answered your question about the business need, but I'll be happy to
> respond further. Sorry to be so long-winded...
>
> Thanks!
>
> --
> Teresa Robinson
> Staff Programmer Analyst
> Anteon Corporation
> trobinson at anteon dot com
>
> Posted Via mcse.ms Premium Usenet Newsgroup Services
> ----------------------------------------------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----------------------------------------------------------
> http://www.mcse.ms

Based on your last comment, you are taking a wrong approach,. ORACLE
is a powerful DBMS, nothing like MS ACCESS. (I'm assuming you are more
familiar with that based on your comment about using VB) You should not
worry about stressing an ORACLE DB.

That said, I think it sounds more like a job for PL/SQL than for FORMS.
I've done a lot of work using a processing model like yours, ie, a set
of staging tables loading into permanent tables. It's a good model but
I'd be hesitant to do the processing in Oracle FORMS. FORMS is a user
interface tool. Try doing it in PL/SQL.

If you need more help, drop me a line. (Please put ORACLE in the
subject because my account gets a LOT of spam). Or give me a call.

HTH,
Ed Prochak
Magic Interface, Ltd.
440-498-3700 (440-666-9013 direct line)

Sponsored Links