CODEWITHSUNDEEP
pythonoracle-databaseencodingutf-8cx-oracle
asha sasidharan
asha sasidharan

Reputation: 21

Decoding error while querying using Python cx_Oracle library

I am getting decoding error while using pd.read_sql.I am querying Oracle DB , and using cx_oracle library.

I have tried passing the encoding parameter in the Oracle Connection String as below.

cx_oracle.connect(user=user_name, password=pwd, dsn=dsn_tns,encoding="UTF-8")

The encoding options i have tried and error i am getting everytime pd.read_sql runs is as below:

  1. With encoding = 'UTF-8', error is utf-8' codec can't decode byte 0xc3 in position 34: unexpected end of data

  2. With encoding="UTF-8",nencoding="UTF-8", error is utf-8' codec can't decode byte 0xc3 in position 34: unexpected end of data With

  3. With encoding="UTF-16", nencoding="UTF-16", error is ORA-29275: partial multibyte character

The NLS_CHARACTERSET is AL32UTF8.

Anyone who has faced this issue and resolved, please suggest.

Thanks

Upvotes: 1

Views: 1852

Answers (2)

Amit Naidu
Amit Naidu

Reputation: 2638

You first need to determine the character set that your server is set to:

SELECT value
FROM nls_database_parameters
WHERE parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Then set the encoding and nencoding parameters of .connect() to match client to the server. ( AL32UTF8 on server matches UTF-8 on client)

If your columns are of types nvarchar, nclob etc. then you need to use the nencoding parameter as well. But you did not post the column datatypes nor your query.

cx_oracle.connect(user=user_name, password=pwd, dsn=dsn_tns, encoding="UTF-8", nencoding="UTF-8")

If your server really is AL32UTF8 and cx_oracle still gives you a decode error with encoding set to UTF-8, then as the other answer says, you have corrupt data. Test by querying a different, smaller set of rows.

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10506

If you have corrupt data try something like suggested in the cx_Oracle doc Querying Corrupt Data:

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.STRING:
        return cursor.var(defaultType, size, arraysize=cursor.arraysize,
                encodingErrors="replace")

cursor.outputtypehandler = OutputTypeHandler

cursor.execute("select column1, column2 from SomeTableWithBadData")

Upvotes: 2

Related Questions