-
Sqlloader , validate data before load
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)
Last edited by mike9; 01-07-2004 at 10:57 AM.
-
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.
-
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.
OCP 7.3,8.0,8i,9i
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|