Re: SQLLDR fixed format and number format

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 19 Sep 2005 18:21:12 +0200
Message-ID: <dgmoho$4t6$00$1@news.t-online.com>

Moritz Klein schrieb:
> Maxim Demenko schrieb:
>

>> 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')").

>
>
> It worked, but not as thought... First of all I had an copy&paste
> problem so a wrong bind-variable was used on the last column (damn
> little errors). But to get positive, negative and zeros out of this
> column I had to do an additional decode.
> line looks like:
> open_int_change_prev position(138:147)
> "to_number(
> decode(ltrim(rtrim(:open_int_change_prev)),'0', '+0',
> ltrim(rtrim(:open_int_change_prev))),'S9G999G999')"
>
> My question is now, why does the number format 'S9G999G999' does not
> work for an input with the value '0'? Shouldn't SQL*Loader not treat a
> missing sign like a '+'?
>
> Thanks for your help,
> Moritz

Now i got it too, it seems, by using of 'G' + is not more optional, and by combination 'SG' all numbers must be signed. So, i would suggest use of only 'G' modifiers and ltrim all '+' signs (i.e. open_int_change_prev position(138:147)
"to_number(ltrim(:open_int_change_prev,'+'),'S99G999')" ).

Btw, if you would post the ddl of your table ( as well as your plattform and db version) , it would be easier to reproduce your problem, so you would increase the chance to get valuable response.

Best regards

Maxim Received on Mon Sep 19 2005 - 11:21:12 CDT