[cx-oracle-users] Decimal and european NLS_LANG

[Michael B. <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]

of course, because "GERMAN" wants "56,5" and I'm assuming cx_Oracle is calling str() on the Decimal.

It also appears that Python Decimal uses "." as the decimal point no matter what you do with the "locale" module.

So I just need to give the user an answer, pick one:

	1. You shouldn't be using NLS_LANG=GERMAN with cx_Oracle, at least use GERMAN_AMERICA

	2. You shouldn't be using Python Decimal objects as bind parameters ...
	2a. .... when NLS_LANG=GERMAN

	3. You should be using a custom inputtypehandler which does the appropriate string conversion (can we send strings here ?)

	4. cx_oracle should handle this automatically

There's also the other side of it, Decimal objects as results, but since cx_oracle doesn't provide that directly we will have to add more conditionals to our custom outputtypehandler, would be nice if cx_oracle could someday return Decimal() directly since we have a lot of guesswork on that side as well.