SQLLDR fixed format and number format

From: Moritz Klein <mklein_at_students.uni-mainz.de>
Date: Mon, 19 Sep 2005 12:39:45 +0200
Message-ID: <dgm4hc$c5e$1@news1.zdv.uni-mainz.de>

Hi NG,
the following situation:
I have a datafile with following format:

Aug 05 97.865      97.865    0.000 97.870S 97.865  97.950  97.665 
97.865           13          N/A           0|         202      20,418 
     -164 [newline]
Sep 05 97.865      97.860    0.000 97.865  97.860  97.995  94.290 
97.860       20,410          N/A           0|      21,314     521,305 
   -1,249 [newline]
Oct 05             97.835    0.000                 97.865  97.800 
97.835            0          N/A           0|           0       2,998 
        0 [newline]
Nov 05 97.830      97.830    0.000 97.830  97.830  97.830  97.830 
97.830        1,000          N/A           0|           0           0 
        0 [newline]
Dec 05 97.785      97.790   +0.005 97.805  97.780  98.065  94.405 
97.790       62,100          N/A           0|      75,613     633,261 

  +11,662 [newline]

Contents of Controlfile:

LOAD DATA

	INFILE data_test.dat
	INTO TABLE underlying
	APPEND
	(
		contract position(1:6) "to_date(:contract, 'Mon yy')",
		lookupdate "to_date('11.08.05', 'dd.mm.yy')",
		opening position(8:13),
		settle position(20:25),
		change position(27:34),
		daily_high position(36:41),
		daily_high_type position(42:42) nullif daily_high_type = ' ',
		daily_low position(44:49),
		daily_low_type position(50:50) nullif daily_low_type = ' ',
		lifetime_high position(52:57),
		lifetime_high_type position(58:58) nullif lifetime_high_type = ' ',
		lifetime_low position(60:65),
		lifetime_low_type position(66:66) nullif lifetime_low_type = ' ',
		closing position(68:73),
		connect_vol position(76:86) "to_number(:connect_vol, '9999999999')",
		basis_vol position(90:100) "decode(ltrim(rtrim(:basis_vol)), 'N/A', 0, 
:basis_vol)",
		trade_vol position(102:111),
		official_vol_prev position(114:124) "to_number(:official_vol_prev, 


'9999999999')",

		open_int_prev position(126:136) "to_number(:open_int_prev, '9999999999')",
		open_int_change_prev position(138:147) "to_number(:official_vol_prev, 


'9999999999')"

	)

My problem with this load is every column with a sign on it. I took a look at Oracle SQL*Loader The Definitive Guide, but that didn't help. So I#m stuck. Can anyone point me into the right direction for loading these columns? The main problem for me is, this columns can be up to 10 digits plus sign, but not always. I figured out how to get rid of the thousands sign (,) but the data now gets loaded without the minus/plus sign.

Any help appreciated,

    Moritz Received on Mon Sep 19 2005 - 05:39:45 CDT