Home > oracle, SQL Loader > Wrong characters using SQL Loader against an UTF8 database

Wrong characters using SQL Loader against an UTF8 database

I have to load periodically data with SQL Loader. The source data is generated in a Windows environment, and I load the data in our servers (linux-based).

I have problems uploading special characters (Ñ, Ç, Á, …) because of the different NLS_LANG of the source data (ISO-8859, WE8ISO8859P1) and the NLS_LANG of the database (SPANISH_SPAIN.UTF8).

I’ve tried to convert the source data to UTF8 with iconv, but I still get wrong data.

Trying to specify the NLS_LANG in the control file does not work:


LOAD DATA
CHARACTERSET UTF8
INFILE ...

So finally, the solution that worked for me is to export NLS_LANG before uploading the data with SQL Loader:


export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1

sqlldr userid=user@database control=control_file.ctl log=log_file.log bad=bad_records.bad direct=true

And I get the correct data.

A few and interesting references about SQL Loader:

http://www.orafaq.com/wiki/SQL*Loader_FAQ

http://www.akadia.com/services/ora_sql_loader_utf8.html

Advertisements
  1. Artuso
    May 30, 2014 at 19:57

    Great Article. It really worked for me the solution disclosed. I changed the NLS_LANG and was okay!

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: