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

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 17 2001 - 16:42:24 CST