Re: Modifying Data inserted with SQL Loader

Well, you could create a PL/SQL function (make sure to study the Rules of Purity, etc), and call it the same way in the .ctl file as you might an intrinsic Oracle SQL function.

However, be aware that doing transforms and checks beyond very, very simple ones will mean you must use the conventional path loader and not be able to use DIRECT. Depending on your data volumes, the difference could be enormous, and you might be better off loading into a dumb temporary flat table do your transforms in a PL/SQL procedure that puts the data into wherever you want it to eventually live. We found that using DIRECT, PARALLEL, etc and then post processing from the temp tables cut the flat file to 1st level derivative tables step by 75%.

RSH. "pstryjew" <pstryjew_at_att.net> wrote in message news:29f8354.0204241103.7f835dc7_at_posting.google.com...
> Hello,
>
> I have data in a flat file that I am using SQL Loader to load into a
> table.
>
> I'm adding an offset to one of the values and that is straight forward
> in the control file. I would also like to modify another value based
> on it's current value (if-then or look up table). I can't find out if
> this is possible.
>
> Control file:
>
> mynumber "TO_NUMBER(:mynumber) + offset",
> othernumber [if 4 insert 12, if 5 insert 15, etc]
>
> Pete Stryjewski
> pstryjew at att dot net
Received on Wed Apr 24 2002 - 14:18:38 CDT