Question

在oracle中使用utl_raw.cast_to_varchar2，将raw转换为varchar类型，出现乱码，是怎么回事啊？

这是语句：select utl_raw.cast_to_varchar2('013ADECC074D4AA11346A2580E423FE0') as luanma from raw_test t;

出现这种乱码，这怎么解决啊！！！！

Answer 1

字符集问题 你的PL/SQL DEV的字符集不对

UTL_RAW.CAST_TO_VARCHAR2 converts a raw value into a value of data type VARCHAR2 with the same number of data bytes. The result is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type VARCHAR2.

Syntax result := UTL_RAW.CAST_TO_VARCHAR2(input);
where:
resultis the output value of the function. It is data type VARCHAR2. The value is null if input is null.inputis the input value of datatype RAW to convert to datatype VARCHAR2.

==》正常的例子
问问maclean
SQL> select UTL_RAW.CAST_TO_RAW('问问maclean') from dual;
UTL_RAW.CAST_TO_RAW('问问MACLEAN')--------------------------------------------------------------------------------E997AEE997AE6D61636C65616E
SQL> select utl_raw.cast_to_varchar2('E997AEE997AE6D61636C65616E') as maclean from dual;
MACLEAN--------------------------------------------------------------------------------问问maclean

==》 字符集不对的例子

C:\Users\xiangbli>set nls_lang=american_america.US7ASCII
C:\Users\xiangbli>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 23 21:32:27 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select utl_raw.cast_to_varchar2('E997AEE997AE6D61636C65616E') as maclean from dual;
MACLEAN--------------------------------------------------------------------------------??maclean

==》出现了乱码

追问

那我这个问题怎么解决呢？

追答

一、如果您使用PL/SQL DEVELOPER（版本不限）
1、影响PL/SQL DEVELOPER字符集显示的要素
1.1 OS系统当前字符集

936表示操作系统当前字符集为简体中文，同ZHS16GBK.
1.2 ORACLE注册表

该键值默认读取OS系统当前字符集，当然可以修改。
1.3 系统环境变量NLS_LANG

环境变量影响Dos、sql*plus、PL/SQL DEVELOPER等内容
1.4 ORACLE数据库字符设置

该值是在创建数据库时指定的
1.5 PL/SQL DEVELOPER菜单中TOOLS中首选项的'unicode support'

该选项的说明是：
Unicode enabled.
When selected, Unicode data will be fetched as such from the Oracle Server, and displayed as Unicode text. When disabled, Unicode data from the server will be converted to the character set of the Oracle Client, in accordance with the NLS_LANG key of the Oracle Home Registry. -----------from pl/sql developer help content

追问

现在我不知道“013ADECC074D4AA11346A2580E423FE0”，这个是什么编码格式，怎么能看出他是什么编码格式呢？
他代表的应该是一组中文。

追答

试试zhs16gbk