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