23.1.1.Database Character Set

Data fetched from and sent to Oracle Database will be mapped between thedatabase character setand the “Oracle client” character set of the Oracle Client libraries used bypython-oracledb. If data cannot be correctly mapped between client and servercharacter sets, then it may be corrupted or queries may fail with“codeccan’t decode byte”.

All database character sets are supported by the python-oracledb.

To find the database character set, execute the query:

SELECTvalueASdb_charsetFROMnls_database_parametersWHEREparameter='NLS_CHARACTERSET';

23.1.2.Database National Character Set

For the secondarynational character setused for NCHAR, NVARCHAR2, and NCLOB data types:

• AL16UTF16 is supported by both the python-oracledb Thin and Thick modes

• UTF8 is not supported by the python-oracledb Thin mode

To find the database’s national character set, execute the query:

SELECTvalueASdb_ncharsetFROMnls_database_parametersWHEREparameter='NLS_NCHAR_CHARACTERSET';

23.1.3.Setting the Client Character Set

In python-oracledb, the encoding used for all character data is “UTF-8”. Olderversions of the driver allowedencoding andnencoding parameters to bepassed to theoracledb.connect() andoracledb.create_pool() methodsbut these parameters are now desupported.

23.2.1.Finding the Time Zone Files in Use

You can find the time zone file used by the database itself by executing aquery, for example:

SQL>select*fromv$timezone_file;FILENAMEVERSIONCON_ID-------------------- ---------- ----------timezlrg_43.dat430

The time zone files on the client side can be shown by running the utilitygenezi-v. In Instant Client, this is in the Basic and Basic Lightpackages. The output will be like:

$ genezi -v. . .TIMEZONE INFORMATION--------------------Operating in Instant Client mode.Small timezone file = /opt/oracle/instantclient/oracore/zoneinfo/timezone_43.datLarge timezone file = /opt/oracle/instantclient/oracore/zoneinfo/timezlrg_43.dat

With Instant Client, the paths refer to a virtual file system in the Oraclelibraries. These files are not present on the operating system file system.

The larger filetimezlrg_<n>.dat contains all time zone information. Thisis the file used by default. The smallertimezone_<n>.dat file containsonly the most commonly used time zones.

The filenames shows the version of the time zone files, in this example it isversion 43.

The Oracle Database documentation contains more information about time zonefiles, seeChoosing a Time Zone File.

23.2.2.Changing the Oracle Client Time Zone File

You can get updated time zone files from a full Oracle Database installation,or by downloading a patch fromOracle Support.For use with Instant Client, unzip the patch and copy the necessary files:installing the patch itself will not work.

Using a New Time Zone File in Instant Client

From Oracle Instant Client 12.2, you can use an external time zone file,allowing you to update time zone information without updating the completeInstant Client installation. Changing the file in earlier versions of InstantClient is not possible.

To change the time zone file, do one of the following:

• Create a subdirectoryoracore/zoneinfo under the Instant Clientdirectory and move the file into it. Then setORA_TZFILE to the filename, without any absolute or relative directory prefix prefix. Forexample, if Instant Client is in/opt/oracle/instantclient:

  mkdir-p/opt/oracle/instantclient/oracore/zoneinfocptimezone_43.dat/opt/oracle/instantclient/oracore/zoneinfo/exportORA_TZFILE=timezone_43.dat

• Alternatively, from Oracle Instant Client 19.18, you can place the externaltime zone file in any directory and then set theORA_TZFILE environmentvariable to the absolute path of the file. For example:

  mkdir-p/opt/oracle/myconfigcptimezone_43.dat/opt/oracle/myconfig/exportORA_TZFILE=/opt/oracle/myconfig/timezone_43.dat

After installing a new client time zone file, rungenezi-v again to checkif it is readable.

Using the Embedded Small Time Zone File in Instant Client

By default, Instant Client uses its larger embeddedtimezlrg_<n>.dat file.If you want to use the smaller embeddedtimezone_<n>.dat file, then set theORA_TZFILE environment variable to the name of the file without anyabsolute or relative directory prefix. For example:

exportORA_TZFILE=timezone_43.dat

Using a New Time Zone File in a Full Oracle Client

If python-oracledb Thick mode is using Oracle Client libraries from a fullOracle Client software installation (such as installed with Oracle’s GUIinstaller), and you want to use a non-default time zone file, then setORA_TZFILE to the file name with an absolute path directory prefix. Forexample:

exportORA_TZFILE=/opt/oracle/myconfig/timezone_43.dat

This also works if python-oracledb Thick mode is using libraries from an OracleDatabase installation.

23.3.1.Thick Mode Oracle Database National Language Support (NLS)

The python-oracledb Thick mode uses Oracle Database’s National Language Support(NLS) functionality to assist in globalizing applications, for example toconvert numbers and dates to strings in the locale specific format.

You can use theNLS_LANG environment variable to set the language andterritory used by the Oracle Client libraries. For example, on Linux you couldset:

exportNLS_LANG=JAPANESE_JAPAN

The language (“JAPANESE” in this example) specifies conventions such as thelanguage used for Oracle Database messages, sorting, day names, and monthnames. The territory (“JAPAN”) specifies conventions such as the default date,monetary, and numeric formats. If the language is not specified, then the valuedefaults to AMERICAN. If the territory is not specified, then the value isderived from the language value. SeeChoosing a Locale with the NLS_LANGEnvironment Variable

If theNLS_LANG environment variable is set in the application withos.environ['NLS_LANG'], it must be set before any connection pool iscreated, or before any standalone connections are created.

Any client character set value in theNLS_LANG variable, for exampleJAPANESE_JAPAN.JA16SJIS, is ignored by python-oracledb. SeeSetting theClient Character Set.

Other Oracle globalization variables, such asNLS_DATE_FORMAT can also beset to change the behavior of python-oracledb Thick, seeSetting NLS Parameters.

For more information, see theDatabase Globalization Support Guide.

23.3.2.Thin Mode Locale-aware Number and Date Conversions

In the python-oracledb Thin mode, output type handlers need to be used toperform date and number localizations. The examples below show a simpleconversion and also how the Python locale module can be used. Type handlerslike those below can also be used in python-oracledb Thick mode.

To convert numbers:

importlocaleimportoracledb# use this if the environment variable LANG is already set#locale.setlocale(locale.LC_ALL, '')# use this for programmatic setting of localelocale.setlocale(locale.LC_ALL,'de_DE.UTF-8')# simple naive conversiondeftype_handler1(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(oracledb.DB_TYPE_VARCHAR,arraysize=cursor.arraysize,outconverter=lambdav:v.replace('.',','))# locale conversiondeftype_handler2(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_NUMBER:returncursor.var(metadata.type_code,arraysize=cursor.arraysize,outconverter=lambdav:locale.format_string("%g",v))connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb")withconnection.cursor()ascursor:print("no type handler...")cursor.execute("select 2.5 from dual")forrowincursor:print(row)# gives 2.5print()print("with naive type handler...")connection.outputtypehandler=type_handler1cursor.execute("select 2.5 from dual")forrowincursor:print(row)# gives '2,5'print()print("with locale type handler...")connection.outputtypehandler=type_handler2cursor.execute("select 2.5 from dual")forrowincursor:print(row)# gives '2,5'print()

To convert dates:

importlocaleimportoracledb# use this if the environment variable LANG is already set#locale.setlocale(locale.LC_ALL, '')# use this for programmatic setting of localelocale.setlocale(locale.LC_ALL,'de_DE.UTF-8')locale_date_format=locale.nl_langinfo(locale.D_T_FMT)# simple naive conversiondeftype_handler3(cursor,metadata):ifmetadata.type_codeisoracledb.DB_TYPE_DATE:returncursor.var(metadata.type_code,arraysize=cursor.arraysize,outconverter=lambdav:v.strftime("%Y-%m-%d %H:%M:%S"))# locale conversiondeftype_handler4(cursor,name,default_type,size,precision,scale):ifmetadata.type_codeisoracledb.DB_TYPE_DATE:returncursor.var(metadata.type_code,arraysize=cursor.arraysize,outconverter=lambdav:v.strftime(locale_date_format))connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb")withconnection.cursor()ascursor:print("no type handler...")cursor.execute("select sysdate from dual")forrowincursor:print(row)# gives datetime.datetime(2021, 12, 15, 19, 49, 37)print()print("with naive type handler...")connection.outputtypehandler=type_handler3cursor.execute("select sysdate from dual")forrowincursor:print(row)# gives '2021-12-15 19:49:37'print()print("with locale type handler...")connection.outputtypehandler=type_handler4cursor.execute("select sysdate from dual")forrowincursor:print(row)# gives 'Mi 15 Dez 19:57:56 2021'print()

23.3.3.Inserting NVARCHAR2 and NCHAR Data

To bind NVARCHAR2 data, useCursor.setinputsizes() or create a bindvariable with the correct type by callingCursor.var(). This removesan internal character set conversion to the standardDatabase Character Setthat may corrupt data. By binding asoracledb.DB_TYPE_NVARCHAR, thedata is inserted directly as theDatabase National Character Set. Forexample, to insert into a table containing two NVARCHAR2 columns:

sql="insert into mytable values (:1, :2)"bv=['data1','data2']cursor.setinputsizes(oracledb.DB_TYPE_NVARCHAR,oracledb.DB_TYPE_NVARCHAR)cursor.execute(sql,bv)

For NCHAR data, bind asoracledb.DB_TYPE_NCHAR.