oracle and decimal numbers

Peter Beno
2005-03-21
2013-05-01
  • Peter Beno
    Peter Beno
    2005-03-21

    I noticed this in last time, so i guess it may not hurt to share my findings and even some sort of solution:
    It seems that oracle (at least v9) excpects string representation of decimal numbers to use localized decimal separator, so for example 1.2 produces 'invalid number' if passed as a value to SQLBindParam for numeric field under slovak locale (which uses , as decimal separator) - 1,2 work fine then. Also, returned values use localized decimal separator too. So, what to do with it:
    For input values, 1.2 = 12E-1. Exp format seems to be recognized well by odbc (at least for oracle and sybase asa wich are my targets), so all that needs to be done is format number in a way that it does not use any decimal separator.
    For output values, simply treat any non-digit character as decimal separator - nobody should be stupid enough to set digit as decimal separator i guess.

    This seems to working just well for input formatting:
    function BcdToStrExpNotationWithNoDecimalSeparator(Bcd: TBcd): String;
    var
      Sc: Byte;
    begin
      Sc := Bcd.SignSpecialPlaces and 63;
      if Sc > 0 then
      begin
        Bcd.SignSpecialPlaces := Bcd.SignSpecialPlaces and not 63;
        Result := Format('%sE-%d', [ BcdToStr(Bcd), Sc ]);
      end
      else
        Result := BcdToStr(Bcd);
    end;

     
    • Very interestingly. I shall test your variant. Even if it will work not everywhere, I all the same shall add it.

       
    • Peter Beno
      Peter Beno
      2005-03-21

      Btw, this is extract from msdn:

      ODBC Programmer's Reference 

      Numeric Literal Syntax
      The following syntax is used for numeric literals in ODBC:

      numeric-literal ::= signed-numeric-literal | unsigned-numeric-literal

      signed-numeric-literal ::= [sign] unsigned-numeric-literal

      unsigned-numeric-literal ::= exact-numeric-literal | approximate-numeric-literal

      exact-numeric-literal ::= unsigned-integer [period[unsigned-integer]] |
      period unsigned-integer

      sign ::= plus-sign | minus-sign

      approximate-numeric-literal ::= mantissa E exponent

      mantissa ::= exact-numeric-literal

      exponent ::= signed-integer

      signed-integer ::= [sign] unsigned-integer

      unsigned-integer ::= digit...

      plus-sign ::= +

      minus-sign ::= -

      digit ::= 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 0

      period ::= .

      1998-2004 Microsoft Corporation. All rights reserved.

      Well, i wonder where did oracle get the idea about using localized decimal searator at first place? However i'm loking at it, i still see just "period ::= ." - that is a DOT exatly. Oh, well, at least that same specification tells that exp notation should work!