title: Oracle Unicode problem when using NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16, and ColdFusion as programming language
body: <p>I have 2 Oracle 10g database, XE and Enterprise</p>
  
  <p><strong>XE</strong></p>
  
  <p><img src="https://i.stack.imgur.com/BcFkb.jpg" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/QmkR9.jpg" alt="enter image description here"></p>
  
  <p><strong>Enterprise</strong></p>
  
  <p><img src="https://i.stack.imgur.com/hIfvf.jpg" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/d6cxI.jpg" alt="enter image description here"></p>
  
  <p>and this are the data type I've use in the test table</p>
  
  <p><img src="https://i.stack.imgur.com/JCkfI.jpg" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/42GwQ.jpg" alt="enter image description here"></p>
  
  <p>and then I tried to test to insert some Unicode char from <a href="http://www.sustainablegis.com/unicode/" rel="noreferrer">http://www.sustainablegis.com/unicode/</a></p>
  
  <p>and the results are</p>
  
  <p><strong>XE</strong></p>
  
  <p><img src="https://i.stack.imgur.com/jezy6.jpg" alt="enter image description here"></p>
  
  <p><strong>Enterprise</strong></p>
  
  <p><img src="https://i.stack.imgur.com/OrNwL.jpg" alt="enter image description here"></p>
  
  <p>for this test, I use ColdFusion 9 developer edition</p>
  
  <pre><code>&lt;cfprocessingDirective pageencoding="utf-8"&gt; 
  &lt;cfset setEncoding("form","utf-8")&gt;
  
  &lt;form action="" method="post"&gt;
  Unicode : &lt;br&gt;
  &lt;textarea name="txaUnicode" id="txaUnicode" cols="50" rows="10"&gt;&lt;/textarea&gt;
  &lt;br&gt;&lt;br&gt;
  Language : &lt;br&gt;
  &lt;input type="Text" name="txtLanguage" id="txtLanguage"&gt;
  &lt;br&gt;&lt;br&gt;
  &lt;input type="Submit"&gt;
  &lt;/form&gt;
  
  &lt;cfset dsn = "theDSN"&gt;
  
  &lt;cfif StructKeyExists(FORM, "FIELDNAMES")&gt;
      &lt;cfquery name="qryInsert" datasource="#dsn#"&gt;
          INSERT INTO UNICODE
          (
              C_VARCHAR2,
              C_CHAR,
              C_CLOB,
              C_NVARCHAR2,
              LANGUAGE
          )
          VALUES
          (
              &lt;cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXAUNICODE#"&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_CHAR" value="#FORM.TXAUNICODE#"&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#FORM.TXAUNICODE#"&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXAUNICODE#"&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.TXTLANGUAGE#"&gt;
          )
      &lt;/cfquery&gt;
  &lt;/cfif&gt;
  
  &lt;cfquery name="qryUnicode" datasource="#dsn#"&gt;
      SELECT  *
      FROM    UNICODE
      ORDER BY    LANGUAGE
  &lt;/cfquery&gt;
  
  &lt;table border="1"&gt;
      &lt;thead&gt;
          &lt;tr&gt;
              &lt;th&gt;LANGUAGE&lt;/th&gt;
              &lt;th&gt;C_VARCHAR2&lt;/th&gt;
              &lt;th&gt;C_CHAR&lt;/th&gt;
              &lt;th&gt;C_CLOB&lt;/th&gt;
              &lt;th&gt;C_NVARCHAR2&lt;/th&gt;
          &lt;/tr&gt;
       &lt;/thead&gt;
       &lt;tbody&gt;
          &lt;cfoutput query="qryUnicode"&gt;
              &lt;tr&gt;
                  &lt;td&gt;#qryUnicode.LANGUAGE#&lt;/td&gt;
                  &lt;td&gt;#qryUnicode.C_VARCHAR2#&lt;/td&gt;
                  &lt;td&gt;#qryUnicode.C_CHAR#&lt;/td&gt;
                  &lt;td&gt;#qryUnicode.C_CLOB#&lt;/td&gt;
                  &lt;td&gt;#qryUnicode.C_NVARCHAR2#&lt;/td&gt;
              &lt;/tr&gt;
          &lt;/cfoutput&gt;
      &lt;/tbody&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p>from this guide <a href="http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch6unicode.htm#i1007297" rel="noreferrer">http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch6unicode.htm#i1007297</a> I think for my Enterprise database it should produce same thing as XE (at least for NVARCHAR2 column) since the <strong>typical solution</strong> from that guide said:</p>
  
  <ul>
  <li>Use NCHAR and NVARCHAR2 datatypes to store Unicode characters</li>
  <li>Keep WE8ISO8859P1 as the database character set</li>
  <li>Use AL16UTF16 as the national character set</li>
  </ul>
  
  <p>So, how to make it works too in my Enterprise database?</p>
  
  <p>Thank you
  :)</p>