cx_oracle decimal parsing depends on non european NLS_LANG numeric format

[Former user]

When numbers are not formatted using a decimal point, the decimal detection fails.

[Former user]

Example causing a traceback on a very simple query

[Mike Bayer]

There is a workaround for this, but may I suggest that the GERMAN setting on NLS_LANG is going to cause more problems than that. Decimal binds will not work either, and that is a cx_oracle issue:

import os
import cx_Oracle
import decimal

os.environ["NLS_LANG"]("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()

for the above, we could add more handlers to accommodate but this will add latency to all queries. I'd ask why you can't at least set NLS_LANG to "GERMAN_AMERICA", because for SQLA to work around this is not going to be pretty.

[Mike Bayer]

the patch which repairs non-US decimal strings as results is:

diff -r f86d8a877ba774434b294a7e94470ccdf2c52944 lib/sqlalchemy/dialects/oracle/cx_oracle.py
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Sun Oct 24 16:08:14 2010 -0400
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Mon Oct 25 10:33:31 2010 -0400
@@ -76,6 +76,7 @@
 from datetime import datetime
 import random
 from decimal import Decimal
+import re

 class _OracleNumeric(sqltypes.Numeric):
     def bind_processor(self, dialect):
@@ -473,28 +474,48 @@
                 self.dbapi.BLOB: oracle.BLOB(),
                 self.dbapi.BINARY: oracle.RAW(),
             }
+    @classmethod
+    def dbapi(cls):
+        import cx_Oracle
+        return cx_Oracle

     def initialize(self, connection):
         super(OracleDialect_cx_oracle, self).initialize(connection)
         if self._is_oracle_8:
             self.supports_unicode_binds = False
+        self._detect_decimal_char(connection)

-    @classmethod
-    def dbapi(cls):
-        import cx_Oracle
-        return cx_Oracle
-
+    def _detect_decimal_char(self, connection):
+        conn = connection.connection
+        outputtypehandler = conn.outputtypehandler
+        conn.outputtypehandler = None
+        try:
+            cursor = conn.cursor()
+            cursor.numbersAsStrings = 1
+            cursor.execute("SELECT 0.1 FROM DUAL")
+            val = cursor.fetchone()[0](0)
+            cursor.close()
+            char = re.match(r"([\.,](\.,))", val).group(1)
+            if char != '.':
+                _detect_decimal = self._detect_decimal
+                self._detect_decimal = lambda value: _detect_decimal(value.replace(char, '.'))
+                self._to_decimal = lambda value: Decimal(value.replace(char, '.'))
+        finally:
+            conn = outputtypehandler
+        
+    def _detect_decimal(self, value):
+        if "." in value:
+            return Decimal(value)
+        else:
+            return int(value)
+    
+    _to_decimal = Decimal
+    
     def on_connect(self):
         if self.cx_oracle_ver < (5,):
             # no output type handlers before version 5
             return

-        def maybe_decimal(value):
-            if "." in value:
-                return Decimal(value)
-            else:
-                return int(value)
-                
         cx_Oracle = self.dbapi
         def output_type_handler(cursor, name, defaultType, size, precision, scale):
             # convert all NUMBER with precision + positive scale to Decimal
@@ -503,7 +524,7 @@
                 return cursor.var(
                             cx_Oracle.STRING, 
                             255, 
-                            outconverter=Decimal, 
+                            outconverter=self._to_decimal, 
                             arraysize=cursor.arraysize)
             # if NUMBER with zero precision and 0 or neg scale, this appears
             # to indicate "ambiguous".  Use a slower converter that will 
@@ -515,7 +536,7 @@
                 return cursor.var(
                             cx_Oracle.STRING, 
                             255, 
-                            outconverter=maybe_decimal, 
+                            outconverter=self._detect_decimal, 
                             arraysize=cursor.arraysize)
             # allow all strings to come back natively as Unicode
             elif defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):

[Mike Bayer]

well good news, the handling of Decimal() as a bind was fixed in cx_oracle 5.0.3. going to check if we should be doing something for the receive side.

[Former user]

Replying to zzzeek:

I'd ask why you can't at least set NLS_LANG to "GERMAN_AMERICA", because for SQLA to work around this is not going to be pretty.

I can do that without problems. I just wanted to report this problem so that it could be avoided for other users, or a helpful error message could be added for that case.

• Karl Bartel

[Mike Bayer]

thanks for the response. In fact I think it will be OK since cx_oracle itself did a similar workaround for its Decimal bind handling, i.e. handling the decimal point alone is sufficient. If I can clean up the above patch, and add a note to the documentation that at least cx_oracle 5.0.3 is needed for european decimal points on the bind parameter side, it should be OK.

[Mike Bayer]

completed, 37644a64ec6f6e466e93cdefbda332e13d7f77c4 , thanks for the test case !

[Mike Bayer]

Removing milestone: 0.6.6 (automated comment)