Sqlloader , validate data before load

[Amonte]

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

[Srinivas_Sharma]

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

[DaPi]

. . . or SQL*loader into a work table and use PL/SQL to transfer the insert/updates to the target table.

[Amonte]

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?

[Srinivas_Sharma]

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

[DaPi]

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

[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)

[slimdave]

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.

[DaPi]

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?

[rad_jen]

As DaPi suggested before, using a work table and pl/sql is much simplier.