Trigger with SQL Loader

[kevina555]

Hi All,

I have a Normalization situation.
I have denormalized data in the incoming flat file and has to be normalized into the target, while generating PK as well. But SQL Loader only allows us to create a sequence. Can I use a trigger to tell SQL Loader that if (field x in the record just loaded = field x of the new incoming record), don't load the incoming row and load it only if they are not equal?

Details:
I have a flatfile source that has say 10 fields. Now, field 2, 5, 7 go into target T1 and 3, 4 go into target T2 and 6, 9, 10 go into T3 etc...

Now, I have to build a PK on T1 based on the value of a combination of fields 2 and 5. And for every unique record in T1, I have to have a unique record in T2, with the same PK (created for T1) copied into a field of T2(not available in the incoming data) and similarly for T3 etc.....


How do I acheive this?

1)Can I use a WHEN clause in the control file? but it does not take :NEW and :OLD as arguments

2) Can I use a before insert trigger to check for the incoming fields?

3) Should I use an after insert trigger to delete the duplicates?

How to acheive the PK values in a scenario?


Thank you .....

 

[SantaMufasa]

Kevina,

My first suggestion is to apply Santa's First Law of Programming: "One test is worth 1000 expert opinions." So, try it and see.

My second suggestion is, "Don't pile too much work on SQL*Loader." I suggest you simply do a straight load of your "flat" records into a temp table, then do your real work via SQL or PL/SQL, manipulating your data from the various tables, resulting in the finished data set that matches your business needs.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:33 (02Mar05) UTC (aka "GMT" and "Zulu"),
@ 11:33 (02Mar05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.