How to load flat files containing Arabic (any non-English) characters using SQLLDR

When you need to load non-ASCII data from a flat file into an Oracle database, the primary tool of choice is SQL*Loader, but unfortunately it does not automatically recognize the character encoding scheme of non-ASCII text (Arabic) files, you have to specify the correct encoding for the flat file in order to ensure a successful load. For that purpose, NLS_LANG needs to be set before launching SQL*Loader. If encoding type of the flat file is unknown, unfortunately there is no 100% reliable way to determine what encoding is used in that text file.

Example:

When loading a data file containing Arabic characters created under Windows to an Oracle database, the following environment variable should be set: 

C:\> set nls_lang=AMERICAN_AMERICA.AR8MSWIN1256

C:\>sqlldr testbkp/Testbkp@tstdbt11 control=loader.ctl
C:\>sqlldr testbkp/Testbkp@tstdbt11 control=loader.ctl errors=99999999 rows=1000 direct=true


loader.ctl

load data
infile 'C:\temp\MESSAGE_DETAIL.csv'
APPEND
into table TST_P_ELIGIBLITY_MSG
fields terminated by "," optionally enclosed by '"'
TRAILING nullcols
(NIN CHAR,
MESSAGE_DETAIL CHAR(50000) optionally ENCLOSED BY '<' AND '>',
CYCLE_ID integer)

Table to be loaded:

CREATE TABLE TESTBKP.TST_P_ELIGIBLITY_MSG
(
NIN NUMBER(10) NOT NULL,
MESSAGE_DETAIL CLOB,
CYCLE_ID INTEGER NOT NULL
)

Ref: 100033.1