NLS SETTINGS PROBLEM [message #217021] Wed, 31 January 2007 06:17 Go to next message
Messages: 34
Registered: November 2006
Location: india
Hi we have the the flat files from countries Hungary and Czech republic while using this in the orcle we faced some problems
like junk charcters anyone help me how to convert this files to UTF-8 file format.

Re: NLS SETTINGS PROBLEM [message #217104 is a reply to message #217021] Wed, 31 January 2007 12:33 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you need to be specific about your environment.
What OS is your database on - and what codepage/characterset is the default?
What version?
Are you using sqlldr, utl_file or something else?
What is your database characterset?
What are your current NLS? Something like this?

Do a dump on the character data from the table to examine the byte contents - your app which is displaying the data could be the problem rather than a problem with getting the data into the table.

-- here you can see that characters aren't displaying 
correctly, but have the correct byte values
-- http://www.tachyonsoft.com/uc0020.htm#U20AC

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

create table utf8_tst(col1 varchar2(1 char));

-- Euro is U+20AC
insert into utf8_tst values (unistr('\20AC'));
-- Small Greek Gamma U+03B3
insert into utf8_tst values (unistr('\03B3'));
insert into utf8_tst values (unistr('A'));

select col1, vsize(col1), dump(col1, 1010) Decimal_bytes, dump(col1, 1016) Hex_Bytes from utf8_tst;
 	3	Typ=1 Len=3 CharacterSet=AL32UTF8: 226,130,172	Typ=1 Len=3 CharacterSet=AL32UTF8: e2,82,ac
	2	Typ=1 Len=2 CharacterSet=AL32UTF8: 206,179	Typ=1 Len=2 CharacterSet=AL32UTF8: ce,b3
A	1	Typ=1 Len=1 CharacterSet=AL32UTF8: 65	Typ=1 Len=1 CharacterSet=AL32UTF8: 41

SQLPLUS can't display UTF8 characters - try isqlplus, because it runs in a browser and browsers can display many utf8 characters without issue

[Updated on: Wed, 31 January 2007 12:51]

Report message to a moderator

Previous Topic: SQL beeded
Next Topic: Database Time in Oracle 9i
Goto Forum:

Current Time: Sat Apr 29 08:54:42 CDT 2017

Total time taken to generate the page: 0.14429 seconds