French characters are not retrieved correctly from oracle database

[arvindsam96]

while retrieving data from table french data is converted to unknown characters From Table: LE VILLAGE DU P? NO?(APP)-PIP PAKBASIC-AMQUI

Actual data: LE VILLAGE DU PÈRE NOËL (APP) -PIP PAKBASIC-AMQUI

i have tried setting NLS_LANG also import os os.environ["NLS_LANG"] = 'AMERICAN_AMERICA.WE8ISO8859P1' import cx_Oracle

but nothing seems to be working for me.

my NLS_DATABASE_PARAMETERS are [('NLS_RDBMS_VERSION', '12.1.0.2.0'), ('NLS_NCHAR_CONV_EXCP', 'FALSE'), ('NLS_LENGTH_SEMANTICS', 'CHAR'), ('NLS_COMP', 'BINARY'), ('NLS_DUAL_CURRENCY', '$'), ('NLS_TIMESTAMP_TZ_FORMAT', 'DD-MON-RR HH.MI.SSXFF AM TZR'), ('NLS_TIME_TZ_FORMAT', 'HH.MI.SSXFF AM TZR'), ('NLS_TIMESTAMP_FORMAT', 'DD-MON-RR HH.MI.SSXFF AM'), ('NLS_TIME_FORMAT', 'HH.MI.SSXFF AM'), ('NLS_SORT', 'BINARY'), ('NLS_DATE_LANGUAGE', 'AMERICAN'), ('NLS_DATE_FORMAT', 'DD-MON-RR'), ('NLS_CALENDAR', 'GREGORIAN'), ('NLS_NUMERIC_CHARACTERS', '.,'), ('NLS_NCHAR_CHARACTERSET', 'AL16UTF16'), ('NLS_CHARACTERSET', 'AL32UTF8'), ('NLS_ISO_CURRENCY', 'AMERICA'), ('NLS_CURRENCY', '$'), ('NLS_TERRITORY', 'AMERICA'), ('NLS_LANGUAGE', 'AMERICAN')]

Please help

[anthony-tuininga]

Please let us know what version of Python, cx_Oracle, Oracle Client and Oracle Database you are using. Please let us know the code you are using to retrieve the data. As mentioned earlier, please take a look at the documentation.

[arvindsam96]

Python 3.7.1 cx_Oracle 7.2.0 Oracle databse 12.1.0.2.0

i am using curosr.execute(query) to fetch data

[arvindsam96]

#----------------------------------------------------------------Modules
import os
os.environ["NLS_LANG"] = 'AMERICAN_AMERICA.WE8ISO8859P1'
import cx_Oracle
import logger
#-----------------------------------------------------------------------
# Create logger instance
log = logger.getlogger()
#-----------------------------------------------------------------------

def oraclesql(datasource, username, password):
    dsn_tns = ''
    conn = ''
    cursor = ''
    try:
        dsn_conn = datasource.split('/')
        # get host and port
        host_port = dsn_conn[0].split(':')
        log.info('connecting to hostname: {0}  port: {1}  service: {2}'\
                 .format(str(host_port[0]), host_port[1], str(dsn_conn[1])))
        # create dsn
        dsn_tns = cx_Oracle.makedsn(str(host_port[0]), host_port[1], service_name=str(dsn_conn[1]))
        conn = cx_Oracle.connect(user=str(username), password=str(password), dsn=dsn_tns)
        cursor = conn.cursor()
        log.info('connection encoding is: {}/{}'.format(conn.encoding, conn.nencoding))
        log.info('connected to DB version: {}'.format(conn.version))
    except Exception as oracle_error:
        content = ('oracle '+dsn_tns+' is unreachable, Error: '+ str(oracle_error)).strip()
        log.error(content)

[anthony-tuininga]

As an aside, there is no need to perform your own parsing of the host/port/service name. You can simply do

conn = cx_Oracle.connect(user, password, datasource)

So the value of conn.encoding is UTF-8? You still haven't shown the query you are executing, the table and the data that is within it. How did the data get into the table?

[arvindsam96]

Query i am executing: SELECT CHANNEL_NM, CHANNEL_PAK_NM, EXTRACTION_DT FROM STGADM03.PACK_CHANNEL_IPTV where ETL_BATCH_RUN_EVENT_KEY=349493

Data: [('LE VILLAGE DU P? NO?(APP)-PIP', 'EVERYTHING', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-AMQUI', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-BAIE COMEAU', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-GASPE', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-RIMOUSKI', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-RIVIERE-DU-LOUP', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-ST GEORGES', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-GASPE', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-RIMOUSKI', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-RIVIERE-DU-LOUP', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASELIMITEE-ST GEORGES', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-AMQUI', datetime.datetime(2019, 7, 1, 0, 0)), ('LE VILLAGE DU P? NO?(APP)-PIP', 'PAKBASIC-BAIE COMEAU', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'EVERYTHING', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'PAKESSENTTOPUP-AB', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'PAKESSENTTOPUP-BC', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'PAKSPORTS-AB', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'PAKSPORTS-BC', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'PAKSPORTSHD-AB', datetime.datetime(2019, 7, 1, 0, 0)), ('GOLF CHANNEL NG HD (HD)', 'ALACARTE-GOLF CHANNEL', datetime.datetime(2019, 7, 1, 0, 0))]

data is too much i am just showing some sample data

Also, I have no idea how the data get into the table, there's a possibility that the data is from some file used.

[anthony-tuininga]

In that case, create a table similar to the one you are querying but with only a single character column and then insert a single row from the problem table into the new one. Then provide the table structure and the data. It is possible that the data was not stored properly in the database. Have you tried examining the data with SQL*Plus? Make sure you set NLS_LANG to .AL32UTF8 before running SQL*Plus in order to ensure that the data will be displayed properly.

[arvindsam96]

Thanks @anthony-tuininga for yours time and help Problem was with the data stored in the database

[anthony-tuininga]

Glad you discovered the source of the issue and I hope you can correct the data!