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

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 Tue Dec 18 2001 - 10:36:40 CST