How to Load file from one Character Set to EBS in UTF8

It a very common to load file (data) from one character set to another character set.

Here I will give u an example:

We have created a csv file from a database which has got following NLS properties:

NLS_LANG=American_America.WE8ISO8859P1

Now we have to load the file in EBS using concurrent manager and the NLS details of the EBS are as follows

NLS_LANG=American_America.UTF8

If the source file has got some special characters (Western European characters), you will find that complete file is not loaded in EBS as the file generated is not in UTF8 character set.

Errors u may get are

Record 144: Rejected - Error on table "GL"."GL_INTERFACE", column REFERENCE10.
Multibyte character error.
Record 145: Rejected - Error on table "GL"."GL_INTERFACE", column REFERENCE10.
Multibyte character error.

Table "GL"."GL_INTERFACE":
11968 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

To resolve this issue, we have to do the following:

After the file is created and transferred to EBS server, it has to be converted to UTF8 character set before loading in EBS.

How to convert the Character set of a file

adncnv

e.g. to convert a file from WE8ISO8859P1 to UTF8:

$AD_TOP/bin/adncnv AP_loaddata1_f_we8.dat WE8ISO8859P1 AP_loaddata1_f.dat UTF8

(this converts the AP_loaddata1_f_we8.dat file from Western European character set to UTF8 character set ).

You should then copy the resulting file to the directory from where you intend to run the sql*loader utility.

More details can be found in Metalink Note 135691.1