Home » SQL & PL/SQL » SQL & PL/SQL » Mysterious where clause with japanese (PL/SQL Develope, Version 7.1.0.1337, Windows XP Professional 5.1 Build 2600 (Service Pack 2))
icon5.gif  Mysterious where clause with japanese [message #353175] Sun, 12 October 2008 04:26 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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!
Re: Mysterious where clause with japanese [message #353313 is a reply to message #353207] Mon, 13 October 2008 05:28 Go to previous message
muya05
Messages: 3
Registered: October 2008
Junior Member
thanks all, I got it.
simple edit registry
[hkey_local_machine\software\oracle] and
[hkey_local_machine\software\oracle\home0],
change both of the key NLS_LANG from *_*.* to *_*.UTF8

special thanks for: hijack
Previous Topic: how to write in appned mode thru DBMS_XSLPROCESSOR.CLOB2FILE
Next Topic: arabic text in where condition
Goto Forum:
  


Current Time: Fri Dec 02 18:27:41 CST 2016

Total time taken to generate the page: 0.15296 seconds