By default, in a SQL statement, the text of any literal is encoded in the same character set as the rest of the statement. The character set on the client is determined by the client character set defined in NLS_LANG. When the statement is executed, the character set on the client is converted to the character set on the database server.
Data in string literals is lost in the conversion if the character set on the database server does not contain the characters used on the client. NChar string literals are most affected by this issue because they are designed to be independent of the character set on the database server.
To avoid this data loss, set the NCharLiteral database parameter to ‘Yes”. This setting causes the Oracle client to encode all literals prefixed with N in the statement on the client with an internal format. The database server decodes the literals to Unicode when the statement is executed.
For example, when NCharLiteral is set to “Yes”, the string “some unicode data” in the following SQL statement is transferred from the client to the server with no data loss:
insert into table1 (id, ncharcol) values(1, N'some unicode data')
Oracle 10.2 or higher required
The NCharLiteral database parameter requires Oracle 10.2 or
higher on both the client and the database server.
For more information, see the description of NCharLiteral in the online Help.
