Oracle Unicode problem when using NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16, and ColdFusion as programming language

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 :)

Answers


First database stores values in utf-8 encoding, second in iso-8859-1(besides N-datatypes), however you are writing out both values in utf-8 so first one is alright, but second one is wrong decoded.


WE8ISO88591 encoding has a limited character set and can not store all unicode characters. Please refer to http://en.wikipedia.org/wiki/ISO/IEC_8859-1 for list of supported characters.


The first step is to check the NLS environment variables because they determine the conversion (if needed). Also check the contents for NLS_SESSION_PARAMETERS inside a coldfussion page .


The NVARCHAR2 thing combined with WE8ISO88591 has a nasty side effect if you use literals in queries. The whole query is converted through the NLS_CHARSET encoding, so if it cannot encode your characters you might be in trouble. But that should not happen with bound paramters.


Need Your Help

smartcard and p12 file creation

java cryptography smartcard

I'm trying to create a p12 file (pkcs12).

Add and Update Single Item in Sharepoint List via VBA

excel list vba sharepoint

I am trying to add and/or update single items in a sharepoint list via VBA and I found a similar question: