?
Solved

Oracle NLS

Posted on 2003-02-27
5
Medium Priority
?
4,133 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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month8 days, 23 hours left to enroll

621 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