Inserting vietnamese characterset into nvarchar2/varchar without Prefix 'N' from client

Question

I am having a database(Oracle 11g) on windows whose NLS_CHARACTERSET value is WE8MSWIN1252, while the NLS_NCHAR_CHARACTERSET value is AL16UTF16.

Now, I have a table 'TEST_NOTE' whose column type is NVARCHAR2. While running the following insert statement:

insert into test_note values (n'Chào thế giới!')

The data gets inserted fine and I am able to fetch it properly.

Since I have to insert the data from a different client software(company's proprietary software), I am not able to append 'n' to the value the user enters.

Also, can I make do with VARCHAR2 instead of NVARCHAR, as I don't want to change the existing schema of the database in production?

My ideal solution will be using VARCHAR2 and inserting Vietnamese Characters without using 'n' as prefix.

EDIT:

I Tried the following on Windows 10:

C:\WINDOWS\system32>chcp
Active code page: 437

C:\WINDOWS\system32>set NLS_LANG =.AL32UTF8

C:\WINDOWS\system32>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 22 11:15:11 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect system as sysdba
Enter password:
Connected.

SQL> insert into ss_repo.test_note values ('abcs','Chào thế giới!');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from SS_REPO.TEST_NOTE;

SOEID                NOTE
-------------------- --------------------
ID17836              Chào th? gi?i!
s                    Chào th? gi?i!
abcs                 Chào th? gi?i!
ABCD                 Chαo th┐ gi┐i!

Or Can I do the same from SQL Developer? Will it be easy using that?

The client which will be used in production will be using JDBC JAR file OJDBC6.JAR

But for the time being I am trying to do using SQL Plus or SQL Developer.

I think you can simply skip the `n` prefix, Oracle will make an implicit cast to `NVARCHAR`. But ensure proper setting of `NLS_LANG` value. — Wernfried Domscheit, Feb 21 '17 at 12:19
Yes I tried doing that. But it's not working; garbage values are getting stored. NLS_LANGAUGE value is AMERICAN NLS_CHARACTERSET value is WE8MSWIN1252 NLS_NCHAR_CHARACTERSET value is AL16UTF16 — Siddharth Pathak, Feb 21 '17 at 12:31
I did not ask for `NLS_LANGUAGE` - I asked for `NLS_LANG` which is different, see http://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 Which application or driver do you use to insert the data. — Wernfried Domscheit, Feb 21 '17 at 16:14
Oh I see. Following the link you provided, there is no NLS_LANG parameter specified. I tried setting the NLS_LANG parameter using chcp and then inserting data through SQL Plus, but still its not working. `C:\WINDOWS\system32>set NLS_LANG = .AL32UTF8 SQL> select 'Chào thế giới!' from dual; 'CHàOTH?GI?I!'--------------Chào th? gi?i!` For the time being I am trying to insert from SQL PLUS to a NVARCHAR2 column type without prefix N — Siddharth Pathak, Feb 22 '17 at 06:00
Please edit your question with code instead of putting it as comment. You put a space in NLS_LANG value, this does not work. Also which chcp value did you use? — Wernfried Domscheit, Feb 22 '17 at 06:07

score 0 · Answer 1 · answered Feb 22 '17 at 09:07

Codepage 437 does not support any Vietnamese characters.

When you set set NLS_LANG =.AL32UTF8 then you have to execute chcp 65001 beforehand in order to change your codepage (and thus also character set of SQL*Plus) to UTF-8

However, using UTF-8 on Windows command line has some issues, see this discussion: https://community.oracle.com/thread/600575

You can also try Codepage 1258 which should work for Vietnamese:

C:\>chcp 1258
Active code page: 1258

C:\>set NLS_LANG =.VN8MSWIN1258

C:\>sqlplus ...

Inserting vietnamese characterset into nvarchar2/varchar without Prefix 'N' from client

1 Answers1