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

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

Post by Jay Hardi » Wed, 19 Dec 2001 07:42:24



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!

 
 
 

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

Post by Mark » Thu, 20 Dec 2001 01:36:40


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


> 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!


 
 
 

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

Post by lee » Wed, 26 Dec 2001 06:13:55


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?

> 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


> > 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!

 
 
 

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

Post by Ed proch » Fri, 28 Dec 2001 03:46:21


The easiest is to load the raw data into a conversion table and then
use another program or script to populate the normalised table. That's
the route I would take.

 BUT, if you really want to do this with a SQL Loader script,
(assuming fixed position data) try this for the inserts:

--- day 1
INTO normalized_table
WHEN POSITION(16:23)!='        '
( ticker POSITION(2:5)  CHAR ,
  year   POSITION(9:12) CHAR ,
  day    POSITION(16:23)CHAR )
--- day 2
INTO normalized_table
WHEN POSITION(27:34)!='        '
( ticker POSITION(2:5)  CHAR ,
  year   POSITION(9:12) CHAR ,
  day    POSITION(27:34)CHAR )
--- and so on ....

A lot of error prone typing if you ask me, but it's your job.

ED Prochak
Magic Interface, Ltd.  440-498-3700
Consulting and contract engineering


> 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?


> > 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


> > > 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!

 
 
 

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

Post by Ed proch » Fri, 28 Dec 2001 03:46:21


The easiest is to load the raw data into a conversion table and then
use another program or script to populate the normalised table. That's
the route I would take.

 BUT, if you really want to do this with a SQL Loader script,
(assuming fixed position data) try this for the inserts:

--- day 1
INTO normalized_table
WHEN POSITION(16:23)!='        '
( ticker POSITION(2:5)  CHAR ,
  year   POSITION(9:12) CHAR ,
  day    POSITION(16:23)CHAR )
--- day 2
INTO normalized_table
WHEN POSITION(27:34)!='        '
( ticker POSITION(2:5)  CHAR ,
  year   POSITION(9:12) CHAR ,
  day    POSITION(27:34)CHAR )
--- and so on ....

A lot of error prone typing if you ask me, but it's your job.

ED Prochak
Magic Interface, Ltd.  440-498-3700
Consulting and contract engineering


> 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?


> > 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


> > > 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!

========= WAS CANCELLED BY =======:




Newsgroups: comp.databases.oracle.misc
X-No-Archive: yes
Lines: 2
NNTP-Posting-Host: wonenara.ozemail.com.au
X-Trace: ozemail.com.au 1009694995 203.108.164.177 (Sun, 30 Dec 2001 17:49:55 EST)
NNTP-Posting-Date: Sun, 30 Dec 2001 17:49:55 EST
Organization: OzEmail Ltd, Australia
Distribution: world
Date: Sun, 30 Dec 2001 03:21:31 GMT
Path: news.sol.net!spool0-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!news1.optus.net.au!optus!yorrell.saard.net!duster.adelaide.on.net!newsfeed.ozemail.com.au!ozemail.com.au!not-for-mail

This message was cancelled from within Mozilla.

 
 
 

1. Using SQL*Loader to load a record sequential data file

Hi all,

I need to load a fixed-length record sequential datafile(i.e, no new-line
characters at end of record) using SQL*Loader into the Oracle database.

I am not able to find any way to do this in the SQL*Loader documentation.

I can change the file to Line sequential format(put a new-line character
at the end of every record), but the size of the data file makes this
approach prohibitive.

I would appreciate if anyone could help me out with this problem.

Thanks,

Rajiv.

2. new to TSQL - how to assign sp result to variable??????

3. Loading RAW data using SQL*LOADER

4. dbase maillist

5. ORA-01722 during SQL Loader load - Help!

6. Another sproc question

7. Load a table using SQL*Loader

8. Can't drop materialised view (ORA-06553 : PLS 707)

9. Problem loading to Timestamp column using SQL Loader

10. Loading negative number using SQL*Loader