全角，半角互换

对于全角和半角互换，oracle 提供了两个函数to_multi_byte和to_single_byte函数，只是针对字母和数字有效，如：

SQL> select to_multi_byte('1234') from dual;

TO_MULTI_BYTE('1234')
---------------------
１２３４
SQL> select to_single_byte('１２３４') from dual;

TO_SINGLE_BYTE('１２３４')
--------------------------
1234

对于日文，中文以及其它语言字符，实现都不够，以下，实现了日文字符的全半角互换：

SELECT UTL_I18N.TRANSLITERATE(TO_MULTI_BYTE('abc123'), 'KANA_HIRAGANA') NAME --半角カタカナ・英数字-->全角ひらかな
,UTL_I18N.TRANSLITERATE(TO_MULTI_BYTE('1234'), 'KANA_FWKATAKANA') KANA --半角カタカナ-->全角カタカナ
,UTL_I18N.TRANSLITERATE(TO_MULTI_BYTE('1234'), 'KANA_HIRAGANA') SEIKINAME --半角カタカナ-->全角ひらかな
,UTL_I18N.TRANSLITERATE(TO_MULTI_BYTE('ひたかな'), 'HIRAGANA_HWKATAKANA') SEIKIKANA --全角ひらかな・カタカナ-->半角タカ
 from dual;

如果出现乱码问题，可以这么解决：

SQL> SELECT * FROM sys.props$;

NAME                           VALUE$                                                                           COMMENT$
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
DICT.BASE                      2                                                                                dictionary base tables version #
DEFAULT_TEMP_TABLESPACE        TEMP                                                                             Name of default temporary tablespace
DBTIMEZONE                     +08:00                                                                           DB time zone
NLS_LANGUAGE                   AMERICAN                                                                         Language
NLS_TERRITORY                  AMERICA                                                                          Territory
NLS_CURRENCY                   $                                                                                Local currency
NLS_ISO_CURRENCY               AMERICA                                                                          ISO currency
NLS_NUMERIC_CHARACTERS         .,                                                                               Numeric characters
NLS_CHARACTERSET               UTF8                                                                             Character set
NLS_CALENDAR                   GREGORIAN                                                                        Calendar system
NLS_DATE_FORMAT                DD-MON-RR                                                                        Date format
NLS_DATE_LANGUAGE              AMERICAN                                                                         Date language
NLS_SORT                       BINARY                                                                           Linguistic definition
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                                   Time format
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                                         Time stamp format
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                                               Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                                     Timestamp with timezone format
NLS_DUAL_CURRENCY              $                                                                                Dual currency symbol
NLS_COMP                       BINARY                                                                           NLS comparison
NLS_LENGTH_SEMANTICS           BYTE                                                                             NLS length semantics

NAME                           VALUE$                                                                           COMMENT$
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP            FALSE                                                                            NLS conversion exception
[color=red][b]NLS_NCHAR_CHARACTERSET         AL16UTF16  [/b] [/color]                                                                     NCHAR Character set
NLS_RDBMS_VERSION              10.2.0.4.0                                                                       RDBMS version for NLS parameters
GLOBAL_DB_NAME                 133g5                                                                           Global database name
EXPORT_VIEWS_VERSION           8                                                                                Export views revision #
DEFAULT_TBS_TYPE               SMALLFILE                                                                        Default tablespace type
WORKLOAD_CAPTURE_MODE                                                                                           CAPTURE implies workload capture is in progress
DEFAULT_PERMANENT_TABLESPACE   USERS                                                                            Default Permanent Tablespace ID

28 rows selected

然后更新语言信息即可：

update   sys.props$   set   value$=''JA16SJIS''   WHERE   name   =   ''NLS_CHARACTERSET'';