Re: SQLLDR fixed format and number format

From: Moritz Klein <mklein_at_students.uni-mainz.de>
Date: Mon, 19 Sep 2005 15:43:20 +0200
Message-ID: <dgmf9j$cn3$1@news1.zdv.uni-mainz.de>

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 Received on Mon Sep 19 2005 - 08:43:20 CDT