Re: [cx-oracle-users] Decimal and european NLS_LANG

[Michael B. <mi...@zz...>]

On Oct 25, 2010, at 2:37 PM, Amaury Forgeot d'Arc wrote:

> 2010/10/25 Michael Bayer <mi...@zz...>:
>> I have a user (a user of the database access layer SQLAlchemy)  who wants to do this:
>> 
>>        import os
>>        import cx_Oracle
>>        import decimal
>> 
>>        os.environ["NLS_LANG"] = "GERMAN"
>> 
>>        dsn = cx_Oracle.makedsn('localhost', '1521', 'xe')
>>        conn = cx_Oracle.connect('scott', 'tiger', dsn)
>>        cursor = conn.cursor()
>>        cursor.execute("select :foo from dual", foo=decimal.Decimal("56.5"))
>>        print cursor.fetchall()
>> 
>> The error is:
>> 
>>        cx_Oracle.DatabaseError: OCI-22062: invalid input string [56.5]
> 
> A similar issue was fixed in cx_Oracle 5.0.3:
> http://cx-oracle.sourceforge.net/HISTORY.txt
> see item 14)
> 
> Are you using an older version?

ah yes and I see it is your fix, and yes the above test case is fixed.    So, you're sending Decimal objects to cx_oracle.  How are you getting them back ?    Our current approach is kind of a nightmare as we use an output handler and parse from string, code fragments are below:

_to_decimal = Decimal
"""Convert string -> Decimal"""
    
def _detect_decimal(value):
    """Convert string -> Decimal if decimal point present, otherwise int.
    
    We want simple phrases like "select some_seq.nextval from dual" 
    to return int.
    
    """
    if "." in value:
        return Decimal(value)
    else:
        return int(value)

def _detect_decimal_char(connection):
    """See what the decimal separator due to NLS_LANG.

   We run this just once when the first connection is made.
  """
    cursor = connection.cursor()
    cursor.execute("SELECT 0.1 FROM DUAL")
    val = cursor.fetchone()[0]
    cursor.close()
    char = re.match(r"([\.,])", val).group(1)
    if char != '.':
        _detect_decimal = lambda value: _detect_decimal(value.replace(char, '.'))
        _to_decimal = lambda value: Decimal(value.replace(char, '.'))
    
def output_type_handler(cursor, name, defaultType, size, precision, scale):
    """Output type handler.  Attempts to return Decimal / int as appropriate.

       We associate this with all cx_Oracle.Connection objects.

    """
    
    if defaultType == cx_Oracle.NUMBER and precision and scale > 0:
        return cursor.var(
                    cx_Oracle.STRING, 
                    255, 
                    outconverter=_to_decimal, 
                    arraysize=cursor.arraysize)
    elif defaultType == cx_Oracle.NUMBER \
            and not precision and scale <= 0:
        return cursor.var(
                    cx_Oracle.STRING, 
                    255, 
                    outconverter=_detect_decimal, 
                    arraysize=cursor.arraysize)