Loading RAW data using SQL*LOADER

Loading RAW data using SQL*LOADER

Post by Bill » Wed, 02 Jul 1997 04:00:00



Hello,

I am in a bind over how to load RAW data into Oracle and would really
appreciate any help I can recieve. Following are some specifics...

I am trying to load a ".sq2" sequence file output by a cobol program
and then sent to the printer to output one record at a time on a
pre-printed form. I wish to store the records (approx. 81 lines long)as
a single record in Oracle. I have been trying SQLLOAD with the
TERMINATED BY clause (I have inserted a "+" sign at the beginning of
every record using "sed". I have even used the "DIRECT=TRUE" option to
no avail. I have also created my table using (RAW, VARCHAR, LONG RAW,
LONG) datatypes with varying success, but each time I get records only
one line long (I think Oracle is reading the newline chars or something)
 Maybe datatypes, table design, or some other SQLLOAD option I am
unaware of is my stumbling block.
Again, any assistance will be appreciate.

Bill

 
 
 

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

2. SELECT FROM Stored Procedure

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

4. 29363-OR-Greater Eugene Area-HP-UX-ORACLE-Sr. Prog. Analyst URGENTLY needed!

5. Load a table using SQL*Loader

6. Help:ContextId

7. Problem loading to Timestamp column using SQL Loader

8. Sybase and Holos

9. Loading negative number using SQL*Loader