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.

ORA-01704: String Literal Too Long. Cause: String Literal is Longer Than 4000 Characters

A text literal can have a maximum length of 4000 bytes.  In order to update a column greater than 4K, bind variables must be used and in the case of lobs if the data set is greater than 32k, use DBMS_LOB for piece wise manipulation.