Printable View
Hello everybody,
some application generates ASCII-Files
like monday.dat, tuesday.dat etc.
I would like to load data form this tables
into a table in Oracle using following validation rules:
If a record already exists in the table then skip it.
If a record has been updated on Monday evening - update it in the table.
If a record doesn't exist - insert it.
Hope you understand what I mean :)
Thanks in advance.
WinNT, Oracle 8.1.7
I think the best thing would be to go for UTL_FILE rather than SQLLOADER unless its huge amount of data.
If you use UTL_FILE, you can do all your checks
SS
. . . or SQL*loader into a work table and use PL/SQL to transfer the insert/updates to the target table.
Srinivas_Sharma
thx, can you recommend some good examples for usage of the UTL_FILE ?
DaPi
thx, should I use the trigger to compare this 2 tables? how can one define the validation rules for it?
http://www.adp-gmbh.ch/ora/plsql/utl_file.html
comparative analysis of sqlloader and utl_file
http://www.orafaq.com/papers/sqlload.doc
check out documentation in oracle site as well......
HTH
Srini
If you have the choice, I see several advantages of using SQL*loader with a work table:
- you don't have to have access to the server, other than as an Oracle client, and no changes in init.ora are required
- you can sort the data at will and can deal with "duplicate" rows
- the data is parsed (separated into columns) for you
UTL_FILE comes into its own when the file can not be loaded (easily) as a table: reading a transaction stream where the record format is a function of the transaction code; reading free text (e.g. alrt.log).
The validation rules (written in PL/SQL) would be much the same in either case. In this case, if you use SQL*loader, you might even be able to do it with a collection of SQL statements.
I don't think I would use a trigger . . . .
In 9i you could do it over the external table feature ;-)
So in case your are looking for arguments to move from 8i to 9i this would be a good one (depending on how often you would use it)
Indeed -- with a combination of an external table + a MERGE statement, it'd be a one-step process.Quote:
Originally posted by mike9
In 9i you could do it over the external table feature ;-)
So in case your are looking for arguments to move from 8i to 9i this would be a good one (depending on how often you would use it)
The external table has to be on the server - this may or may not be a problem . . .
"If a record already exists in the table then skip it.
If a record has been updated on Monday evening - update it in the table."
Will MERGE do that?
As DaPi suggested before, using a work table and pl/sql is much simplier.
Ah, well i guess it would, but you never know 'til you try.Quote:
Originally posted by DaPi
"If a record already exists in the table then skip it.
If a record has been updated on Monday evening - update it in the table."
Will MERGE do that?
"If a record has been updated on Monday evening"
Like:
which Monday? any Monday? the previous Monday?
do you include this batch if you are running it on a Monday?
do you include the previous batch if you run it twice on a Monday?
etc etc etc
I'm sure MERGE in version 10g solves this automatically; not only making DBA's redundant, but developers and analysts too :D
All I need to see in 10G... should support analytical function in MV FAST refresh option
Don't hold your breath -- MV's are buggy enough without putting analytical functions in there as well.Quote:
Originally posted by balajiyes
All I need to see in 10G... should support analytical function in MV FAST refresh option
Personally I'd settle for Oracle spending some quality time with the refresh and query rewrite code before they start that.
Is this thread hijacked?
Not yet, I'll post some pictures of my cat tomorrow :)Quote:
Originally posted by slimdave
Is this thread hijacked?
Thx everybody!