?
Solved

Oracle NLS

Posted on 2003-02-27
5
Medium Priority
?
4,107 Views
Last Modified: 2007-10-18
hi all,

scenario : i want to create a file which contains chinese chars of ZHS16CGB231280 charset. my oracle database has been created using UTF8 charset. i have the following column in my table which stores hexadecimal.

CHINESE_NAME VARCHAR2(1000)
------------
E5B9B8E7A68FE79A84E78CAA20E5B9B8E7A68FE79A84E78CAA20

what i would i want to do is to convert these hexadecimals into bytes and place it in a file.

my environment variable NLS_LANG has been set to AMERICAN_AMERICA.ZHS16CGB231280
i have tried using utl_file.put_line and utl_raw.cast_to_varchar2(CHINESE_NAME) to create the file using a PL/SQL stored procedure. But the characterset of the contents in the file is still UTF8 (which is the characterset of my DB). what i want my contents of the file to be is ZHS16CGB231280 (GB) charset.

hope any oracle experts can help. thanks in advance.

regards,
denshir
0
Comment
Question by:denshir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 8

Expert Comment

by:Danielzt
ID: 8039131
what's your national character set? if it is ZHS16CGB231280 , then you can function to_nchar or translate...using to get what you want.

if it is not ZHS16CGB231280, create another database specified the national character set as ZHS16CGB231280, export you table and import it into the new database. Then use the way mentioned before to do it.



0
 

Author Comment

by:denshir
ID: 8039764
my database national character set is UTF8. Is it always that utl_file packages uses the national character sets ? This is because when i set the NLS_LANG environment variable to AMERICAN_AMERICA.ZHS16CGB231280 and do a select utl_raw.cast_to_varchar2(CHINESE_NAME) from TABLEX, I could display out the characters of the correct encoding but not when i use the utl_file package to write to file. the contents i.e. chinese characters in the file will be UTF8 encoded.

is there any predefined functions ard for this conversion or do I need to write my own conversion ?

regards,
denshir
0
 

Author Comment

by:denshir
ID: 8039986
my database national character set is UTF8. Is it always that utl_file packages uses the national character sets ? This is because when i set the NLS_LANG environment variable to AMERICAN_AMERICA.ZHS16CGB231280 and do a select utl_raw.cast_to_varchar2(CHINESE_NAME) from TABLEX, I could display out the characters of the correct encoding but not when i use the utl_file package to write to file. the contents i.e. chinese characters in the file will be UTF8 encoded.

is there any predefined functions ard for this conversion or do I need to write my own conversion ?

regards,
denshir
0
 

Author Comment

by:denshir
ID: 8040027
my database national character set is UTF8. Is it always that utl_file packages uses the national character sets ? This is because when i set the NLS_LANG environment variable to AMERICAN_AMERICA.ZHS16CGB231280 and do a select utl_raw.cast_to_varchar2(CHINESE_NAME) from TABLEX, I could display out the characters of the correct encoding but not when i use the utl_file package to write to file. the contents i.e. chinese characters in the file will be UTF8 encoded.

is there any predefined functions ard for this conversion or do I need to write my own conversion ?

regards,
denshir
0
 
LVL 2

Accepted Solution

by:
Datamonkey earned 400 total points
ID: 8051372
Hi,

This has nothing to do with the national character set of the database. The issue is simply that pl/sql procedures are not affected by the character set setting in NLS_LANG. PL/SQL procedures always work in the database charater set (or the national character set if you're using a NVARCHAR2 but since you're using a VARCHAR that's not in play at the moment).
So if you you use utl_file to write a varchar it will simply write it as it is in the database and not use conversion based on the NLS_LANG.
If you want to write in any other character set you need to manualy convert the lines your writing to a different character set. This is simple enough by using the convert function.
This will do the trick:
utl_file.put_line(CONVERT(variablename,'ZHS16CGB231280'));
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question