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 next 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';
Re: Spool Japanese Characters [message #671001 is a reply to message #671000] Thu, 09 August 2018 03:49 Go to previous messageGo to next message
John Watson
Messages: 7484
Registered: January 2010
Location: Global Village
Senior Member
You say the problem is with spool, but you haven't shown a spool. Using SQL*Plus, that would be something like this:
orclx>
orclx> spool d.txt
orclx> select * from dual;

D
-
X

orclx> spool off
orclx>
orclx> host type d.txt
orclx> select * from dual;

D                                                                                                          
-                                                                                                          
X                                                                                                          

orclx> spool off

orclx>
is that what you are doing?
Re: Spool Japanese Characters [message #671003 is a reply to message #671001] Thu, 09 August 2018 04:27 Go to previous messageGo to next message
pratyush_biswas
Messages: 3
Registered: September 2017
Junior Member
I'm trying to spool the contents of the Anonymous block.
I understand I made a mistake, I didnt add the spool command itself.

My main issue is that the dbms_output is returning as "????" for the japanese characters.

spool <filename.txt>

<anonymous block>

spool off;

[Updated on: Thu, 09 August 2018 04:29]

Report message to a moderator

Re: Spool Japanese Characters [message #671006 is a reply to message #671000] Thu, 09 August 2018 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The database I'm working on is 11g.
Quote:
SELECT * FROM V$NLS_PARAMETERS
where PARAMETER in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER               VALUE       CON_ID

11g does not have CON_ID column which was introduced in 12c.

Quote:
I've tried updating these to the following, but still the result is same.
SQL> ALTER SESSIOn set NLS_LANG 'JAPANESE';
ALTER SESSIOn set NLS_LANG 'JAPANESE'
*
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> ALTER SESSIOn set NLS_CHARACTERSET='JA16SJIS';
ALTER SESSIOn set NLS_CHARACTERSET='JA16SJIS'
*
ERROR at line 1:
ORA-00922: missing or invalid option

Even with the correct syntax for the first one:
SQL> ALTER SESSIOn set NLS_LANG='JAPANESE';
ALTER SESSIOn set NLS_LANG='JAPANESE'
*
ERROR at line 1:
ORA-00922: missing or invalid option

At least 3 lies in the post.

[Updated on: Thu, 09 August 2018 05:35]

Report message to a moderator

Re: Spool Japanese Characters [message #671031 is a reply to message #671006] Fri, 10 August 2018 07:05 Go to previous message
Bill B
Messages: 1759
Registered: December 2004
Senior Member
You are using the wrong value for NLS_LANG. See the following link for more information

https://docs.oracle.com/html/B13804_02/gblsupp.htm
Previous Topic: PL/SQL Collection
Next Topic: Dbms_metadat
Goto Forum:
  


Current Time: Wed Aug 15 23:09:46 CDT 2018