[cx-oracle-users] maintaining multiple connections with different encodings

[Dana P. <da...@gm...>]

I have a python process which needs to connect to multiple oracle
databases with different NLS_CHARACTERSET values.

I can set the appropriate character set by setting the NLS_LANG
environment variable, either in my shell or using os.environ before
connecting. For utf-8 databases, I set it to "AMERICAN_AMERICA.UTF8",
for Latin-1 (ISO-8859-1) I set it to "AMERICAN_AMERICA.WE8ISO8859P1".

Here's some code that illustrates the problem:

import os
import cx_Oracle

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
utf8_conn = cx_Oracle.connect('dana', 'pw', 'utf8db')
print utf8_conn.encoding
s = utf8_conn.cursor().execute('SELECT :s AS str FROM dual',
s=u'd\u00e4t\u00e4').fetchone()[0]
print s, type(s), len(s), len(s.encode('utf8'))

utf8_conn.close()

os.environ['NLS_LANG'] ='AMERICAN_AMERICA.WE8ISO8859P1'
latin1_conn = cx_Oracle.connect('dana', 'pw', 'latin1db')
print latin1_conn.encoding
s = latin1_conn.cursor().execute('SELECT :s AS str FROM dual',
s='d\xe4t\xe4').fetchone()[0].decode('latin1')
print s, type(s), len(s), len(s.encode('latin1'))

This outputs:
UTF-8
dätä <type 'unicode'> 4 6
ISO-8859-1
dätä <type 'unicode'> 4 4

The problem is, if I don't close the utf8 connection before opening
the latin1 connection, the latin1 connection's encoding will be utf8.
Here's the output with the utf8_conn.close() call removed:

UTF-8
dätä <type 'unicode'> 4 6
UTF-8
d¿ <type 'unicode'> 3 3

Is there any way around this? I'd like to be able to open all my
database connections at startup time instead of constantly
opening/closing them.

I'm using cx_Oracle 5.1 with oracle client version 10.2.0.1 on SLES
10. Both databases that I'm connecting to are 10.2.0.5.

Thanks,

Dana P