cx_Oracle.connect() 'encoding' parameter - 'utf8' vs 'UTF-8'

[sobayed]

While reading text data from an Oracle DB I came across some strange UnicodeDecodeErrors. After debugging the issue for quite some time I realized, that 'utf8' / 'UTF-8' as values for the cx_Oracle.connect() 'encoding' parameter have a different effect and are therefore not synonyms in this context! Most importantly, they differ in terms of the Connection.maxBytesPerCharacter attribute (see below):

import cx_Oracle

with cx_Oracle.connect(dsn='MyDSN', user='MyUser', password='MyPassword', encoding='UTF-8', nencoding='UTF-8') as con:
    print(con.maxBytesPerCharacter)

 # 4

with cx_Oracle.connect(dsn='MyDSN', user='MyUser', password='MyPassword', encoding='utf8', nencoding='utf8') as con:
    print(con.maxBytesPerCharacter) 

# 3

I assume that 'utf8' potentially maps to Oracle's CESU-8 standard which seems counterintuitive. Also, I couldn't find this behaviour mentioned in the documentation and it goes against the standard Python naming scheme (https://docs.python.org/3/library/codecs.html#standard-encodings). Therefore I suggest either:

1. Update documentation to make users aware of this issue (including full table of acceptable encoding parameter values and their respective Oracle counterparts).

and/or:

2. Change parameter value mapping to treat 'utf8' and 'UTF-8' as synonyms

Answer the following questions:

1. What is your version of Python? Is it 32-bit or 64-bit?
   Python 3.6.5 64-bit

2. What is your version of cx_Oracle?
   6.2.1

3. What is your version of the Oracle client (e.g. Instant Client)? How was it
   installed? Where is it installed?
   oracle-instantclient12.2-basic

4. What is your version of the Oracle Database?
   11.2.0.4.0

5. What is your OS and version?
   Debian 8 64bit

6. What compiler version did you use? For example, with GCC, run
   gcc --version.
   GCC 7.2.0

7. What environment variables did you set? How exactly did you set them?
   None

8. What exact command caused the problem (e.g. what command did you try to
   install with)? Who were you logged in as?
   cursor.fetchone()

9. What error(s) you are seeing?
   UnicodeDecodeError when using encoding='utf8'

[cjbj]

Some quick, late night comments:

From #122 (comment) "cx_Oracle uses the Oracle character set name if it cannot find an encoding using the IANA name. "

There's background into some of the Oracle char sets at http://www.oracle.com/technetwork/products/globalization/twp-appdev-unicode-10gr2-129234.pdf

For completeness, all Oracle char sets are listed at: https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/appendix-A-locale-data.html#GUID-344E34CA-DD16-4979-8CF1-6D0A7AEBAA4A

Yes, cx_Oracle doc can be improved. PRs' welcome.

What is your DB charset? :

cursor.execute("""select 'DB: ' || value as db_charset from nls_database_parameters where parameter = 'NLS_CHARACTERSET'
union
select distinct 'Client: ' || client_charset from v$session_connect_info where sid = sys_context('USERENV', 'SID')""")
v = cursor.fetchall()
print(v)

[sobayed]

Thanks for the quick reply and the link to the comment (I searched for similar issues but didn't come across the one you linked)!

If I set encoding to 'UTF-8' I get:

[('Client: AL32UTF8',), ('DB: AL32UTF8',)]

And when using 'utf8':

[('Client: UTF8',), ('DB: AL32UTF8',)]

I think my confusion stems from the fact that 'UTF8' and 'UTF-8' are used interchangeably almost everywhere in Python. I understand that Oracle has a somewhat "complicated" history in dealing with Unicode but the way those strings are currently handled seems counterintuitive. Also, I'm aware of Oracle's list of character sets and also Python's set of encodings. What would help my understanding of their relationship in cx_Oracle is some kind of mapping in the form of:

Encoding parameter string --> Oracle character set + Python encoding

[anthony-tuininga]

As Chris noted, cx_Oracle uses the IANA encoding name if it exists and, if not, attempts to use the Oracle character set name. And "UTF8" is not a valid IANA encoding name, even if Python is "kind" and lets you use it as a synonym for the correct name "UTF-8"! And to top it all off the name "UTF8" is the name given by Oracle for the encoding "CESU-8". As you noted this is historical and unfortunate. The Oracle character set name for "UTF-8" is "AL32UTF8". Perhaps it might make sense to drop any attempt to use Oracle character set names since there are standard names that most Python developers would be familiar with anyway. At the very least I can update the documentation to include the link Chris provided.

Just to be clear, the "encoding" and "nencoding" attributes on the connection are always valid IANA encoding names that Python understands. The Oracle character set names are simply the names that Oracle has given but there are equivalent IANA names. Unfortunately, it would seem that the page Chris linked doesn't provide the cross reference between the two. @cjbj is there such a page available? This one provides a similar method in PL/SQL that cx_Oracle is using to perform the mapping. You can use it as follows to determine the names that will be usable:

SELECT
    VALUE AS ORACLE_CS_NAME,
    UTL_I18N.MAP_CHARSET(VALUE) AS IANA_VALUE
FROM V$NLS_VALID_VALUES
WHERE parameter = 'CHARACTERSET'
  and UTL_I18N.MAP_CHARSET(VALUE) is not null
ORDER BY IANA_VALUE, VALUE

Of course this will show you that the Oracle character set name "UTF8" maps to "UTF-8" -- but it doesn't really do so. Certain characters will be mangled. Thankfully these are less commonly used characters!

[cjbj]

Since there is a fallback to NLS_LANG, I would still allow Oracle names to be used in the encoding params for general consistency.

[anthony-tuininga]

Ah yes, good point.

[sobayed]

Is it even possible to correctly decode Oracle's "UTF8" (CESU-8) using Python3 without having access to the raw bytes (i.e. when using e.g. cursor.fetchone())? Like you mentioned there are some characters which cause issues when trying to decode these values using "regular" UTF-8 logic. Or in other words: Is there any scenario where one would actually want to receive "UTF8" encoded strings when Python3 refuses to decode them altogether in case of those problematic characters?

[anthony-tuininga]

As far as I understand, CESU-8 handles supplemental characters differently but the rest are all handled the same way. See here. In general it should not be used, however, as it is not part of the standard. For an example of characters that will not work you can see the test "testSupplmentalCharacters" in the cx_Oracle test suite (StringVar.py).

[anthony-tuininga]

Assuming all is well due to lack of activity.