CODEWITHSUNDEEP
databaseoracle-databasecharacter-encodingsqlplus
Duc Bui
Duc Bui

Reputation: 33

Japanese Character in Oracle Database not displayed properly

I recently created an Oracle DB with JA16SJIS Character Set.

And then I try to insert some data include Japanese characters using SQL*Plus running an external SQL file. The file is encoded in Shift-JIS (and I can see Japanese characters properly in the file using notepad++).

Inserting was success but when I select the data (using SQL*Plus), Japanese characters are not displayed properly (like some alphabet characters with some question marks).

Even when I use SQL Developer to view the data, Japanese characters still unreadable.

And I'm using Window 7 Professional SP1, Oracle Database 11g R2, system locale set to Japan as well.

Upvotes: 0

Views: 5567

Answers (2)

Nicolas
Nicolas

Reputation: 933

First, you should try to insert some text directly from SQLDeveloper data view. That should work no matter what, so you can use it to check your imports.

Then before you connect with SQL*Plus you must specify what you're going to send by setting or changing the value of environment variable NLS_LANG.

NSL_LANG=ENGLISH_FRANCE.JA16SJIS

The syntax will depend on your OS. The only important part is the last one JA16SJIS which means Shift-Jis as you already know.

You can then connect with SQL*Plus and import your file.

Note that the encoding that you specify must match the encoding of your file but not necessarily the encoding of the base as Oracle will do a conversion if necessary. So you could have your base in UTF8 and it would still work (because UTF8 can hold japanese characters).

Upvotes: 1

Rene
Rene

Reputation: 10541

In these cases the first thing I do is to have a look at what byte values are stored in the database. You can use the dump function for that.

select dump(<column>) from <table>

If you know what byte values your characters should have you can check if the correct values are in your table.

Upvotes: 0

Related Questions