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
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.
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)
Indeed -- with a combination of an external table + a MERGE statement, it'd be a one-step process.