varchar2转化nvarchar2类型乱码

开发人员有个需求，需将一个字符串，转成nvarchar2类型。但是测试是乱码，例句如下：SQLgt; select(selectutl_raw.cast_to_nvar

开发人员有个需求，需将一个字符串，，转成nvarchar2类型。但是测试是乱码，例句如下：

SQL> select(selectutl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw('i am nvarchar2string')) from dual) from dual;

(SELECTUTL_RAW.CAST_TO_NVARCHAR2(UTL_RAW.CAST_TO_RAW('IAMNVARCHAR2STRING'))FROMDUAL)
------------------------------------------------------------------------------------------------------------------------------------
??????????

 

cast_to_raw函数按照缺省字符集将varchar2字符串转换为RAW，而cast_to_nvarchar2是将RAW转换成Nvarchar2类型，nvarchar2依据所选的国家字符集。

SQL> col value formata22               
SQL> select * from nls_database_parameters
  2  where parameterin('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET');

PARAMETER                    VALUE
------------------------------ ----------------------
NLS_NCHAR_CHARACTERSET        AL16UTF16
NLS_CHARACTERSET              ZHS16GBK

数据库缺省字符集ZHS16GBK，而国家字符集为AL16UTF16，将ZHS16GBK的RAW用AL16UTF16显示，会产生乱码。如果不产生乱码可以将原字符串'iam nvarchar2 string'转换国家字符集，输出类型为NVARCHAR2。

SQL> select (selectutl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw(translate('i amnvarchar2 string' using nchar_cs))) from dual) from dual;

(SELECTUTL_RAW.CAST_TO_NVARCHAR2(UTL_RAW.CAST_TO_RAW(TRANSLATE('IAMNVARCHAR2STRING'USINGNCHAR_CS)))FROMDUAL)
------------------------------------------------------------------------------------------------------------------------------------
i am nvarchar2 string

SQL>

问题解决。