Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, October 20, 2014

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

2 comments:

Trilok Singh said...
This comment has been removed by the author.
Trilok Singh said...

very clear & prcise info. Thank you.