NCHAR Literal Replacement

Being part of a SQL or PL/SQL statement, the text of any literal, with or without the prefix N, is encoded in the same character set as the rest of the statement. On the client side, the statement is in the client character set, determined by the character set defined in the NLS_LANG parameter. On the server side the statement is in the database character set.

When the SQL or PL/SQL statement is transferred from client to the database, its character set is converted accordingly. If the database character set does not contain all characters used in the text literals, the data is lost in this conversion. This affects NCHAR string literals more than the CHAR text literals, this is because the N' literals is designed to be independent of the database character set, and it should be able to include any data that the client character set allows.

To avoid data loss during conversion to an incompatible database character set, you can activate the NCHAR literal replacement functionality. It transparently replaces the N' literals on the client side into an internal format, the database then decodes this to Unicode when the statement is executed. You can set the client environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE to enable this functionality. By default, the functionality is switched off to maintain backward compatibility.