Use SQL*Loader to load unicode text file

I have a large text file that needs to be loaded into Oracle.  I'm planning to use SQL*Loader to load it.  Following is my command.

sqlldr userid=xxx/xxx@xxx control=test.ctl bad=test.bad data=test.data log=test.log

Open in new window


My control file looks like this

OPTIONS (
    ERRORS = 10000,
    PARALLEL = TRUE,
    DIRECT = TRUE,
    readsize=104857600,
    columnarrayrows=100000,
    streamsize=2560000,
    bindsize=2560000)
LOAD DATA APPEND INTO TABLE table
FIELDS TERMINATED BY '^A'
TRAILING NULLCOLS (
    [list_of_columns]
)

Open in new window


The result is there were a small number of rows got rejected.  I looked at the bad file and all of them are unicode text.

I'm just wondering what I should do in the control file so as to be able to load those unicode data as well.

Thanks.
thomaszhwangAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Here is where it is referenced in the sql loader docs:
http://docs.oracle.com/cd/E18283_01/server.112/e16536/ldr_concepts.htm

Stream Record Format
If the character set specified with the NLS_LANG parameter for your session is different from the character set of the data file, then character strings are converted to the character set of the data file. This is done before SQL*Loader checks for the default record terminator.


Setting it:
http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006280
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Can you provide some sample data?

Some 'good' and some 'bad'.
0
 
thomaszhwangAuthor Commented:
The data is pretty sensitive, so I cannot post them here, but I'm sure my description should be correct.  It's a problem w/ unicode characters.  Thanks.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Likely a database characterset conversion issue.

Maybe another Expert has a guess but without data, I don't.

If the database can support Unicode and you are loading this from a remote client, maybe all you need to do is set NLS_LANG to the correct value for the database you are using.

The online docs talk about setting this.
0
 
thomaszhwangAuthor Commented:
My database actually supports Unicode.  I can actually use another way to load the same data - I can create an external table pointing to the data file and do a INSERT ... SELECT * FROM external_table.  Everything will be loaded fine in this case.  This is why I'm thinking I forget some switch in the control file for SQL*Loader.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
External tables pretty much use the same sql loader engine.  It should almost be plug and play.

When you posted "userid=xxx/xxx@xxx"  The @xxx shows a remote database connection.

If the file is on the database server and you are using the same Oracle binaries, it shouldn't matter but you never know...

Try setting NLS_LANG.
0
 
thomaszhwangAuthor Commented:
Could you please tell me how to set NLS_LANG?  Thanks.
0
 
hinpongConnect With a Mentor Commented:
According to SQL Loader manual,  you can specifiy characterset=UTF16 to load unicode
0
 
thomaszhwangAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.