Loading Arabic Data Into Oracle Database using SQLCL

This is a follow up post on my previous post:
https://ahmedfattah.com/2019/12/15/loading-data-into-oracle-database-from-text-csv-files/
If you read this post, you find that we uploaded some data , part of it was Arabic data, and although it was loaded successfully, the Arabic data was corrupted (appeared as ???????).

In this post, we see how we can configure sqlcl to load and display arabic data.

Environment:

Windows 10
Oracle Database 12.1.0.2
sqlcl version 4.2.0.17.097.0719

Key Points to Review:

The key points that you should double check when you face this issue are:

• The encoding of the CSV file that you are uploading.
• The code page of the terminal that you are using.
• The encoding of the sqlcl session.
• The font of the terminal session.

I tried many combinations of the above parameters, I’ll consider below only the working combinations:

Some Remarks before we start:

• The encoding of csv files – that contain only english data- created by Notepad or Excel is “ANSI“.
• The encoding of csv files – that contain English and/or Arabic data- created by Notepad is “UTF-8 without BOM”
• The encoding of csv files – that contain English and/or Arabic data- created by Excel is “ANSI“, even if you modified it by Notepad later.
• Make sure that the encoding of the csv file that your uploading is “UTF-8 without BOM” , if not, then you can use a program like NOTEPAD++ to change it.

Option #1:

The employees.csv file encoding is “UTF-8 without BOM”
I’ll change the code page of the Windows Command terminal to UTF-8.

Ill leave the default terminal font “Courier New”:

Then I’ll run the sqlcl without any further special configuration and upload the data:

If you displayed it from SQL Developer:

Note that , I used the sql command to launch sqlcl. If you use sql.bat instead, then in this case you don’t need to change the encoding of the COmmand Terminal, as this batch script does this for you automatically.

Option #2:

In this option, I will upload the same employees.csv file with encoding “UTF-8 without BOM”
I’ll change the command window code page to 1256, which supports English/Arabic data in Windows and Linux.

Please note that in this case, if you tried to change the font to any True Type font, it will be changed automatically back to ‘Courier New‘. It looks like there is a link between the terminal code page and the relevant available fonts for it.

You will notice that the data is loaded successfully, although the arabic data is not displayed properly, but we can validate it from SQL Developer:

Please note that as sqlcl is a java program, so, it doesn’t consider environmental variables, like NLS_LANG.

So, I may recommend to consider the first configuration options to load and display arabic data using sqlcl.

Thanks

Ahmed