Mysterious where clause with japanese [message #353175] |
Sun, 12 October 2008 04:26  |
muya05
Messages: 3 Registered: October 2008
|
Junior Member |
|
|
I create a table named ja_test with two fields(EXECUTABLE_NAME and DESCRIPTION), both of the type is varchar2, and there is only one record in the table, but the DESCRIPTION contains JAPANESE characters, when i use a where clause like this "where j.executable_name = 'XX00MRP0411C';", everything seems work fine, but when the where clase include JAPANESE characters
like this "j.description = '需要供給データ作成マネージャ';", even if the "j.description" is copied form database, no records returned:
SQL> select * from ja_test;
EXECUTABLE_NAME DESCRIPTION
----------------------------------------------------------------
XX00MRP0411C 需要供給データ作成マネージャ
SQL> select * from ja_test j where j.executable_name = 'XX00MRP0411C';
EXECUTABLE_NAME DESCRIPTION
------------------------------ ---------------------------------
XX00MRP0411C 需要供給データ作成マネージャ
SQL> select * from ja_test j where j.description = '需要供給データ作成マネージャ';
EXECUTABLE_NAME DESCRIPTION
------------------------------ ---------------------------------
SQL> desc ja_test;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- --------
EXECUTABLE_NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(240) Y
SQL> SELECT * FROM v$nls_parameters;
PARAMETER VALUE
----------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR
HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR
HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected
SQL>
thanks in advance!
|
|
|
Re: Mysterious where clause with japanese [message #353177 is a reply to message #353175] |
Sun, 12 October 2008 08:24   |
hijack
Messages: 4 Registered: October 2008
|
Junior Member |
|
|
FYI.
I tested your example,it'ok.
check my nls parameter,i think your problem can be cause by the nls_characterset.
SQL> create table ja_test(EXECUTABLE_NAME varchar2(30),DESCRIPTION varchar2(240));
Table created
SQL> insert into ja_test values('XX00MRP0411C','需要供給データ作成マネージャ');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from ja_test;
EXECUTABLE_NAME DESCRIPTION
------------------------------ ----------------------------------XX00MRP0411C 需要供給データ作成マネージャ
SQL> select * from ja_test where description='需要供給データ作成マネージャ';
EXECUTABLE_NAME DESCRIPTION
------------------------------ --------------------------------------------------------------------------------
XX00MRP0411C 需要供給データ作成マネージャ
SQL> select * from v$nls_parameters;
PARAMETER VALUE
----------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY RMB
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY RMB
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected
|
|
|
Re: Mysterious where clause with japanese [message #353207 is a reply to message #353177] |
Sun, 12 October 2008 20:08   |
muya05
Messages: 3 Registered: October 2008
|
Junior Member |
|
|
to hijack:
thanks for reply, I also think it's caused by character sets, but I can't modify the database setting, and I have tried to change my registry key "NLS_LANG" to *_*.UTF8 too, even though this still do not work, so I confused. Any prompt would be appreciated!
|
|
|
|