Oracle Database Discussions

INSERT INTO TT VALUES(n'您好');

If the problem still remains, post the result of

SELECT dump(name, 1010) FROM  TT

Hi Chris,

SELECT dump(name, 1010) FROM  TT;

Typ=1 Len=4 CharacterSet=AL16UTF16: 0,191,0,191

Typ=1 Len=4 CharacterSet=AL16UTF16: 0,191,0,191

Ok should be something like

Typ=1 Len=4 CharacterSet=AL16UTF16: 96,168,89,125

What client do you use and how does the client connect to the db (java, oci ...)

What is the setting of NLS_LANG in your client environment?

iam using sql develoer chris. nam altering session level NLS_LANGUAGE to chinese

Does the database characterset support Chinese ?

Select * from NLS_DATABASE_PARAMETERS;

Srini Chavali-Oracle wrote:

Does the database characterset support Chinese ?

Select * from NLS_DATABASE_PARAMETERS;

Is there any information missing in the dump of the nvarchr2 column?

Venkadesh Sivalingam-Oracle wrote:

iam using sql develoer chris. nam altering session level NLS_LANGUAGE to chinese

I ask about the NLS_LANG setting of your client.

Verify the encoding in sqldeveloper:

Tools --> Preferences --> Environment --> Encoding -->  should be UTF-8

Take a look under Help -> Info -> Properties in sql developer:

If there isnt an entry like

oracle.jdbc.convertNcharLiterals="true"

you may try to add it.

Not sure how this works. Most likely there is a properties-file in you r sql-developer installation in a top level directory which contains those entries.

The file is located in \sqldeveloper\bin

I added the line below. Try if it helps with your issue (it's assumed that sqldeveloper connects using the jdbc-driver)

AddVMOption -Doracle.jdbc.convertNcharLiterals=true

First, you committed the crime of not revealing any software version in your original question, neither that of the RDBMS nor that of SQL Developer ;-)

You cannot insert Chinese characters directly, because your database character set does not support Chinese (most probably). A statement in the form INSERT INTO TT VALUES('您好'); undergoes character set conversion to the database character set before it is even parsed. Therefore, the Chinese characters in the literal are lost before they are parsed and inserted into the table. You can check this in the SQL trace, if you want.

The problem may be solved in one of the following ways:

1. Migrate the database character set to AL32UTF8 and forget the NVARCHAR2 data type altogether. This is the recommended though most complex approach.

2. Use UNISTR to encode and insert data. This method is a bit cumbersome as you have to find the Unicode code of each character. It is most suitable for scripts as it works independently of any client or database configuration.

3. Use the Data tab in the table editor in SQL Developer to insert data. SQL Developer (at least the one coming with Oracle Database 11.2) automatically sets everything as required to avoid conversion of data to the database character set for NVARCHAR2 columns.

4. Add the line:

  AddVMOption -Doracle.jdbc.convertNcharLiterals=true

to the file \SQLDeveloper\sqldeveloper\bin\sqldeveloper.conf and use the INSERT statements of the form INSERT INTO TT VALUES(N'您好'); This will make the JDBC driver encode the N'text' literals in UNISTR-compatible way, so that the statement text contains only ASCII characters, before sending the statement to the database. If you use JDBC 12.1, you do not have to do this anymore, because this property is on by default (see bug #14060598).

As you work for Oracle, you can contact me directly with further globalization questions.

Thanks,

Sergiusz

Hi Srini,

Chris, it's already UTF-8 in sql developer

i am using sql developer version - Version 4.0.3.16

and db version 11.2.0.2.0.

i added AddVMOption -Doracle.jdbc.convertNcharLiterals=true but still not working

how to Migrate the database character set to AL32UTF8 ?

Venkadesh Sivalingam-Oracle wrote:

i am using sql developer version - Version 4.0.3.16
and db version 11.2.0.2.0.
i added AddVMOption -Doracle.jdbc.convertNcharLiterals=true but still not working 

That's wha ti proposed, but it is adressed to the java jdbc driver, so confirme that sql develepoer use it for the database connection.

Otherwise you have to set convertNcharLiterals=true as an environment variable (see globalization document in the oracle docs on this).

Did you notice the leading n in the sql statement i gave you in my first post?

INSERT INTO TT VALUES(n'您好');

It only works if you use this to indicate the n-varchar.

For character set migration to AL32UTF8, see http://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/overview/index.html, especially the DMU documentation.

As for "but still not working", describe the exact steps you execute to arrive at this conclusion. Also, use the About dialog box in SQL Developer to check if the property oracle.jdbc.convertNcharLiterals=true is really recognized by the SQL Developer. Double-check the property name spelling including proper letter case.

Thanks,

Sergiusz