Home » SQL & PL/SQL » SQL & PL/SQL » Spool Japanese Characters (Oracle 11g)
Spool Japanese Characters [message #671000] Thu, 09 August 2018 03:14 Go to previous message
pratyush_biswas
Messages: 3
Registered: September 2017
Junior Member
Hi All,

The database I'm working on is 11g. I'm using SQLDEVELOPER.
My database tables contains english and japaense characters.
When I do a select statemtn on the table, the Japanese characters are visible in the result set.

But when I'm trying to spool the japanese characters they show up as "??????" but the english characters are fine.

My NLS parameters are

SELECT * FROM V$NLS_PARAMETERS
where PARAMETER in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER               VALUE       CON_ID
-------------------------------------------------
NLS_LANGUAGE	        AMERICAN	0
NLS_TERRITORY	        AMERICA	        0
NLS_CHARACTERSET	AL32UTF8	0

Sample:
SQL statement
-----------------
select CODE_LIST_ID, CODE, DISPLAY_VALUE
from <table_name>
where CODE_LIST_ID='ACTION_TAKEN';

CODE_LIST_ID   CODE     DISPLAY_VALUE
---------------------------------------
ACTION_TAKEN	1	変更無し
ACTION_TAKEN	2	減量
ACTION_TAKEN	3	増量


DBMS output
-------------
set serveroutput on 
declare

type typ_x is record  (CODE_LIST_ID   CODE_LIST_DETAIL_DISCRETE.CODE_LIST_ID%type,  CODE CODE_LIST_DETAIL_DISCRETE.CODE%type,
                       DISPLAY_VALUE  CODE_LIST_DETAIL_DISCRETE.DISPLAY_VALUE%type);
type x1 is table of typ_x;
x x1;

begin

select CODE_LIST_ID, CODE, DISPLAY_VALUE
bulk collect into x
from  CODE_LIST_DETAIL_DISCRETE
where CODE_LIST_ID='ACTION_TAKEN';

FOR y in x.FIRST..x.LAST
loop
dbms_output.put_line(x(y).CODE_LIST_ID||','|| x(y).CODE||','|| x(y).DISPLAY_VALUE);
end loop;

end;
/

Output
--------------
ACTION_TAKEN,1,????
ACTION_TAKEN,2,??
ACTION_TAKEN,3,??

I've tried updating these to the following, but still the result is same.
Japanese chacracters are being spooled as "?????"

Update Set 1
-----------
ALTER SESSION SET NLS_LANGUAGE='JAPANESE';
ALTER SESSION SET NLS_TERRITORY='JAPAN';
ALTER SESSION SET NLS_CHARACTERSET='UTF8';


Update Set 2
-----------
ALTER SESSIOn set NLS_LANG 'JAPANESE';
ALTER SESSIOn set NLS_TERRITORY='JAPAN';
ALTER SESSIOn set NLS_CHARACTERSET='JA16SJIS';
 
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: PL/SQL Collection
Next Topic: Dbms_metadat
Goto Forum:
  


Current Time: Thu Mar 28 11:22:55 CDT 2024