Re: Using SQL*Loader to normalize data during load...

I've never tried it, by why couldnt you put a trigger on the target table (the one sql loader is loading) to execute whatever procedure you like to inert into some OTHER table?

MarkyG wrote:

> Maybe in Oracle 12i ;-)
>
> No function exists in SQL*Loader. Unless you build some kind of
> function based around the field you want to insert and call it in the
> SQL*Loader script.
>
> M
>
> jhardin_at_vestek.com (Jay Harding) wrote in message news:<a8ee054f.0112171442.6cc1f36a_at_posting.google.com>...
> > I am wondering if there is a way to normalize source data during
> > SQL*Loader load.
> >
> > Suppose for example the source data represents stock prices and
> > looks something like the following(doesn't matter if it's fixed
> > with or delimited):
> >
> > "AAAX","1994","14.20312","13.90625","14.01562",...,"15.93750"
> > "BBBY","1997","20.14062","19.85938","19.84375",...,"21.03125"
> > ... (etc)
> >
> > The data fields in each of these records would represent the following:
> > TICKER,YEAR,DAY_1_PRICE,DAY_2_PRICE,DAY_3_PRICE,...,DAY_366_PRICE
> >
> > Where "DAY_1_PRICE" means "the price on Jan 1", and "DAY_2_PRICE"
> > means "the price on Jan 2", ... and "DAY_366_PRICE" means, "the price
> > on Dec 31".
> >
> > Ideally, I would like the loaded result set to look something like:
> >
> > "AAAX", "Jan-01-1994", "14.20312"
> > "AAAX", "Jan-02-1994", "13.90625"
> > "AAAX", "Jan-03-1994", "14.01562"
> > ...
> > "AAAX", "Dec-31-1994", "15.93750"
> > "BBBY", "Jan-01-1997", "20.14062"
> > "BBBY", "Jan-02-1997", "19.85938"
> > "BBBY", "Jan-03-1997", "19.84375"
> > ...
> > "BBBY", "Dec-31-1997", "21.03125"
> >
> > So, what I'm wondering is if this sort of transformation is possible with
> > SQL*Loader???
> >
> > Could I do something like:
> >
> > LOAD DATA
> > INFILE 'blah.dat'
> > APPEND INTO TABLE denormalized_prices
> > (
> > ticker CHAR TERMINATED BY ',' ENCOLSED BY '"',
> > year CHAR TERMINATED BY ',' ENCOLSED BY '"',
> > day_1 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > "normalized_insert(:ticker, :year, :day_1)"
> > day_2 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > "normalized_insert(:ticker, :year, :day_2)"
> > day_3 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > "normalized_insert(:ticker, :year, :day_3)"
> > ...
> > day366 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > "normalized_insert(:ticker, :year, :day_366)"
> > )
> >
> > Here, "normalized_insert" would be a simple custom function
> > which inserts a record for its given arguments???
> >
> > Any suggestions would be greatly appreciated.
> > Thanks!
Received on Mon Dec 24 2001 - 15:13:55 CST