Re: SQLLDR fixed format and number format

Moritz Klein schrieb:
> 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

I couldn't reproduce your problem, (though not with the controlfile you provided ). To load data from your example, i had only specified group delimiter explicitly (i.e. "to_number(:official_vol_prev,'9999999G999')" , the sign was recognized by sqlldr without any problems. You could however specify the sign format mask explicitly (i.e. "to_number(:official_vol_prev,'S9999999G999')").

Best regards

Maxim Received on Mon Sep 19 2005 - 07:04:04 CDT