Hi Tom ,
Does that mean I would still be able to store Chinese characters in a WE8ISO Db ,provided the Client and Db Char sets are the same ? Thru the java program if I managed to encode the data in WE8ISO scheme , according to you , it should work ? Please correct me if I am wrong .

Hi Tom,

We migrated our DB 8.1.7 to 9.2.In 8.1.7 we used UTF8 character set.It remains same in 9.2.
We know that Oracle 9.2 doesn't have UTF8 but AL32UTF8.
Can we keep this UTF8 or have to change to AL32UTF8.
If we need to change, may we do it by :
alter database character set AL32UTF8
or
we must use exp/imp utility?

Regards

After migration to 9.2 when we run this query:
select * from nls_database_parameters where parameter like '%CHARACTERSET'
we got :
Parameter Value
------------------ ------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

We are still confused with this values.
Any explanation?

Hi tom,

We are confused because during creation of new db under Oracle 9.2 we have had no opportunity to choose UTF8 as character set, only AL32UTF8 and AL16UTF16.

Thanks

Hi Tom,

Currently I have to work with oracle9.2.0.2.1 server as a database server, which uses UTF8 characterset. 
At the same time I have to use oracle8.1.7 client since some perl modules only work with oracle8 client.
But I found out that I could not use oracle8.1.7 client to insert diacritics value, the data value 
is changed after inserting into the database. If I use oracle9i client, I can insert and select then get the same value.

For example: 
==============================================================
Using oracle9i client,

 SQL> create table t (tm varchar2(100));
 
 Table created.
 
 SQL> insert into t (tm) values ('êë');
 
 SQL> select tm from t;
 
 TM
 ------------------------------------------------------------
 êë
 
 1 row selected.
 
Elapsed: 00:00:00.02
=============================================================
If I use oracle8i client, I get the following:

 SQL> insert into t (tm) values ('êë');
 
 SQL> select tm from t;
 
 TM
 ------------------------------------------------------------
 ++
 
 ======================================================
 
 So is there a way I can get diacritics value work CORRECTLY in the environment of 
 Oracle8i client and Oracle9i server ?
 
 Thanks, 

Please tell us which one has to be used - either utf-8 or
some other - explicity please...

If the client and db char sets are the same- you can stuff anything in, and if the tool expects a certain char set, it'll come out "looking fine". While it may seem dandy at first- the long term prospects are horrendous.

I worked on a migration project that turned out to be "un-migratable" because of it. Tool one created accounts in batch, tool two via a nifty little Java program for the CSRs. As long as you looked at the Java tool accounts with the Java tool and the others with the older tool, all was well. These clowns built a high-end OLTP system that was stuffing data in both Latin-1 and UTF-8.

Once you mix char sets, you can't separate things out later. Take a look at what Oracle thinks is in the table using the SQL Dump function and you'll see that as soon as you tell Oracle that data is Latin-1 and you want it to be UTF-8, there will be characters that Oracle won't be able to understand. You then get to decide which accounts aren't really important. But actually, it's even better- there is of course overlap in the char sets, so Oracle looks at some of the data and says, yup I can convert that, but it's actually from the other char set and results in gibberish.

If you go this path, take the fast track and make sure the Java guys don't use bind variables, you'll be able to redesign the system in less time that way.







There is a character set scanner tool shipped with the database, you can

Hi Tom,

We are migrating from oracle 8.1.6 to oracle 9 R2. We have about 14 oracle instance. All instances have WE8ISO88591P1
character set. Our company is expanding globally so we are thinking to use unicode character set with oracle 9.
I have few questions on this issue.

1) What is the difference between UTF-8,UTF-16
Is AL32UTF8 and UTF-8 is same character set or they are different?
Is UTF-16 and AL16UTF16 is same character set or different ?

2) Which character is super set of all character set?
If there is any, Does oracle support that character set?

3) Do we have to change our pl/sql procedure if we move to unicode database ? The reason for this question is our developer is using ascii character for carrage return and line feed like chr(10) and chr(13) and some other ascii character .

4) What is impact on CLOB ?

5) What will be the size of the database? Our production DB size is currently 50GB. What it would be in unicode?

Thanks

from start to finish.

see

tom,
i have created a database with utf8 character set.

UTF8WL>select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER VALUE
============================== =========================================================
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

i created a table temp, i want to insert a japanese character and view it on sqlplus - please let me how to do it.

i can take a japense character from my application,but when i copy to sqlplus to insert it goes as ????. dont know why!

please through some light on this


Thanks

I read the subject oracle doc online. However, I would like to clarify some ponits with you.

(1) If my database char set is UTF8, does it mean that I can store chars longer than a byte (ex chinese or japanese chars) in CHAR or VARCHAR2 datatypes even though I have not specified ny National Char set when I created my database.

(2) Only if my client system or application is set in a character set (for example UTF8) the clients can input foreign chars such as Chinese to store in the database.

(3) Oracle does character set conversion NOT data conversion. My understanding is that using globaliztion we can store chars in many languages in the database
if the database character set supports.

(4) Do I have to explicitly set National char set while creating database to use NCHAR or NVARCHAR datatypes in my tables?

I would greatly appreciate if you could please clarify these points. Thanks so much for your time. You are the best.

I thought that I was beginning to get a handle on this multi-byte stuff but now I'm confused. (Sometimes the more you read, the more you realize how little you know!) The 9i Global. support guide states:

UTF8
The UTF8 Oracle character set encodes characters in one, two, or three bytes. It is for ASCII-based platforms. The UTF8 character set supports Unicode 3.0. Although specific supplementary characters were not assigned code points in Unicode until version 3.1, the code point range was allocated for supplementary characters in Unicode 3.0. Supplementary characters are treated as two separate, user-defined characters that occupy 6 bytes.

UTF-8
The 8-bit encoding of Unicode. It is a variable-width encoding. One Unicode character can be 1 byte, 2 bytes, 3 bytes, or 4 bytes in UTF-8 encoding. Characters from the European scripts are represented in either 1 or 2 bytes. Characters from most Asian scripts are represented in 3 bytes. Supplementary characters are represented in 4 bytes


UTF-8 *and* UTF8?? Even though the differences are listed above I'm not sure that I exactly understand the distinction. Which UTF-eight characterset has been the one discussed in this thread up until now -- UTF8 or UTF-8?

Thanks,

Dear Tom,

We have inserted japanese data using japanese key board through  our java application into the database of character set UTF8.
When we retrieve data either through the application or backend, it is displayed as '?????'. 

Our NLS_LANG setting is AMERICAN_AMERICA.UTF8.

The output using the dump function on the column wherein we have inserted japanese data is given below:
SQL> select dump(pt_desc,1016) from w_type;

DUMP(PT_DESC,1016)
--------------------------------------------------------------------------------
Typ=1 Len=31 CharacterSet=UTF8: e2,80,9a,c3,8d,e2,80,9a,c2,b5,e2,80,9a,c3,86,20,
e2,80,9a,c3,8d,e2,80,9a,c2,b5,e2,80,9a,c3,86

Typ=1 Len=5 CharacterSet=UTF8: 3f,3f,3f,3f,3f
Typ=1 Len=5 CharacterSet=UTF8: 4d,55,54,48,55
Typ=1 Len=16 CharacterSet=UTF8: 4d,55,54,48,55,20,4d,41,52,54,48,41,4e,44,41,4d
Typ=1 Len=14 CharacterSet=UTF8: 3f,3f,3f,3f,3f,3f,3f,3f,3f,3f,3f,3f,3f,3f
Typ=1 Len=7 CharacterSet=UTF8: 3f,3f,3f,3f,3f,3f,3f
Typ=1 Len=7 CharacterSet=UTF8: 3f,3f,3f,3f,3f,3f,3f
Typ=1 Len=5 CharacterSet=UTF8: 3f,3f,3f,3f,3f
Typ=1 Len=5 CharacterSet=UTF8: 3f,3f,3f,3f,3f


What could be the possible reason for non-display of the japanese data? Is there any other setting we will have to look into and change?

Thanks and Regards,
Venkat

 

Dear Tom,

Thanks for your immediate response.
Is there any way to query and see the data in japanese at the back end so that we can confirm that it is certainly not a data storage problem.

Thanks and Regards,
Venkat

Dear Tom,

In the application , the displays is '?????'.
Forgot to add this point.

Thanks and Regards,
Venkat

Dear Tom,

The application team wants to see the japanese characters when they query this data at the back end through sqlplus to confirm that the storage and display of data is fine at the backend. What is the way to achieve this? We open a telnet session with the server and get connected to the database.

Thanks and Regards,
Venkat

Tom,

In the dump output, the character set of the database is displayed. How to interpret and confirm that the record is stored in utf8? A sample output of dump showing the particular record containing japanese data is given below:

DUMP(PT_DESC)
--------------------------------------------------------------------------------
Typ=1 Len=31: 226,128,154,195,141,226,128,154,194,181,226,128,154,195,134,32,226
,128,154,195,141,226,128,154,194,181,226,128,154,195,134

Typ=1 Len=5: 63,63,63,63,63
Typ=1 Len=5: 77,85,84,72,85
Typ=1 Len=26: 239,190,157,239,190,133,239,189,182,239,190,157,239,190,133,32,239
,189,182,239,189,189,239,189,189,32

Typ=1 Len=26: 239,190,157,239,190,133,239,189,182,239,190,157,239,190,133,32,239
,189,182,239,189,189,239,189,189,32

This is just to confirm to the application team that the data storage is correct in the database.

Thanks and Regards,
Venkat

UNICODE ENCODINGS 
The Unicode Character Set has a few encodings, i.e. ways in which characters 
are represented by binary codes. The main one is UCS-2 in which exactly 2 bytes 
are used to store each character. Unicode codes of characters are conventionally 
specified in this encoding and denoted as U+nnnn, where nnnn is the hex UCS-2 
code of the character in range 0000-FFFF. 
The second popular encoding is UTF-8 -- multibyte encoding scheme where 
characters are stored in either 1, 2 or 3 bytes. Oracle RDBMS supports only 
the UTF-8 encoding. The corresponding database character set is named UTF8. 
MAPPING BETWEEN UCS-2 AND UTF-8 
For character with UCS-2 codes in range 0x0000-0x007f (0-127), the character 
code has no more than 7 significant bits and the UCS-2 and UTF-8 code points 
(bit patterns) are the same, except that the UCS-2 code has 2 bytes and UTF-8 
only one. If you want to use the CHR function to insert a Unicode character 
in this range into an UTF8 database you simply use CHR(n) (where n is 0-127). 
UTF-8 codes in this range are identical to US7ASCII. 
For Unicode characters with codes from 0x0080 (128) onwards, UTF-8 uses 
multibyte encoding and so you have to convert the UCS-2 code to the multibyte 
code point. 
The mapping between UCS-2 and UTF-8 is as follows: 
Encoding bits Unicode (UCS-2) UTF-8 bit pattern UTF-8 byte size 
------------- --------------- --------------------------- --------------- 
7 0x0000 - 0x007f 0xxxxxxx 1 
11 0x0080 - 0x07ff 110xxxxx 10xxxxxx 2 
16 0x0800 - 0xffff 1110xxxx 10xxxxxx 10xxxxxx 3 
As an example, from either of the above URLs, character U+00C2 is "A" with a 
circumflex above. The UCS-2 code is obviously 0x00c2. To find its UTF-8 code 
point: 
1. 0x00c2 lies within the 11 bit range (i.e. there are from 8 to 11 significant 
bits in the code) so the UTF-8 bit pattern to be used is 
110xxxxx 10xxxxxx 
|___| |____| 
| | 
A B 
2. Convert 0x00c2 to its bit representation: 0000 0000 1100 0010 
3. Convert 1100 0010 to the UTF-8 bit pattern: 
Take the rightmost 6 bits and slot them into position B: 1000 0010 
Take the remaining 5 bits (11-6) and slot them into position A: 1100 0011 
Any bit positions not used are set to 0. 
4. The UTF8 bit pattern is: 1100 0011 1000 0010 
5. Convert this to decimal: 50050 
The UTF-8 code point for UCS-2 character U+00C2 is 50050. You can use CHR(50050) 
to insert "A" with circumflex above into an UTF8 database. 
Similar example could be given for the Euro currency symbol, codepoint U+20AC. 
20AC = 10000010101100. We have to map this to the following 3 byte pattern: 
1110xxxx 10xxxxxx 10xxxxxx 
|__| |____| |____| 
| | | 
A B C 
A = 0010 , B = 000010 , C = 101100 
(note that we prefixed A with '00') 
So we get: 111000101000001010101100 binary, that is 14844588 decimal. 
a more handy place to find a UTF8 codepoint: 

http://www.macchiato.com/unicode/charts.html

(the UCS2 hex codepoint corresponds with the UTF16 hex) 
create table test ( testcol VARCHAR2(10)); 
insert into test values (chr(50050)); 
(-> U+00C2 : LATIN CAPITAL LETTER A WITH CIRCUMFLEX) 
insert into test values (chr(14844588)); 
(-> U+20AC : EURO SIGN) 
If you want to insert into an Nvarchar column you need to use: 
create table test ( testcol NVARCHAR2(10)); 
insert into test values (chr(50050 USING NCHAR_CS)); 
insert into test values (chr(14844588 USING NCHAR_CS)); 
From V9 onwards you can use also this syntax: 
* the Nchar: 
create table test ( testcol NVARCHAR2(10)); 
insert into test values (nchr(50050)); 
insert into test values (nchr(14844588)); 
* the UNISTR function to insert unicode directly: 
create table test ( testcol NVARCHAR2(10)); 
insert into test values (UNISTR('\00c2')); 
insert into test values (UNISTR('\20AC')); 
the value is the UCS2 code for the symbol, you can append this like this: 
insert into test values (UNISTR('\20AC\00c2')); 
Oracle will do no normalisation on this type of operation, so insert of 
insert into test values (UNISTR('\0041\0308')); 
will insert 2 symbols the A and the ¨ and not Ä ... 
6) With the "dump" command you can see what is actually stored in the database 
but you get the representation for each byte back, hence if you want to check 
the data in a UTF8 database you need to covert the UCS-2 to UTF8 and then to 
the decimal for each byte. 
For "A" with circumflex this gives 195,130 
For the Euro symbol this gives 226,130,172 
SQL> select dump(testcol) from test; 
DUMP(TESTCOL) 
-------------------------------------- 
Typ=1 Len=2: 195,130 
Typ=1 Len=3: 226,130,172 
the "Len" give the number of BYTES the row physically uses in the 
database, if you store only one character, then you have of course the length of 
the character (3 bytes for euro in utf8 as you can see) 
other options with "dump": 
SQL> select dump(testcol,16) from test; 
DUMP(TESTCOL,16) 
---------------------------------------- 
Typ=1 Len=2: c3,82 
Typ=1 Len=3: e2,82,ac 
50050 decimal is C382 hex and 14844588 decimal is E282AC hex 
adding a '16' to the dump command shows the data in hexadecimal format. 
Side remark: 
How can you tell the number of bytes for a character encoded in UTF-8? (! not UCS2 !!! ) 
You look at the first hex digit and follow this rule: 
If it has X then the character is n byte long 
----------- -------------------------------------- 
0 through 7 1 
C or D 2 
E 3 
F 4 
SQL> select dump(testcol,1016) from test; 
DUMP(TESTCOL,1016) 
------------------------------------------------- 
Typ=1 Len=2 CharacterSet=AL32UTF8: c3,82 
Typ=1 Len=3 CharacterSet=AL32UTF8: e2,82,ac 
adding a '1016' to the dump command shows the characterset used for the data 
and the data in hexadecimal format. 
SQL> select dump(testcol,1010 )from test; 
DUMP(TESTCOL,1010) 
--------------------------------------------------- 
Typ=1 Len=2 CharacterSet=AL32UTF8: 195,130 
Typ=1 Len=3 CharacterSet=AL32UTF8: 226,130,172 
adding a '1010' to the dump command shows the characterset used for the data 
and the data in decimal format. 
You can also use the convert function if you know the codepoint of a character 
in another characterset: (if you know the codepoint of the euro is 128 in the 
WE8MSWIN1252 characterset by example) 
SQL> select dump(convert(chr(128),'UTF8','WE8MSWIN1252'),16) from dual; 
DUMP(CONVERT(CHR(128) 
--------------------- 
Typ=1 Len=3: e2,82,ac 
the syntax is: 
select dump(convert(chr(<Decimal value>),'<target_char_set>','<source_char_set>'),16) from dual; 
 

Dear Tom,

The DB character set is UTF8.Like to know whether the NLS_LANG needs to be set suitably during every insertion/querying of different language data?

For example, during insertion of japanese data, setting the value of NLS_LANG as AMERICAN_AMERICA.JA16SJIS and settting as AMERICAN_AMERICA.UTF8 for insertion of Hindi( one of the indian languges). Is this the way to go?

Regards,
Venkat

Dear Tom,

We like to store japanese,korean and english data in our database of character set UTF8. My colleague says that UTF8 may not support all japanese characters and suggest going for AL32UTF8 as charater set and AL16UTF16 as national character set. Is this correct? If so, kindly explain the rationale behind this. If not, what should be the value for character set and national character set and why?

Regards,
Venkat

We have an existing db using US7Ascii charset if we made it UTF8 will there be performance issues ? since characters will be represented by several bytes ergo less data fitting on the block buffer cache??

Dear Tom,
I have installed Oracle 9i with UTF-8 characterset and nls_language as AMERICAN. I want to use this Database for storing ENGLISH and FRENCH Data.I want to know the following
how do i log onto sqlplus with my language as FRENCH / ENGLISH. what are the parameters that i have to set. if i have logged in as French then i want all the ora messages to be in french. when i alter the session and set the date format to dd/mm/yyyy it works but when i alter the session and set the nls_language to french it executes and on giving an errorneous select statement it returns the ora message in English and not french. Pls. help

Suggest that you read the support Note:152260.1 and 179133.1

Hello Tom,

I have the following:


----------------------------------------------------------------------------------
create table clob_table(pk number, text clob, primary key(pk));

create or replace java source named test2 as
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.io.*;
public class Test2 {
public static void save(String fn, CLOB clob) throws Exception {
OutputStream out=clob.getAsciiOutputStream();
InputStream z=new FileInputStream(fn);
byte[] buffer=new byte[1024];

int cnt;
while ((cnt=z.read(buffer))!=-1) {
out.write(buffer,0,cnt);
}
z.close();
out.close();
}
public static void main(String s[]) throws Exception {
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@bill:1521:devdb","USER","PASSWORD");
con.setAutoCommit(false);
CallableStatement cs=con.prepareCall("begin insert into clob_table (pk,text) values (?,empty_clob()) returning text into ?; end;");
cs.registerOutParameter(2,OracleTypes.CLOB);
cs.setInt(1,10);
cs.execute();
CLOB clob=((OracleCallableStatement)cs).getCLOB(2);
save("/var/tmp/plain.txt",clob);
con.commit();
}
};

/

create or replace procedure save(p_fname varchar2, p_clob clob)
as language java
name 'Test2.save(java.lang.String, oracle.sql.CLOB)';
/

declare
l_clob clob;
begin
insert into clob_table (pk,text) values (10,empty_clob()) returning text into l_clob;
save('/var/tmp/plain.txt',l_clob);
end;


$> cat /var/tmp/plain.txt
über

----------------------------------------------------------------------------------


Then if run the following Java program as client from windoze or unix it stores the word `über' as it should

Offset Byte
--------------
0 252
1 98
2 101
3 114

but if I deploy the Java program directly on the server and call the above anonymous block it stores `?ber'
Offset Byte
--------------
0 191
1 98
2 101
3 114

My server is:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Both client and server use WE8ISO8859P1

Can you explain this?

Thank you in advance

Because I need to process the file before it is saved in database. This processing is something pl/sql is not capable to do (currently, I know 10g could do that).

I use Writer from clob.getCharacterOutputStream and explicit byte[] -> char[] encoding. And that works albeit a little bit slower.

But still if you could explain the original problem. I'd be grateful. Understanding "why" is sometimes more important than finding an alternate solution.

Thank you very much.

This "mysterious" ;) processing is zip compression. I omitted this in original question for brevity. 9i can't do that natively but 10g can. So I opted for java (I could do it in C as well)

We had similar issue already some time ago:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12980645053487, <code>

But so far I have not had to store "strange" characters so the problem didn't manifest itself.

Well, that it is character conversion issue was clear. But why does it work on the client (exactly the same Java code) and fails on the server???

Maybe it is different Java versions?
Client 1.4.2_01-b06
Server 1.3.1

And maybe it is because of different "drivers". On the server it is native "already there" connection (supposedly native code) while on the client it is "normal" remote connection (java class). Whether it is a bug I can't tell.

Thank you very much,

Regards
Piotr

Hi again,

Just to make a final note (sorry for being persistent on that issue but I could not sleep until I found an explanation ;))

So the little java client program above, when run (on the client's machine) with java thin driver, does not make character conversion IMO as should be cause client character set=server character set but with OCI driver it does character conversion and stores junk in DB.

So now I am sure it is a bug. Cause the drivers should work at least consistently. Changing the driver may not change the semantics of a program.

Best regards,
Piotr

Pls see the metalink note Note:60942.1
Subject: JDBC Drivers and NLS
It answers your query

Pls help me clarify some language issues.

As of my understanding.

1. If my db characterset is JA16SJIS i can store japanese characters in column types char, varchar and varchar2.

2. Using the same charset as above (JA16SJIS) and if i want to store Chinese char in my db it should be placed on columns defined as Nchar, Nvarchar and Nvarchar2 and set my nls_language = chinese charset.

3. If i want to store both chinese and japanese on the same table using the same column defined as char, varchar, varchar2 my db characterset should be = UTF8 ?

4. 'Fortunately' for us our current db charset is US7ASCII and i've read that if using US7ASCII it only takes 'alter database character set xxxxxx' command to change the existing character set.


select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET US7ASCII
NLS_RDBMS_VERSION 8.1.7.0.0

#2 should be nls_nchar_characterset instead of nls_language

I've 8i and 9i documentations available.

Since our db's in 8i i'm reading the 8i docs. There seems to be no globalization guide on 8i just the Oracle8i National Language Support Guide Release 2 (8.1.6). 

1. Where can i find documents or guides laying out which db characterset are strict superset of another characterset ?

2. If my target characterset is a strict superset of my current characterset. Do i need to do a full export/import ? or just the suggested 

---8<---
SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
    <do a full backup>

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET <new_character_set_name>;
SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
SQL> STARTUP;

---8<---

3. How does the isuance of 'alter database character set xxxx' affects standby databases ?


 

Tom,

I have 2 database db1 (9204) with AL32UTF8 characterser and db2 (8174) with WEISO889P1 characterset.

I am trying to execute a procedure (passing some parameters) from 9204 on 8174 using db links. It throws up an error "database character set conversion not supported"

If i convert those parameters to raw and pass it works, but i dont wanna do that as it involves lots of changes.

Can you please suggest some solution.

Thanks.

Tom,

This is the error message.

Ora-12703 "This character set conversion is not supported"

Sorry Tom i didn't get you on convert one. Can you please clarify.

Thanks.

Thanks for your feedback. Sorry i typed it wrong,corrected.
As i mentioned before it will difficult to change the code,
Can we implement a trigger for the user who executes this procedures the parameter that will be passed will be converted to we8iso8859p1 and viceversa.

What i meant is can we enforce some plsql code so that whenever the procedure is executed the parameter that is passed to the procedure is automatically converted to we8iso8859p1 characterset.

Thanks.

Tom,

Here is the output,

Error on line 0
select convert( 'hello world', 'al32utf8', 'we8iso8859p1' ) from dual

ORA-01482: unsupported character set

Error on line 0
select convert( 'hello world', 'we8iso8859p1', 'al32utf8' ) from dual

ORA-01482: unsupported character set


Thanks.

Dear Tom,
  I have this:
SQL> show user
User is "lizh"
SQL> drop table emp;
Table dropped
SQL> drop table dept;
Table dropped
SQL> create table emp as select * from scott.emp where 1=0;
Table created
SQL> create table dept as select * from scott.dept where 1=0;
Table created
SQL> alter table DEPT modify DEPTNO nvarchar2(20);
Table altered
SQL> alter table EMP modify DEPTNO nvarchar2(20);
Table altered
SQL> insert into dept select * from scott.dept;
4 rows inserted
SQL> insert into emp select * from scott.emp;
14 rows inserted
SQL> commit;
Commit complete
SQL> alter table DEPT
 2    add constraint pk_dept primary key (DEPTNO);
Table altered
SQL> alter table EMP
  2    add constraint pk_emp primary key (EMPNO);
Table altered
SQL> alter table EMP
  2    add constraint fk_dept foreign key (DEPTNO)
  3    references dept (DEPTNO);
Table altered
SQL> create or replace function gencommastr(pc in sys_refcursor)
  2  return varchar2
  3  is
  4    temp   varchar2(100);
  5    Result varchar2(1000);
  6  begin
  7    loop
  8      fetch pc
  9        into temp;
 10      exit when pc%notfound;
 11      Result := Result || ',' || temp;
 12    end loop;
 13    return nvl(substr(Result, 2),'null');
 14  end;
 15  /
Function created
SQL> select gencommastr(
  2         cursor (select ENAME
  3                 from emp
  4                 where DEPTNO = d.deptno)) result,
  5         deptno
  6    from dept d;
RESULT                               DEPTNO
------------------------------------ ----------------------------------------
null                                 10
null                                 20
null                                 30
null                                 40
SQL> ALTER SESSION SET NLS_NCHAR_CONV_EXCP=TRUE;
Session altered
SQL> select gencommastr(
  2         cursor (select ENAME
  3                 from emp
  4                 where DEPTNO = d.deptno)) result,
  5         deptno
  6    from dept d;
RESULT                               DEPTNO
------------------------------------ ----------------------------------------
null                                 10
null                                 20
null                                 30
null                                 40
SQL> select gencommastr(
  2         cursor (select ENAME
  3                  from emp
  4                 where DEPTNO = d.deptno)) result,
  5         deptno
  6    from (select to_char(deptno) deptno,dname from dept) d;
RESULT                               DEPTNO
------------------------------------ ------------------------------------
CLARK,KING,MILLER                    10
SMITH,JONES,SCOTT,ADAMS,FORD         20
ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 30
null                                 40


-------------------------------------------------------------------------
   Why the result is "null" when I use Nvarchar2?

(SQL> select * from v$nls_parameters;
PARAMETER                        VALUE
-------------------------------- --------------------------------
NLS_LANGUAGE                     SIMPLIFIED CHINESE
NLS_TERRITORY                    CHINA
NLS_CURRENCY                     RMB
NLS_ISO_CURRENCY                 CHINA
NLS_NUMERIC_CHARACTERS           .,
NLS_CALENDAR                     GREGORIAN
NLS_DATE_FORMAT                  DD-MON-RR
NLS_DATE_LANGUAGE                SIMPLIFIED CHINESE
NLS_CHARACTERSET                 ZHS16GBK
NLS_SORT                         BINARY
NLS_TIME_FORMAT                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                RMB
NLS_NCHAR_CHARACTERSET           AL16UTF16
NLS_COMP                         BINARY
NLS_LENGTH_SEMANTICS             BYTE
NLS_NCHAR_CONV_EXCP              TRUE
19 rows selected
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0    Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

sql:
drop table emp;
drop table dept;
create table emp as select * from scott.emp where 1=0;
create table dept as select * from scott.dept where 1=0;
alter table DEPT modify DEPTNO nvarchar2(20);
alter table EMP modify DEPTNO nvarchar2(20);
insert into dept select * from scott.dept;
insert into emp select * from scott.emp;
commit;
alter table DEPT
     add constraint pk_dept primary key (DEPTNO);
alter table EMP
  add constraint pk_emp primary key (EMPNO);
alter table EMP
     add constraint fk_dept foreign key (DEPTNO)
     references dept (DEPTNO);
create or replace function gencommastr(pc in sys_refcursor)
    return varchar2
    is
      temp   varchar2(100);
      Result varchar2(1000);
    begin
      loop
        fetch pc
          into temp;
       exit when pc%notfound;
       Result := Result || ',' || temp;
     end loop;
     return nvl(substr(Result, 2),'null');
   end;
   /
select gencommastr(
           cursor (select ENAME
                   from emp
                   where DEPTNO = d.deptno)) result,
           deptno
      from dept d;
ALTER SESSION SET NLS_NCHAR_CONV_EXCP=TRUE;
select gencommastr(
           cursor (select ENAME
                   from emp
                   where DEPTNO = d.deptno)) result,
           deptno
      from dept d;
select gencommastr(
           cursor (select ENAME
                    from emp
                   where DEPTNO = d.deptno)) result,
           deptno
      from (select to_char(deptno) deptno,dname from dept) d;
) 

hi Tom,
  Thank you for your review.

>SQL> alter table DEPT modify DEPTNO nvarchar2(20);
>Table altered
>SQL> alter table EMP modify DEPTNO nvarchar2(20);
>Table altered

emp.deptno and dept.deptno are all nvarchar2(20)
Why there are IMPLICIT conversions?
And 
  >ALTER SESSION SET NLS_NCHAR_CONV_EXCP=TRUE;
Why there are no errors reported if the data lossed when conversions?
(sorry for my poor english ;) )
Thank you in advance. 

hi tom,
 I have changed the datatype in my table and function:
SQL>DESC DEPT
    Name                        Null?    Type
    --------------------------- -------- ----------------
    DEPTNO                      NOT NULL NVARCHAR2(20)
    DNAME                                NVARCHAR2(20)
    LOC                                  NVARCHAR2(20)
SQL>DESC EMP
    Name                        Null?    Type
    --------------------------- -------- ----------------
    EMPNO                       NOT NULL NUMBER(4)
    ENAME                                NVARCHAR2(20)
    JOB                                  NVARCHAR2(20)
    MGR                                  NUMBER(4)
    HIREDATE                             DATE
    SAL                                  NUMBER(7,2)
    COMM                                 NUMBER(7,2)
    DEPTNO                               NVARCHAR2(20)

create or replace function gencommastr(pc in sys_refcursor)
    return nvarchar2
    is
      temp   nvarchar2(100);
      Result nvarchar2(1000);
    begin
      loop
        fetch pc
          into temp;
       exit when pc%notfound;
       Result := Result || ',' || temp;
     end loop;
     return Result;
   end;
   /


I get the same result!
I was confused by this.
Can you Please point me where I'am wrong?

Thank you.
 

confirmed -- looks like something is wrong, i just copied deptno=10:

ops$tkyte@ORA9IR2> select emp.ename, dept.deptno
  2    from emp, dept
  3   where emp.deptno = dept.deptno;
 
ENAME      DEPTNO
---------- --------------------
CLARK      10
KING       10
MILLER     10
 
<b>it joins right, but</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select cursor (select ENAME
  2                     from emp
  3                     where DEPTNO = d.deptno ) result,
  4             deptno
  5        from dept d;
 
RESULT               DEPTNO
-------------------- --------------------
CURSOR STATEMENT : 1 10
 
CURSOR STATEMENT : 1
 
no rows selected
 

<b>no joy with the cursor variable...  But the value is getting passed in, but it looks strange on the way out:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select d.deptno, cursor( select d.deptno from dual )
  2    from dept d;
 
DEPTNO               CURSOR(SELECTD.DEPTN
-------------------- --------------------
10                   CURSOR STATEMENT : 2
 
CURSOR STATEMENT : 2
 
DEPTNO
--------------------------------
 1 0
 
 
<b>here is a workaround for you if you like -- might be more efficient as well:</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dept.deptno, stragg(emp.ename)
  2    from emp, dept
  3   where emp.deptno = dept.deptno
  4   group by dept.deptno
  5  /
 
DEPTNO
--------------------
STRAGG(EMP.ENAME)
------------------------------------------------------------------------
10
CLARK,KING,MILLER
 

(search this site for stragg to pick up that user defined aggregate package)


Interestingly, in 8i and before, you would get:

ops$tkyte@ORA817DEV> select cursor (select ENAME
  2                     from emp
  3                     where DEPTNO = d.deptno ) result,
  4             deptno
  5        from dept d;
      from dept d
           *
ERROR at line 5:
ORA-00604: error occurred at recursive SQL level 1
ORA-12704: character set mismatch


if you would like -- i would persue this via support with this very small test case:

drop table emp cascade constraints;
create table emp ( deptno nvarchar2(10), ename varchar2(10) );
drop table dept cascade constraints;
create table dept(deptno nvarchar2(10) );
insert into emp values( n'10', 'king' );
insert into dept values( n'10' );
commit;
                                                                                                                       
select emp.ename, dept.deptno
  from emp, dept
 where emp.deptno = dept.deptno;
                                                                                                                       
select cursor (select ENAME
                   from emp
                   where DEPTNO = d.deptno ) result,
           deptno
      from dept d;
                                                                                                                       
select d.deptno, cursor( select d.deptno from dual )
  from dept d;

 

hi tom,
In my database I need to store Chinese and English.
When the input mixed Chinese and English I use Nvarchar2 (for verify data length easier),when the input only English character I use varchar2.
What do you think about it?
How can I avoid the problem that I asked you previously?
Please give me some advise.
Thank you.

hi tom,
Thanks for the review.
I have done some test use your test case,this is the result:

lizh@lizhdb>alter session set nls_language=english;

Session altered.
(lizh:for display english).

lizh@lizhdb>drop table emp cascade constraints;

Table dropped.

lizh@lizhdb>create table emp ( deptno nvarchar2(10), ename varchar2(10) );

Table created.

lizh@lizhdb>drop table dept cascade constraints;

Table dropped.

lizh@lizhdb>create table dept(deptno nvarchar2(10) );

Table created.

lizh@lizhdb>insert into emp values( n'10', 'king' );

1 row created.

lizh@lizhdb>insert into dept values( n'10' );

1 row created.

lizh@lizhdb>commit;

Commit complete.

lizh@lizhdb>select emp.ename, dept.deptno
2 from emp, dept
3 where emp.deptno = dept.deptno;

ENAME DEPTNO
---------- --------------------
king 10

lizh@lizhdb>
lizh@lizhdb>
lizh@lizhdb>select cursor (select ENAME
2 from emp
3 where DEPTNO = d.deptno ) result,
4 deptno
5 from dept d;

RESULT DEPTNO
-------------------- --------------------
CURSOR STATEMENT : 1 10

CURSOR STATEMENT : 1

no rows selected


lizh@lizhdb>
lizh@lizhdb>
lizh@lizhdb>select d.deptno, cursor( select d.deptno from dual )
2 from dept d;

DEPTNO CURSOR(SELECTD.DEPTN
-------------------- --------------------
10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DEPTNO
--------------------------------
1 0

and test another sql:
lizh@lizhdb>column deptno format a30
lizh@lizhdb>select d.deptno,
2 cursor (select deptno
3 from dept)
4 from (select to_char(deptno) deptno from dept) d;

DEPTNO CURSOR(SELECTDEPTNOF
------------------------------ --------------------
10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

DEPTNO
------------------------------
10

Hi Tom,

Is AL32UTF8 and UTF-8 is same character set or they are different?
Is UTF-16 and AL16UTF16 is same character set or different ?

Thanks

Hi Tom,

If, for any reason, we want to convert UTF-8 back to WE8ISO8859P1, can we do that and what is the easiest way to do so?

Thank you for your help.

When we run the following query why do we get this error ?

SELECT COMPANY_NAME
FROM COMPANY
UNION
SELECT '' AS COMPANY_NAME
FROM COMPANY

SELECT COMPANY_NAME
*
ERROR at line 1:
ORA-12704: character set mismatch

The column COMPANY_NAME is defined as NVARCHAR2(200).

We have all UTF8 data. Althout it is UTF8 there is only English data right now. Why is it mandatory to put an N before the ''.

This query works :

SELECT COMPANY_NAME
FROM COMPANY
UNION
SELECT N'' AS COMPANY_NAME
FROM COMPANY

Why cannot Oracle automatically convert it ? Or it supposed to work like that ? This works in SQL Server.

because nvarchar2 <> varchar2

and '' is a varchar2

and company_name is a nvarchar2

so -- they "mismatch".


it would be like

ops$tkyte@ORA10G> select 1 from dual
  2  union all
  3  select '1' from dual;
select 1 from dual
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

you have two imcompatible datatypes. 
 

The error we are getting is

SELECT COMPANY_NAME
*
ERROR at line 1:
ORA-12704: character set mismatch

Doesn't this indicates that the character set is different.

The parameters we have are

NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

Both are compatible, right ?

Here are some fields including Swedish characters that we want to load 
Vendor Name: Björneborgs Lådfabrik AB
Address Line 1: Köttgränd 4
Postal Code: 740 22
City: Bälinge
Contact First Name: Åke
Contact Last Name: Möhré
Bank Name: Föreningssparbanken
Bank Branch Address Street: Smålandsvägen 32
Bank Branch Address City: Västervik

    Here are some questions that I have:
·    How will supplier names with special characters impact the load of data into the datawarehouse?  Will the data be rejected or accepted?  Will the data display in Business Objects reports with special characters or without?
·    How will supplier names with special characters impact the load of data into a Java based application?  Will the data be rejected or accepted?  Will the data display in on line with special characters or without?
·    Can queries be run in a Java based application if the suppliers have special characters?  Is there a workaround?
·    Can queries be run in Oracle if the suppliers have special characters? Since the keyboards in US, Mexico, Hungary do not have special characters.  Is there a workaround? Like using ASCII.


    SQL> select * from V$NLS_PARAMETERS
    
    PARAMETER                         VALUE
    ------------------------- -----------------------------------
    NLS_LANGUAGE                          AMERICAN
    NLS_TERRITORY                         AMERICA
    NLS_CURRENCY                      $
    NLS_ISO_CURRENCY                  AMERICA
    NLS_NUMERIC_CHARACTERS            .,
    NLS_CALENDAR                          GREGORIAN
    NLS_DATE_FORMAT                   DD-MON-YY
    NLS_DATE_LANGUAGE                 AMERICAN
    NLS_CHARACTERSET                  WE8ISO8859P1
    NLS_SORT                              BINARY
    NLS_TIME_FORMAT                   HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT            DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT                HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT           DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY                 $
    NLS_NCHAR_CHARACTERSET            UTF8
    NLS_COMP                              BINARY
    NLS_LENGTH_SEMANTICS              BYTE
    NLS_NCHAR_CONV_EXCP               FALSE
    
    19 rows selected.
    
    SQL> select * from NLS_DATABASE_PARAMETERS
      2* where parameter ='NLS_CHARACTERSET'
    SQL> /
    
    
    PARAMETER                     VALUE
    -------------------------     -----------------------------------
    NLS_CHARACTERSET          WE8ISO8859P1
    

Thanks for the wonderful service 

Thanks Tom.But supposed I just wanted to replace a special character like å,ä etc.with say "a" and "A".How do I do it?

Tom,
  I have a table temp_tilde2 - someone else created it so I don't know how they inserted the record. When I try to insert a record using ASCII equivalent 148  of say an o(letter o in lower case) with two dots on top this is what I get this :

SQL>  desc tilde_temp2;
 Name                            Null?    Type
 ------------------------------- -------- ----
 VENDOR_NAME                              VARCHAR2(240)

SQL> select * from tilde_temp2;

VENDOR_NAME
----------------------------------------------------------------------------
--------
Lövånger Elektronik AB

SQL> col value format a30
SQL> select * from NLS_DATABASE_PARAMETERS where parameter like
'%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               UTF8
NLS_NCHAR_CHARACTERSET         UTF8

SQL> select dump(vendor_name) from tilde_temp2;

DUMP(VENDOR_NAME)
----------------------------------------------------------------------------
------------------------
Typ=1 Len=24:
76,195,182,118,195,165,110,103,101,114,32,69,108,101,107,116,114,111,110,105
,107,32,65
,66

SQL> insert into tilde_temp2 values ('A'||chr(195)||chr(182)||'BCDE');

1 row created.

SQL> select * from tilde_temp2;

VENDOR_NAME
----------------------------------------------------------------------------
Lövånger Elektronik AB
A¿BCDE

SQL> Delete from tilde_temp2 where
vendor_name like 'A%'

1 row deleted.

SQL> insert into tilde_temp2 values ('A'||chr(148)||'BCD');

1 row created.

SQL> select * from tilde_temp2;

VENDOR_NAME
----------------------------------------------------------------------------
-
Lövånger Elektronik AB
A¿BCD

SQL> 

Why am I not getting an o with double dots on top? 

Thanks
 

Tom,
   Thanks for the reply. I am using SQL Plus to connect to the database. How do I find the client's character set. Here is the output you requested.

SQL> select dump (substr(vendor_name,2,1)) from tilde_temp2
/

DUMP(SUBSTR(VENDOR_NAME,2,1))
--------------------------------------------------
Typ=1 Len=2: 195,182
 

Thanks Tom,
I knew in Unix. Yes I have Windows and the registry setting is

NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1

Can I change it to UTF8?

How does Oracle handle day light savings times? Does it automatically change it for example, if I set my database time zone to US/PST? Thanks.

<quote>they differ in the descriptive
timezone text that comes with it.<quote>

Could you elaborate on this? Thanks.

so, If I set database time zone US/PST, it will fall back an hour and Spring forward an hour automatically?

Thanks.

(1) should I set my time zone for the database to US/EDT to take care of this. Is there US/PDT for pacific?

(2) If I don't use the tz as EDT, then oracle would not take care of day light savings?

Thanks.

so, can db timezone be different from server (os) time zone? When you say whatever we set at os level we can use it, then why do we have to set time zone for the database? Maybe I am missing a point here. Thanks.

My understanding is that in 8i any character set can be used for National Character set. I was wondering what is the point of using US7ASCII as National character set as it cannot store char that needs more than a byte? Thanks.

My database is currently us7ascii, everyone is happy, no globalization needs yet. There is this one reporting app that we are considering that needs some database to park its metadata, needs minimal storage (<100MB). For Oracle, it requires the characterset to be UTF8, the installation aborts if it is not.

Does it make sense to convert the entire database to AL32UTF8 just for the sake of this app?

The other alternative is to create another 100MB database containing just this one schema, but then we have another little production database to manage, with its associated admin overhead.

Comments?

Thanks

Hi Tom,

We have two databases, a new one (9iR2) uses UTF8 and the old one (8.1.4) uses WE8ISO8859P1. In both databases, there is a table that has NVARCHAR2 column (the tables are identical in both database). Currently, tables only have western characters. We want to transfer some new data into the old table. We can do the following in the new UTF8 database:

insert into t_we8@db_link_to_old select * from t_utf8;

The query is executed without any errors. However, when we select from the t_we8 table, there is no data showing in the NVARCHAR2 column.

Please help us with that. What is the correct way to transfer UTF8 data to WE8ISO8859P1 with NVARCHAR2 column?

Thank you in advance.

Hi Tom,

We are in Oracle 9.2.0.6 on which I have activated isqlplus. I am just trying to see sysdate in arabic for this reason in isqlplus I am trying following

alter session set nls_language='ARABIC';

select to_char(sysdate, 'DAY') from dual;

I am also using IE 6 in this I am doing setting view -> encoding -> arabic ( tried dos/windows ..etc ) tried unicode ...etc

But still it is showing question marks or some junk characters. Pl guide me where I have done mistake and how to correct it ?

regards & thanks
pjp

Check the database characterset.

If I remember correctly it should be one
of AR8ISO8859P1/P6

UTF-8 should work as well. I have not tried this one for Arabic.

I forgot to include one more point in addition to database characterset.

Your NLS_LANG should be set appropriately as well.

Please refer to Oracle manual - NLS_LANG session to see a valid list of NLS_LANG settings and choose the one for Arabic.

Example : NLS_LANG=Arabic.United Arab Emirates.AR8MSWIN1256

Hi Tom,

I am planning to set multilingual database specially for asian language for an application in oracle 9.2.0.6. As a first step I am trying to see day of the month in native language. For e.g. in Arabic or Hindi or Tamil ...etc. My understanding is that your frontend should able to display these speical characters. For this reason I am trying to see special characters thru isqlplus and setting IE6 for language support, which is failed.

Can you or anybody tell me how to do for asian language with an example ? ( isqlplus is not a mandatory requirement for me)

regards & thanks
pjp

Hi Tom,

thanks for your valuable help to all oracle users/dbas for giving more insight to oracle. As per your suggestion I have tried to set nls_lang ( set nls_lang=HINDI_INDI.UTF8 ) and invoke sqlplus by setting up language in Windows XP SR3 but still it is showing junk characters. Will you pl show me by an example for asian language

1. setting up nls_lang
2. executing sysday in native language
3. storing sysday in a table
4. extracting a data from a table

regards & thanks
pjp

Greetings Tom,
In one of our systems upgrade programme we are migrating 7.3 database to 10g. The charcterset was US7ASCII. As a part of upgrade we also need to cater to Celtic language support. Oracle is providing CEL8ISO8859P14 characterset for supporting Celtic languages. I opine that we should use this characterset. However, the data architects believe the database characterset should be UTF8, since it would take care of Celtic language. As for the issue of length semantics of BYTE or CHAR, they recommend setting the session NLS_LENGTH_SEMANTICS parameter, so that we can use all the sql scripts, stored procedures etc. They opine that storage is not an issue, so we should go for UTF8, even if the database balloons to a bigger size. I read in one of your answers above on this issue about string manipulations being CPU intensive, may get slower, if the database characterset is set to UTF8. On checking the code and business logic, I found there isn't much of string manipulations such as substr or instr to make life worse.
I feel UTF8 is an overkill.
Is there any other issue, I might have overseen that may help me in convincing the data architects to consider CEL8ISO8859P14 over UTF8.
Regards
Jeeva

Thanks Tom,
Your view has struck a chord in me. There isn't anything to argue about on this issue.

Hi Tom,

I'm in the process of creating an Oracle 10g database. No specific user request with respect to the character set. Given this situation, which do you recommend WE8ISO8859P1 or UTF8?

Thank you

Face it... just because you have no intention of using other character sets that does not mean that everyone else will cooperate with you.

Look here: &#20013;&#25991;

I just typed some Chinese. Doesn't matter what you planned - I didn't cooperate.

What will you do when you integrate with a vendor system and you find out that some product descriptions have randome foreign characters? Or double length english like this: &#65316;&#65359;&#65365;&#65346;&#65356;&#65349;&#12288;&#65324;&#65349;&#65358;&#65351;&#65364;&#65352;&#65311; Or Asian style punctuation like this&#65311; &#12290;&#12290;&#12290;&#12290;&#65292;&#65288;&#65289;

These days Unicode is the default choice&#12290;

Tom I have imported several tables that contain arabic data which I would like to convert to english. Whats the best way to do this?

Ok so there is no way we can do this with NLS_LANG, TRANSLATE or CONVERT functions? If not do you recommend any 3rd party software that can do this?

Are you really asking if the Oracle DB can translate Arabic text into English? Machine translation?

I'll admit it's not totally unreasonable - the company that created the Oracle Context technology (now part of Intermedia) was working on machine translation before Oracle bought them - but it's not on the horizon for any of the major DB vendors.

There are third party tools that can do a semi-decent job that provides a good starting point for human translators (ie. Systrans) but I suspect that you are looking for something different like a Roman character transliteration.

Suggest you clarify your requirement.

Hi Tom,

Pl explain the pros of sets like UTF8 vs. western alphabet sets - performance tradeoffs.

some example will be much helpfull

pro of utf8, it can hold any data, it is multibyte
con of utf8, it can hold any data, it is multibyte

Maybe this beta version of what'll be in my next book will help...

<quote>

NLS Overview
NLS stands for National Language Support and it is a very powerful feature of the database, but one that is often not as well understood as it should be.  NLS controls many things about our data – how it is sorted, whether we see commas and a single period in a number – eg: 1,00,000.01 -- or many periods and a single comma , 1.000.000,01.  But most importantly, it controls the encoding of the textual data as stored persistently on disk and it transparently converts data from character set to character set.  It is this transparent part that confuses people the most – it is so transparent you cannot even really see it happening.  Let’s take a small example.

Suppose you are storing 8 bit data in a WE8ISO8859P1 character set in your database, but have some clients that connect using a 7 bit character set such as US7ASCII.  These clients are not expecting 8 bit data and need to have the data from the database converted into something they can use.  While this sounds wonderful, if you are not aware of it taking place, you might well find your data to “lose” characters  over time as the characters that are not available in US7ASCII are translated into some character that is.  This is due to the character set translation taking place, in short, if you retrieve data from the database in character set 1, convert it to character set 2, and then insert it back (reversing the process) there is a very good chance that you have materially modified the data.  Character set conversion is typically a process that will change the data and we are typically mapping a large set of characters (in this example the set of 8 bit characters) into a smaller set (that of the 7 bit characters).  This is a lossy conversion, you modify the characters,  – we quite simply cannot represent every character.  But, it must take place – we have to do this conversion.  If the database is storing data in a single byte character set but the client (say a java application, that language uses Unicode) expects it in a multi-byte representation, it must be converted, simply so the client application can work with it.

You can see character set conversion very easily.  I have a database whose character set is set to WE8ISO8859P1, a typical Western European characterset:

ops$tkyte@ORA10G> select *
  2  from nls_database_parameters
  3  where parameter = ‘NLS_CHARACTERSET’;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1

Now, if I ensure my NLS_LANG is set the same as my database characterset (Windows users would change/verify this setting in their registry):

ops$tkyte@ORA10G> host echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

We can create a table and put in some “8 bit” data – data that will not be usable by a 7 bit client, one that is expecting only 7 bit ASCII data:

ops$tkyte@ORA10G> create table t ( data varchar2(1) );
Table created.
ops$tkyte@ORA10G> insert into t values ( chr(224) );
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( chr(225) );
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( chr(226) );
1 row created.
 
ops$tkyte@ORA10G> select data, dump(data) dump
2    from t;
D DUMP
- --------------------
à Typ=1 Len=1: 224
á Typ=1 Len=1: 225
â Typ=1 Len=1: 226 
ops$tkyte@ORA10G> commit;

Now, if we go to another window and specify we are “a 7 bit ASCII” client, we’ll see quite different results:

[tkyte@desktop tkyte]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[tkyte@desktop tkyte]$ sqlplus /
SQL*Plus: Release 10.1.0.4.0 - Production on Mon May 30 15:58:46 2005
Copyright © 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10G> select data, dump(data) dump
2   from t;
D DUMP
- --------------------
a Typ=1 Len=1: 224
a Typ=1 Len=1: 225
a Typ=1 Len=1: 226

Notice how in that session we received the letter ‘a’ three times, no diacritical marks.  However, the DUMP function is showing us that in the database – there are in fact three separate distinct characters, not just the letter ‘a’.  Our data in the database hasn’t changed, just the values this client received.  And in fact, if this client were to retrieve that data into host variables:

ops$tkyte@ORA10G> variable d varchar2(1)
ops$tkyte@ORA10G> variable r varchar2(20)
ops$tkyte@ORA10G> begin
2  select data, rowid into :d, :r from t where rownum = 1;
  3  end;
  4  /
PL/SQL procedure successfully completed.

And do nothing whatsoever with it, just send it back to the database:

ops$tkyte@ORA10G> update t set data = :d where rowid = chartorowid(:r);
1 row updated.
ops$tkyte@ORA10G> commit;
Commit complete.

We would observer in the original session that we have lost one of the original characters, it has been replaced with the lowly 7 bit ‘a’, not the fancy ‘a’ we had previously:

ops$tkyte@ORA10G> select data, dump(data) dump
  2  from t;
 
D DUMP
- --------------------
a Typ=1 Len=1: 97
á Typ=1 Len=1: 225
â Typ=1 Len=1: 226

That shows the immediate impact of an environment with a heterogeneous character set, where the clients and database use different settings.  It is something to be aware of, if you were not expecting it.  It comes into play in many circumstances – for example, if the DBA uses the EXP tool to extract information, they may note:

[tkyte@desktop tkyte]$ exp userid=/ tables=t
Export: Release 10.1.0.4.0 - Production on Mon May 30 16:19:09 2005
Copyright © 1982, 2004, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
…
That warning should be regarded very seriously.  If you were exporting this table with the goal of dropping the table and using IMP to recreate it – you would find that all of your data in that table was now lowly 7 bit data!  Beware the unintentional character set conversion.

But also be aware that in general, they are necessary, if the client is expecting data in a specific character set – it would be disastrous to send them the information in another character set.

NOTE: I highly encourage everyone to read through the Oracle “Globalization Support Guide”.  It covers NLS related issues to a depth we will not here.  Anyone creating applications that will be used around the globe, or even across international boundaries – and who isn’t today – needs to master the information contained in that document.

Now that we have a cursory understanding of character sets and the impact they will have on us, let’s take a look at the character string types provided by Oracle.
</quote> 

Hi Tom,

We have an Entity Bean which has got a CMP field of type String which is mapped to NVARCHAR2 datatype of ORACLE 9.2.
We are using weblogic 9.0 Beta as application server. While inserting a row in to the Database by creating an entity bean we are getting the following error: "ORA-12704: character set mismatch" for NVARCHAR2 datatype.

The above thing is working fine for WEBLOGIC9.0 beta/MSSQL, JBOSS4.0.0/MSSQL and JBOSS 4.0.0/ORACLE 9.2 combinations.

I have tried the following ways for fixing it:
1) Configured the connection pool with the different ORACLE drivers
2) Replaced nls_charset12.jar with orai18n.jar
3) set NLS_LANG=AMERICAN_AMERICA.UTF8 in the server startup script StartWeblogic.cmd
4) Created Database with characterset UTF8 and National CharacterSet UTF8

Still the problem is not fixed I tried with "goolge.com" to fix this problem but I not get any solutions. Please give me the Solutions from your end to fix this problem.

Thanks for your
Raj

Hi tom ,

i am working on Internationalization stuff in Oracle 10g DB.
i need to migrate data from oracle 10g R1 (western alphabet sets ) to oracle 10g R1 "normalized DB" (proposed version ) and from oracle 10g R1 to SQL server 2000 Both oracle 10g and SQL server reside on Windows 2003 Server (Enterprise) "DOT NET platform"

i have table with the following structure

Source DB

LOCALE_TABLE
=============
PK_LOCALE_ID NUMBER PRIMARY KEY
LOCALE_LANGUAGE VARCHAR2(50)

CONTACTS_TABLE
=================
PK_CONTACTS_ID NUMBER PRIMARY KEY
FK_LOCALE_ID NUMBER
CONTACT_NAME VARCHAR2(50)
DESCRIPTION VARCHAR2(100)

Target DB
=========
CONTACTS_TABLE
=================
PK_CONTACTS_ID NUMBER PRIMARY KEY
FK_LOCALE_ID NUMBER
CONTACT_NAME VARCHAR2(50)
DESCRIPTION NVARCHAR(100)


The source data is in western alphabet sets ,after migration When an user selects a locale as japenese/Tamil/Telugu from the UI(User interface)he should be able to see the description on japanese/Tamil/Telugu language or it can be any language used in this world.

1) what character set do you recommend to use in destination/target DB 10g R1 or R2 (source is in western alphabet sets )

2) can i go ahead using western alphabet sets as db character set for target and set National CHARACTER SET (not the db character set) to UTF-8 or ALUTF8 or ALUTF16.
is this a right/wrong approach , which unicode should i use for DB and National CHARACTER SET .

3)when using windows 2003 server is it recommended to use UFT 16
</code> http://msdn.microsoft.com/library/default.asp?URL=/library/techart/IntlFeaturesInSQLServer2000.htm <code>

4)say "Hello" is stored in a db table column, should i stored the equavelent language unicode value in a seperate table so that i can view them.

Thanks
Thirumaran

Hi Tom,

I am having Japanese data in the Multibyte database. Is it possible to translate few fields in a table into English?

Please let us know. On going through your replies, I am not able to find it in your replies.

We have a situation where the data could be in Chinese Traditional character or
Chinese Simplified. However, we are not able to distinguish which one is what.

The people from Asia Pacific region needs to convert to UTF8 format and should send the data to us
in form of a text file.
They are using CONVERT function, but the data is in two different formats and one of them is coming out
with junk characters. The character set they are using is WESTERN EUROPEAN (WE8ISO8859P1).
Probably they are setting the NLS_LANG at the host level to UTF8.

Is there a way to get the data converted to UTF8 irrespective of the source character set?

What I am thinking is,set the NLS_LANG to Chinese Traditional at the host level.

Convert the column to chinese simiplified. If the data is already chinese simplified, it would
return as it is. Later convert it again to UTF8.

Assuming my column name is ADDRESS.

At the unix level, I would do
export NLS_LANG='CHINESE TRADITIONAL'

I thought of using CONVERT(ADDRESS,'CHINESE SIMPLIFIED').
This would convert chinese traditional to chinese simplified and the chinese simplified data would
return as it is. Is this assumption correct?

Later using the convert function again on top of that to convert to UTF8.

So my final CONVERT statement looks like:

CONVERT (
CONVERT(ADDRESS,'CHINESE SIMILIFIED'),'UTF8');

Does this work for me?

Thanks,

Hi Tom,

One of our database has been configured for the below mentioned nls parameters: However we are receiving the logs and SQL*LOADER is pumping the data in the database tables and the jobs are running on the server itself.There are no clients involved. One of our findings reveal that we have certain columns having the data as ??????????????????????????????. On further investigation (the dump of that column) it has been seen that the string has chr(239),chr(191) and chr(189) represenatation values.

------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=144: 239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,1
89,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,1
89,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,1
89,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,1
89,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189

sys@DWADM> Select chr(239) from dual;
C
-


sys@DWADM> Select chr(191) from dual;
C
-
?

sys@DWADM> Select chr(189) from dual;
C
-
?

sys@DWADM>

sys@DWADM> Select * from V$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YYYY
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

19 rows selected.

What should we change so that the representaion of these characters does n't show the ??????????.

Regards,
Vikas

tOM,
We are running a J2EE application against an Oracle DB. We are in the process of changing the character set of the DB from WE8ISO8859P1 to UTF8. We have executed char scan and are increasing the width of the columns as reported by character scan tool.

We are looking at changing the registry and enviroment variable setting for NLS_Lang on the application server (client to the DB).

My questions
1) What should nls_lang be set to on the client to the DB (Oracle app server machine) if the DB server has UTF8?
2) What changes should be made on the client to the App server (the end users machine)?
3) Are ther any other changes other than char scan on the DB, NLS Lang on the cleint machines that need to be looked into?

Thank you

I would like to get some guidance/examples for testing UTF8 conversion.

The following has been done so far
We did a character scan, fixed items reported by character scan,
Converted the db character set to UTF 8
Changed nls_lang on the db server, app server to UTF8

The test I have in mind is
Create a temp table with a varchar2 column.

Inserting data
1) Get the dump code of a few special characters(japaneses characters)
2) Log into SQLPlus on the db server.
3) Insert these special characters into the temp table, logging from SQLPlus on the db server.
4) Check the dump code of the data inserted.
5) If dump code values in step #1 and step #4 are the same, then test is successful.

Other test
1) Log into SQLPlus from the application server and do the same test.
2) Log into the application and insert data into the temp table or one of the different tables.

Compare the before and after dump code values, if all of them are the same then data is being inserted properly.


What I woud like to know is
1) Are the test cases I have correct?
2) Can you provided an example which shows the before and after dump values.

As always, appreciate all your help

Might also try some the methods discussed on related threads, such as </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10250420313043#29873762616953 <code>

hi tom,

i want to create a comma seperated *.csv file by using a simple sqlplus script and spooling the results.

the data is stored in a 9.2 database having UTF8 as database characterset.

my question is whether it is enough to set the client characterset to UTF8 too for getting the spool file in UTF8 characterset?

i thought about the font. is it necessary to have an appropriate font even if i just want to store but not display the data?

hi tom,

thanks for your answer. let me refine.

the problem i have is to unload data from an UTF8 database, create a *.csv file und load the data into another database - propably also UTF8 - using this *.csv file in following.

i'm not sure about what i have to consider regarding the characterset theme in that case!? will it be enough to change the client characterset to UTF8, unload and reload the data? or will i have conversion errors in that case?

Why not ASCIISTR any string columns when spooling the contents into a CSV, then UNISTR when reading the results?

hi tom,

What is the main different between CHAR & NCHAR? As we know both data type can hold 2000 bytes length of data. NCHAR is a Unicode only datatype, and CHAR is not.

I have created a TEST table to store both the CHAR & NCHAR columns, and started to insert ASCII character and unicode character between both. Both are inserted successfully. I hardly differentiate both. CHAR value can store Unicode character as well.

When is the right time to use NCHAR, and when it is not? Can you show us some example?

TQ

Depends on your database setup if these are even "different".  Depends on your client characterset as well.

Here is a short snippet from my recent book on them, chapter on datatypes:


<quote>
The “N” Variant

So, of what use are the NVARCHAR2 and NCHAR (for completeness)? They are used in systems where the need to manage and store multiple character sets arises. This typically happens in a database where the predominant character set is a single-byte fixed-width one (such as WE8ISO8859P1), but the need arises to maintain and store some multibyte data. There are many systems that have legacy data but need to support multibyte data for some new applications, or systems that want the efficiency of a single-byte character set for most operations (string operations on a fixed-width string are more efficient than on a string where each character may store a different number of bytes), but need the flexibility of multibyte data at some points. 

The NVARCHAR2 and NCHAR datatypes support this need. They are generally the same as their VARCHAR2 and CHAR counterparts, with the following exceptions:

    *    Their text is stored and managed in the database’s national character set, not the default character set. 

    *    Their lengths are always provided in characters, whereas a CHAR/VARCHAR2 may specify either bytes or characters.

In Oracle9i and above, the database’s national character set may take one of two values: UTF8 or AL16UTF16 (UTF-16 in 9i; AL16UTF16 in 10g). This makes the NCHAR and NVARCHAR types suitable for storing only multibyte data, which is a change from earlier releases of the database (Oracle8i and earlier allowed you to choose any character set for the national character set).
</quote>

ops$tkyte@ORA9IR2.US.ORACLE.COM> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
...
NLS_CHARACTERSET               WE8ISO8859P1
...
20 rows selected.


Here is a "quick" difference:

ops$tkyte@ORA9IR2.US.ORACLE.COM> create table t ( x varchar2(1), y nvarchar2(1) );

Table created.

ops$tkyte@ORA9IR2.US.ORACLE.COM> insert into t values ( unistr( '\00d6' ), unistr('\00d6' ) );

1 row created.

ops$tkyte@ORA9IR2.US.ORACLE.COM> select dump(x), dump(y) from t;

DUMP(X)
---------------------------------------------------------------------------------------------------------
DUMP(Y)
---------------------------------------------------------------------------------------------------------
Typ=1 Len=1: 214
Typ=1 Len=2: 0,214


<b>the unicode was converted to we8iso for X, stored "naturally" for Y - different number of bytes...</b>
 

hi tom, me again,

maybe we can and will do the data transfer using exp/imp. but we will have to do it via a *.csv file at this time! besides, it's also a very interesting problem!

so again, how can we transfer UTF-8 encoded data via a spool file? using this asciistr / unistr approach another user stated? or are there any better ways?

Tom

I want to insert Romanian values into table:

O/S - WinXP

set NLS_LANG=ROMANIAN_ROMANIA.EE8MSWIN1250


SQL> create table try (name varchar2(100));

Table created.


SQL> insert into try values('Rãmâneþi conectat în toatã');

1 row created.

SQL> select * from try;

NAME
----------------------------------------------
Ramâne_i conectat în toata

But when i perform the select, certain characters are not displayed as it sholuld be, for instance the second character is different from what we inserted.

Kindly advise where am i wrong. 

Hi Tom,

JAVA <------> JDBC <-------> Oracle
===================================
JAVA:UTF8(JDBC 2.0)
Oracle:ZHS16GBK (9.2.0.5)
===============
Unicode(UTF8) is used in JAVA.
ZHS16GBK Character set is used in Oracle Database.
The language is Chinese.
-------------------------------
I worry about the different of character set between JAVA and Oracle will cause a problem of Character conversion.
It is superset to subset when writing data from JAVA to Oracle.Will the conversion cause any issue? Can the JDBC driver convert it rightly?Could you give me a detailed and exact explanation?

Thanks

HI Tom,
Is their a link where we can see a list of all the characters supported by a specific character set.. I tried looking on metalink and in globalization doc but could not find it. We are mainly interetsed in WE8ISO8859P1 character set and if we could get a list of all the french characters supported by it.....

Thanks for your help as always .

As usual you are right on target......

Thanks a lot.

Well, I'm practically giddy. I tried Raptor with the Romanian test posted by Kshitij back up this thread at:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5783936214008#53305665471667 <code>
Using the script:
create table try (name varchar2(100));
insert into try values('Rãmâneþi conectat în toatã');
select * from try;
these results were displayed:

NAME ------------------------------------------------------------
Rãmâneþi conectat în toatã

1 rows selected

Never could do that in SQL*PLUS !!

</code> http://forums.oracle.com/forums/thread.jspa?threadID=351699 <code>

Hi Tom,

We are using multilingual database for our application. I have a doubt in pl/sql coding.

Consider following code,

create or replace procedure test_proc(v_flag char)
is
begin
if v_flag = 'Y' then
dbms_output.put_line('display True');
else
dbms_output.put_line('display False');
end if;
end test_proc;
/


Here I am checking whether the parameter passed is True or False. The parameter value is given by user from frontend (java).

Now in case of Thai and Japanese characterset(just for example, there can be other languages as well), these characters will differ.

I want to know, how can I handle such kind of situation for all the languages at a time so that I need not do any kind of code change for various languages.

Can you help in this case?

Regards,
Archana.

Dear Tom.
Both client/server OS is Win 2000.
My 10G DB Characterset is UTF8. And NLS_LANG set to AZ8ISO8859P9E at client side.

Using sqlplusworksheet:

insert into valyuta (valyutaname) values('ü');
insert into valyuta (valyutaname) values('ö');
insert into valyuta (valyutaname) values('&#287;');
insert into valyuta (valyutaname) values('&#305;');
insert into valyuta (valyutaname) values('&#601;');

select * from valyuta;

Result:
VALYUTAID VALYUTANAME
---------- --------------------
46 ü
47 ö
48 ?
49 ?
50 ?

5 rows selected.

Sure, I got AZ8ISO8859P9E code page from Oracle Locale Builder list. All above characters are inside it.

What can I do?

Thanks in advance.

Hi Tom,
at the moment we have:
PARAMETER VALUE
------------------------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

Now, there is a need to store customer data (in rusian,chinese) in some of the fields (name, description,owner etc).

What would you do? convert 200 columns to NVARCHAR or go for reinstalling db with real unicode char set?

For me, using NVARCHAR has advantage that all 'db internal' strings are still 'small' and probably faster (like table names) and only specific columns which need to store multibyte chars are multibyte. Any disadvantages you can see?

In that case, would would be migration, simply:
insert into <new_table>
select convert(lastname,'we8..','al16'),.. from <old_table> ?
Thank you for all useful info!

Thanks, yes you are right - if we test it all then do a complete solution.
But one more interesting thing i read in the guide: "Your SQL statements and PL/SQL code contain Unicode data: You must use a Unicode database ... A common place to use Unicode data in a SQL statement is in a string literal."
So what exactly is meant by sql statement (containing string literal), really raw sql statement without values for bind variables? Or in other words - in a "non unicode" db, if you use a bind variable, can you insert a multibyte value in a NVARCHAR column as a 'simple' string?

Hi Tom
My database's character is utf8, I have a table which has a clob column, my client is windows xp simplified chinese version, my nls_lang is .zhs16gbk, I saved a chinese text and used dbms_lob.loadfromfile to load chinese into that clob table, but display of selecting that table is crazy code but not correct chinese code, is it related to database utf8 character?

Thanks!

I'd take a look in the docs to dbms_lob.loadCLOBfromfile, the charset-aware version of dbms_lob.loadfromfile ...

As per suggestion from Oracle, "recommended characterset for 10g OCS database is AL32UTF8". But my database NLS_CHARACTERSET is UTF8. In application tier, OPMN.XML, we have
<variable id="NLS_LANG" value="AMERICAN_AMERICA.AL32UTF8" append="false"/>

Do we need to change the value from AL32UTF8 to UTF8 in the OPMN.XML file or need to change NLS_CHARACTERSET in 10g OCS database to AL32UTF8 from UTF8?

We were getting ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at "ES_MAIL.ES_MESSAGE
_API", line 2496

For the above error we were suggested to change the value from AL32UTF8 to UTF8 in OPMN.XML file. Please advise.

Tom:

Based on what you said below, does that mean if I set my client (SQL*plus) session to UTF8 it will convert my data coming out of the RDBMS in WE8ISO to UTF8 on the client.
<
just set your NLS_LANG correctly -- you will find that your NLS_LANG is set
differently in 8i then in 9i. This CLIENT setting dictates to the database
what character set it should expect from the client and the characters get
converted appropriately. >

Hi Tom,
We are in the process of developing our application to support Chinese, Japanese, Korean and Western European languages. Therefore, we agreed to use AL32UTF8 as character set and AL16UTF16 as national character set.

Our consultant is recommending to change every VARCHAR2 to NVARCHAR2 and CHAR to NCHAR.
Following is their explanation why we need to change VARCHAR2 to NVARCHAR2 and CHAR to NCHAR.
Could you advise if there is performance concern if type is NVARCHAR or VARCHAR because of conversion even after the database is unicode. My understanding is, if client is using different character set; character conversion occurs irrespective of NCHAR or CHAR. Could you please advise.
Thank you,
V
Consultant’s explanation:
The server database character set specifies the character set to be used for varchar data. It is also the character set that is used when converting between the client and server. To store multi-lingual text in a varchar2 field, you’ll want to be sure to specify the server database character set as UTF-8. This will limit any loss of data transferred between client and server. The client character set is usually specified in NLS_LANG. If this is not set to a character set which is a subset of the server database character set then you’ll likely lose data (on insert, select, etc.).

The national character set is used for nvarchar data. This is always Unicode.

Runtime itself is based on Unicode and uses Oracle’s OCI wide char API. This means that there is no character set conversion required when manipulating nvarchar data. There is, however, conversion required when manipulating varchar data – NLS database charset <-> server database charset. Therefore, performance should be greater if you use nvarchar data types for your string data.

When comparing storage requirements, UTF-8 requires more storage for CJK (Chinese, Japanese, and Korean) languages – a minimum 3 bytes per kanji character, maximum of 4 bytes per character. UTF-8 requires less storage non-CJK languages – usually only 1 byte per char with a maximum of 2 bytes per character. UTF-16 always requires 2 bytes (and 4 bytes for very rare CJK characters). So overall, you may find you’ll need up to twice as much storage for switching to nvarchar data types.

Hi Tom,
Could you please advise if the following statement is correct:

Internally Oracle processes most characters in AL16UTF16 encoded strings as fixed-width characters, so Oracle can usually process UTF-16 encoded strings faster than UTF-8 encoded strings (which are variable width)
Thank you,
V

Hi Tom,
We are in the process of developing application to support CJK and Western
European languages.

We use AL32UTF8 as character set and AL16UTF16 for National character set.

We are developing application and using UTF-16 Unicode( OCI wide API) because of

faster string processing capability.

1.Is it good approach to change every CHAR and VARCHAR2 columns in the database
to NCHAR and NVARCHAR2 to avoid character set conversation from UTF 8 to UTF16.

2. Is conversation from UTF-8 to UTF-16 considerable overhead?

3.We have more Western European characters (85%) and few Asian characters; I am
guessing that if we convert every VARCHAR to NVARCHAR2 we may almost double the

database size and requires to double SGA size for same performance before
conversion. Could you advise if this is reasonable guess?
4.Our application team using UTF-16 for faster string processing (over UTF8
variable width) but database uses UTF-8 not UTF-16 for CHAR type, Is is good
approch to develop application to use UTF-8 instead of UTF-16 ?
Thank you,
V

Hello Tom,
I am using 10G Database.
I am having a table containing data in English Langauge.
There is a Front end Java Screen which fetches the data from this table and displays it.
This Java Screen must display the data in the Spanish Langauge.
Can you please advice me the steps to be performed to display the data in Spanish Language.
Thanks and Best Regards
-Hemal

If you're feeling mischievous, just implement a PL/SQL function using UTL_HTTP to call the Google translation service (translate.google.com/translate_t).

It's quite amusing to see people's eyes bug when you show them a form that can translate arbitrary Chinese into English and vice versa and you tell them you implemented it in PL/SQL.

Hi Tom,

We are generating XML using SQLX function and sending data to our sister company.When we send data in XML, some of the special characters are not handeled properly. 

for e.g. we have account like "Péter LLC".Here the XML that we are sending...
SQL> SELECT XMLELEMENT("ACCOUNT", 'Péter LLC') FROM DUAL;

XMLELEMENT("ACCOUNT",'PÉTERLLC')
--------------------------------------------------------------------------------
<ACCOUNT>Péter LLC</ACCOUNT>

out sister company want data in following format

<ACCOUNT>P'é'ter LLC</ACCOUNT>

They want 'é' instead of é. (Just putting quote so that you can see which character we need)

Can you please help?

Thanks

Tom,

SQL> SELECT XMLELEMENT("ACCOUNT", 'Péter LLC') FROM DUAL;

XMLELEMENT("ACCOUNT",'PÉTERLLC')
----------------------------------------------------------------------------------------------------
<ACCOUNT>Péter LLC</ACCOUNT>


we want chracter é  as Ampersand#233; in the output( I hope you can understand what I want)


Thanks

DB :- 9208
NLS_CHARACTERSET   :  UTF8
NLS_NCHAR_CHARACTERSET     : UTF8.

CREATE TABLE TEMP ( N NCLOB) ;

SQL> insert into temp values (1 ) ;
insert into temp values (1 )
                          *
ERROR at line 1:
ORA-12704: character set mismatch ;

SQL>  insert into temp values ('1') ;

1 row created.

Is this error related to Oracle not doing implicit conversion for nclob or its a genuine Character set issue.

 I ran this from the windows client with NLS_LANG SET AS utf8. I got same Error.

I ran this from the Unix DB server.
Got the same Error.

 Why this Error ?How to solve this. ?

Thanx

ops$tkyte%ORA10GR2> insert into t values ( sysdate ) ;
insert into t values ( sysdate )
                       *
ERROR at line 1:
ORA-12704: character set mismatch


ops$tkyte%ORA10GR2> insert into t values ( to_char(sysdate) ) ;

1 row created.

In our application when we are copy-pasting some text in text area and trying to save that in database, same text is not getting saved. What we were trying to save is: ¿jjjjjjjjjjj¿¿*******!!`sachin (copied and pasted the text from microsoft word), and what is getting saved is: ¿jjjjjjjjjjj¿¿*******!!`sachin. When I was trying to save the same text executing a procedure from sql*plus, text is getting saved properly. But from the application it is not the same.

Please suggest a solution. Do we need to change any character set?
I am using Oracle9i Enterprise Edition Release 9.2.0.1.0, in Windows XP. The character set that is used is: WE8ISO8859P1.

Actually in the previous post I wrote '"' and ''' in microsoft word and pasted, but it is showing like '¿'. Please give some suggestion. Whether we need to change character set or there is some other solution.

Hi Tom,
Thanks for your response.
The character set for both database and client are: WE8ISO8859P1.
Could you please tell me is there any other way to fix this issue? Will changing the character set help us? We need to support French, Spanish, English and Portugese language in the application.
If we change the character set to WE8MSWIN1252, will the problem be solved.
Please suggest.

Hi Tom,
10.2
RHEL 4
Ours is a client/server terminal based forms 6i application.
The reports are generated through Pro*c.
I need to store hindi data in some tables and these tables are further processed in Pro*c code.
I have created a testing database in AL32UTF8 character set and AL16UTF16 character set.
As per this link
[url] http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410565[/url]
I have set LC_ALL to UTF-8 and NLS_LANG=HINDI_INDIA.UTF8
Now when i query the table in GUI sqlplus, ican see hindi data but in command line mode ,the query shows empty blocks .
Questions:
1)How to see hindi data in command line mode of linux?
2)How to generate reports containing hindi data via pro*c?

Many Thanks

Regards

Hi Tom

We would like to consolidate our databases, and in doing this, we would like to downgrade the "surviving" databases national char.set from AL16UTF16 to UTF8.

It is NOT used in any application, and csscan reports 100% success (although it could not analyze some tables succesfully).

Are we in for many issues or could it work?

Br
Kim

Hi Tom,
I found this site very useful with lot of details.

We have a database with database character set WE8ISO8859P1 (NLS_CHARACTERSET) and national character set (NLS_NCHAR_CHARACTERSET)AL16UTF16.

I am able to store the names with extended ascii (é, Ñ etc). But when retrieving the same data (using perl script on linux), the extended characters comes as stripped of (e, N etc or ?).
I have set my Windows and unix (linux) NLS_LANG as AMERICAN_AMERICA.WE8ISO8859P1. I even tried using AMERICAN_AMERICA.UTF8. Still no luck.

I tried using dump on the names. The code associated with each letter looks correct. It gives code 233 equivalent to é.

Am I missing something, please clarify.

Thanks in advance.

If what you are doing is putting data into a file, you will need to make sure what ever tool you have will render the characters correctly. We thought we had a problem at one place I worked and it turned out it was working okay, but the default font for the tool we were looking at the data with made it look like it was being messed up.

Also UTF8 characterset is not the characterset of choice any more. AL32UTF8 is what should be used. The UTF8 characterset will not be expanded. AL32UTF8 will store the data in 1 - 4 bytes depending on the character. This helps the database to not be any bigger than necessary, but allows it to deal with multibyte characters. Do not move from the western european to UTF8. You are wasting your time. It will not deal with all the charactersets you are going to work with. I know because I did a conversion and the UTF8 characterset did not work. We converted Korean, Chinese, Japanese and Cyrilic.

Hi Tom,

I posted one question regarding Hindi data display in linux line mode terminal.It is in the fourth last comment of this topic.

Please guide regrading it


Thanks

Hi Tom,

We have a test environment and a development environment.

i have a clob column where data stored is compressed using gzip and then uncompressed back to its normal form during retrieval.

a simple ref cursor is used to return records to the front end, but this cursor fails to produce correct results when used in DEV environment but works perfectly well in test environment.

When i try to unzip the file in DEV, it says file format is invalid, but it gets unzipped in TEST environment

we have same locales installed in both the boxes. the only difference is that the Development box is in India and the test box in is UK

i checked init.ora and all the settings are the same for both the boxes.

the only difference that i could see is, in DEV, when the file is zipped before inserting, CRC is some d4...
but after retrieving, this CRC changes to some b4b4

But in the TEST environment the CRC remains the same. i think this is the cause of the problem

Hi Tom,

thanks for that quick reply

the explanation was really great...no wonder it wasn't working.

I will change the datatype to blob and then try out the same. i am sure it will work.

thanks again

Here are our db settings:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE


Would we be able to store "smart quotes" from Microsoft word cut/pasting in our db, or would we need something beefier - UCS-2 or UTF-16 characters?

Currently, customers cut/paste from word docs to their browser(conencting to a) JBOSS appserver to this database. The characters we see in the db are converted (ie - bullets and smart quotes are ?? or blocks).

Would we need something more then AL32UTF8?

Tom,

There was a question about the difference between the UTF8 (Character set) and UTF-8 (Encoding scheme). Your answer was very clear but one question that I have is why Oracle is using UCS-2 Encoding scheme for such functions as UNISTR. Wouldn't it be handier to just use UTF-8 to avoid confusion or does it have a reason?

Tom:

We are planning to upgrade several oracle 9iR2 databases (WEISO8859-1) to Oracle 11g.

Can you give me your opinion on the following:

1. Databases are mostly used to store book information in english. Some book titles are spanish and require spanish diacritics but i believe this is currently supported by ISO-88591-1.

Some mentioned created the 11g database as unicode to be able to generate xml files from a few columns in unicode format.

Would you consider UTF-8 or UTF-16 for the new database for these reasons? Is not UTF-8 same as ISO-88591 since they both use 8 bit encoding.

2. If we decided to do the unicode would it be a lot of extra effort in testing the charcter set conversion or it would be part (included) of testing the 9i->11g upgrade .

3. Some may argue in saying "what do we have to lose if go to unicode character set"? Can there be any data loss or performance impact or anything else.

thanks,

Tom:

can you elaborate on "unload and reload".

do you mean if you change characterset you have to unload data and reload it. I thought you do that when you upgrade too.

2. If the main reason is to get UNICODe out of the database do you suggest using NVARCHAR2 or CONVERT FUNCION.

I am not sure why people get hung up on unicode. most if not all xml editors should read iso-88591 with no issues.

I am inserting

r/hpo]\xe2\x9d\xc9nu\xeb)\xae\x87^^\xb2\xcaa

this line in DB table but when I did the select and spooled the record I got following output

r/hpo]\xe2\xbf\xc9nu\xeb)\xae\xbf^^\xb2\xcaa

Selected one is differend from that inserted .



DB NLS_CHARACTERSET ==AL32UTF8

client side NLS_LANG is set as follows :-
NLS_LANG = "AMERICAN_AMERICA.WE8ISO8859P1";

thanks And Regards,
Anupam Pandey

Whats the solution you suggest Tom.

Thanks And Regards,
Anupam Pandey

Hi Tom,

Greetings!

Below are my NLS Char setting in my DB

---------------------------------
NLS_NCHAR_CHARACTERSET UTF8
NLS_SAVED_NCHAR_CS UTF8
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN


we are using local languages ( Japaneese,korean etc ) to store the Vendors in my DB, now I want to extract the vendor names into English during SQL extrace, I tried convert function in SQL Developer, but still I am not able to see the converted from local language to English, please suggest me how to retrieve the local language value into English.

Below is the extract from my DB

select vendor_name,convert(vendor_name,'UTF8') FROM PO_VENDORS ;


Vendor_name Convert(vendor_name, 'UTF8')
------------------------------------------------------
(¿)¿¿¿¿-¿¿¿ (¿)¿¿¿¿-¿¿¿
(¿)¿¿¿¿ (¿)¿¿¿¿
(¿)¿-¿¿¿¿¿¿¿¿ (¿)¿-¿¿¿¿¿¿¿¿
(¿)¿¿¿¿¿¿¿¿¿-¿¿ (¿)¿¿¿¿¿¿¿¿¿-¿¿
(¿)¿-¿¿¿¿¿ (¿)¿-¿¿¿¿¿
(¿)¿¿¿¿¿¿¿¿ (¿)¿¿¿¿¿¿¿¿
(¿)¿¿-¿¿-¿¿-¿¿¿¿¿¿¿ (¿)¿¿-¿¿-¿¿-¿¿¿¿¿¿¿
(¿)¿¿-¿¿¿¿¿ (¿)¿¿-¿¿¿¿¿

I need the vendor name to be converted into English in the Extract, please suggest me.

Regards
Kiran

Hi Tom,

Thanx for your Response.

is there any possiblity in getting the records in local language while retrieving using SQL instead of garbled values, if there any possibility by using any standard functions, please suggest me.

Regards
Kiran

Hi Tom,
DB : Oracle 10g on AIX. We use Putty as Terminal Emulator.



$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

SQL> select * from v$NLS_PARAMETERS where parameter like '%CHARACTER%';

NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16

We got a file from our source who uses Sql Server on Windows. The export was on UTF8, which I tried to import in UTF8 with above NLS_LANG. Here is a sample record value in question.(look at apostrophe kind of char after E)

Patriarkhu Grigoriu E’ 11

After doing the CTRL+M removing, if I edit (vi) the file in Unix, it shows ^Y for that apostrope. SQLLDR loads the ^Y also (I saw in dump, ASCII 25).
I can not even paste that apostrophe on Unix window (comes as a dot), even after trying to change the Putty's Translation to UTF8 (just in case).

Tried SQLLDR in all permuations/combinations of CHARACTERSET(s) (and without any too).
I looked at ASCII value of the actual apostrophe, it is 146. Same result if I change Column datatype to/from CHAR/VARCHAR2.

Where am I off? Or I am entirely off? My guess is NLS_CHARACTERSET being US7ASCII and 146 doesn't fit into 7 bits.

Tom:

Can you advise the best solution for this.

I have a web page that is renedred from 9i database using ISO-8859-1.

I need that in UTF-8 format.

What would you do:

1) Create a new COLUMN NCLOB and store the xml file there. That would save it in UTF16 which is the national character set. Change the national character set from UTF16 to UTF8.

2) Use CONVERT(column1,source charset,destination charset) for each column in the cursor and then print he data. I also need to use CONVERT('any string on the web page',source, target)

3) Store the whole web page as a string and do CONVERT (big_string,source,target).
Now you have a new wb page in UTF-8

4) none of the above - do this instead (tell me what it is)



WEBPAGE 1 - ISO88591

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE package PUBLIC "+//ISBN 008-1-9//DTD Package//EN" "test.dtd">
<metadata>
<Title>The moving seas </Title>
<Creator>Tom, Jones </Creator>
...other database fields.
<x-metadata>
<meta name="Publisher" content="SEA"/>
.....other
</x-metadata>
</metadata>

WEBPAGE 2 - UTF8

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE package PUBLIC "+//ISBN 008-1-9//DTD Package//EN" "test.dtd">
<metadata>
<Title>The moving seas </Title>
<Creator>Tom, Jones </Creator>
...other database fields.
<x-metadata>
<meta name="Publisher" content="SEA"/>
.....other
</x-metadata>
</metadata>

Tom:

Thank you for the prompt response.

Page is generated using a pl/sql stored procedure that has 5-6 cursors and then HTML formatting section using HTP.PRINT(..)

Some HTP.P lines print the XML declaration lines and headers
like

htp.p('<xml .......');

and some print the data retreived from the cursor

htp.p(v_title);

The page is also retrieved using MOD_PLSQL which my understanding can upload/download files without character set conversion. I think mod_plsql is set to ISO-8859 too.

If using mod_plsql would not work then i have to store the file on a servr and have a retreive it via a link (bypassing mod_plql).

Please advise the best way to get this.

Tom:

I cant change the character set for the mod_plsql that easily. lot of paperwork needed for that.

But would not that screw up things in other places. I have hundreds of pages that use ISO-88591. It would be better to have client and database using ame character set rather than doing conversions all the time.

I would rather do this programmatically.

Here is what i came up with. Would this work

1. I can replace each HTP.p() call to PRINT call.
2. Save the whole page to CLOB variable
3. CONVERT the CLOB from ISO88591 to UTF8.
4. print the new utf8 variable.

The code is below. Do you think mod_plsql will still convert it to ISO-8859. I can also save the page to GTT or a regular table and download it since mod_plsql does not convert the charcter set that way.

PROCEDURE GENERATE_UTF8(p_magno IN VARCHAR2 DEFAULT NULL)



IS

l_my_var CLOB;
L_utf8_var CLOB;

CURSOR MY_CURSOR IS
SELECT col1,col2 from table1, table2
WHERE ........ result

PROCEDURE PRINT (p_string IN VARCHAR2)
IS
BEGIN
l_my_var := l_my_var || p_string;
END;


BEGIN

OWA_UTIL.mime_header ('text/xml', TRUE);
PRINT('<?xml version="1.0" encoding="UTF-8"?>');
PRINT('<ROWSET>');

/*Open the cusror and print the data */

FOR x IN my_cursor(100)
LOOP
print(x.result.getclobval());
END LOOP;

PRINT ('</ROWSET>');

/* After saving the whole ISO-88591 page to l_my_var convert it to UTF-8 */


l_utf8_var := CONVERT(l_my_var,'alt32utf8','we8iso8859p1')


HTP.P(l_utf8_var);

END;

please advise.

Tom:

OK, then

1) cant i write the above l_clob_utf8 to a file on the server (UTL_FILE) and have a URL that opens it directly. That way it will be browser directly access the file (no mod_plsql).

2) I do not understand why i cant create a new column "NCLOB" or "NVARCHAR2" or "NBLOB" that stores UTF8 data.
Then mod_plsql can retrieve the file without any character set conversion.

2.6 File Upload and Download
mod_plsql allows you to:

Upload and download files as raw byte streams without any character set conversions. The files are uploaded into the document table. A primary key is passed to the PL/SQL upload handler routine so that it can retrieve the appropriate table row.

Tom:

I udnerstand the client character set conversion and the mod_plsql or any client will convert the data to the language it talks..

But i do file downloads using mod_plsql and BLOB field frequently.. Is not this called file download? please explain what is a file download if this is not.

SELECT blob_content,
mime_type
INTO l_blob_content,
l_mime_type
FROM documents
WHERE name = file;

OWA_UTIL.mime_header(l_mime_type, FALSE);
HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content));
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file(l_blob_content);


My other option was to skip mod_plsql altogether. Would apache character set dicatate the character set then.
But based on what your saying the user browser is probably using win-1252, so it would convert the utf-8 again.

You seem to say it is almost impossible to do unless you change the character set in mod_plsql or web server.

<<yes, that is a file download. but what does it have to do with the price of cheese? You don't have a file. That isn't what you've been talking about doing.>>

Let me clarify something.

1. I said i will change the code so the whole web page is assigned to l_clob variable. Then I will save that to NCLOB column in the database. Does not this mean i have a the file in unicode format now??

2. After I do #1, user will *download* the file using Mod_plsql which will *NOT* do a character set conversion according to the docs.

so mod_plsql will deliver the file to the browser as unicode file.

Tom,

I am getting this error ORA-01482: Unsupported Character Set, please help.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as system
 
SQL> 
SQL> SELECT CONVERT(P.TITLE,'US7ASCII') CONVERTED_TITLE, P.TITLE
  2    FROM PRODUCTS                   p,
  3         DISCIPLINE_INFO           DI,
  4         PRODUCT_INTERNET_INDEXES   I,
  5         DISCIP_NUMBER2DISCIP_CODE DC
  6   WHERE P.PROD_ID                                 = 839118
  7     AND I.PROD_ID                                 = P.PROD_ID
  8     AND I.DISCIPLINE_CODE                         = DC.DISCIPLINE_CODE
  9     AND DC.DISCIPLINE_NUMBER                      = DI.DISCIPLINE_NUMBER
 10     AND I.CATALOG_CODE                            = 'INET'
 11  /
 
CONVERTED_TITLE             TITLE                          
Guide d'ecriture            Guide d'écriture

SQL> SELECT CONVERT(P.TITLE,'US7ASCII') CONVERTED_TITLE, P.TITLE
  2    FROM PRODUCTS                   p,
  3         DISCIPLINE_INFO                           DI
  4   WHERE P.PROD_ID                                 = 839118
  5     AND EXISTS ( SELECT 1
  6                    FROM PRODUCT_INTERNET_INDEXES   I,
  7                         DISCIP_NUMBER2DISCIP_CODE  DC
  8                   WHERE I.PROD_ID                                 = P.PROD_ID
  9                     AND I.DISCIPLINE_CODE                         = DC.DISCIPLINE_CODE
 10                     AND DC.DISCIPLINE_NUMBER                      = DI.DISCIPLINE_NUMBER
 11                     AND I.CATALOG_CODE                            = 'INET'
 12                     )
 13  /
 
SELECT CONVERT(P.TITLE,'US7ASCII') CONVERTED_TITLE, P.TITLE
  FROM PRODUCTS                   p,
       DISCIPLINE_INFO                           DI
 WHERE P.PROD_ID                                 = 839118
   AND EXISTS ( SELECT 1
                  FROM PRODUCT_INTERNET_INDEXES   I,
                       DISCIP_NUMBER2DISCIP_CODE  DC
                 WHERE I.PROD_ID                                 = P.PROD_ID
                   AND I.DISCIPLINE_CODE                         = DC.DISCIPLINE_CODE
                   AND DC.DISCIPLINE_NUMBER                      = DI.DISCIPLINE_NUMBER
                   AND I.CATALOG_CODE                            = 'INET'
                   )
 
ORA-01482: unsupported character set
 
SQL> 


please help.

thanks,
Prakash

Tom:

hope your enjoying the nice weather!

Can you tell me if my plan of action is correct ot not.

If i store a l_clob variable (xml file) into a column with NCLOB data type, the oracle stores the data in UTF8 format assuming the national character set is set to UTF8.

Then if i download that file via mod_plsql, the file is sent as UTF8 format.


BTW, i am not sure if the browser does character set conversion. from what I understand you tell the browser the character set of the file, and then it will display it based on that characer set. i think it is more for display purposes.

Tom:

I can not control the user browser setting.
My reqt is to deliver to the user an XML file in UTF8 format..and i am done.

Mod_plsql will not change the character set if it is a file download so we are only concerend with database.

I did some testing with NCLOB column and tried to change the oracle client sql*plus on windows to UTF8 but it did not seem to work. I do not know the default setting.

I beleive the below shows that data was delivered to client in UTF16 format. All i need to do is change the National Character set to UTF8. Do you still see issues.

SELECT * from NLS_SESSION_PARAMETERS

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE



c:> set NLS_LANG=AMERICAN_AMERICA.AL32UTF8


SQL> create table x (doc clob,ndoc nclob);

Table created.

SQL> desc x
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DOC                                                CLOB
NDOC                                               NCLOB

SQL> insert into x values (chr(128),chr(128));

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(dbms_lob.substr(doc, 4000, 1),1016) from x;

DUMP(DBMS_LOB.SUBSTR(DOC,4000,1),1016)
--------------------------------------------------------------------------------
Typ=1 Len=1 CharacterSet=WE8ISO8859P1: 80

1 row selected.


  1* select dump(dbms_lob.substr(ndoc, 4000, 1),1016) from x
SQL> /

DUMP(DBMS_LOB.SUBSTR(NDOC,4000,1),1016)
--------------------------------------------------------------------------------
Typ=1 Len=2 CharacterSet=AL16UTF16: ff,fd

1 row selected.

Wow... an 8.5 year thread. I wonder if that is a record.

I had some Character Set questions because we need to go from just western characters to greek. I got most of my questions answered with 1 Oracle Support ticket and yeah I read the docs first, but this type of chnage requires alot of work so I needed to be certain.

This is the type of question that can easily be answered through Oracle Support.

just a gripe... It is rather annoying that the default Oracle characterset does not support Greek. Alot of scientific databases (including mine) need to support it. When the same characterset supports Icelandic which very few people need.

Oracle does not write character sets. they only support it.

If ISO-88591 does not support greek, has nothing to do with oracle.

Your issue seems to be related to converting a whole database and there is more work invloved. I am only trying to generate one XML web page using UTF-8 format.

Don't you work in the Oracle Government division? you know how government contracting works. they constantly change their minds... so an "oh yeah we need Greek characters" is not something to be surprised about.

In 10g this one appears to be the default choice. WE8ISO8859P1

This one probably should include greek, since it includes many languages that are used far less often.

I am not putting in an enhancement request here. Just blowing smoke.

With oracle 11g, oacle documentation recommends to use unicode (utf8 or utf16) for new deployments as it can support any language on planet.

That way you do not worry about anything.

I think asktom site is UTF-8 and it does not store any foreign language.

In this askTom thread I demonstrated how I stored Greek and other languages in a 9iR2 database:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10250420313043#27317016562512
including examples with unicode Greek symbols, such as those found here:
http://www.unicode.org/charts/PDF/U0370.pdf

Hi Mike Friedman from Shanghai,

> Face it... just because you have no intention of using other character
> sets that does not mean that everyone else will cooperate with you.
>
> Look here: &#20013;&#25991;
>
> I just typed some Chinese. Doesn't matter what you planned - I didn't
> cooperate.

Incorrect. You didn't store chinese characters in the database. You
typed chinese into your browser and your browser turned it into ascii.
What gets stored in the database is &#20013;&#25991; which is exactly
16 ascii characters. But, then, just to really fool you, what happens
on the way out of the database is that those characters are seen by
the browser, and, vhalla, you see chinese characters. You type in
chinese characters and you get out chinese characters. Must have
persisted chinese characters, eh? But, you were duped by the browser
into thinking what was stored was chinese characters.

Store the following as a tstchinese.html file and open it up.

<html>
<body>
&#20013;&#25991;
</body>
</html>

What you see is chinese characters, but the actual data that is being
read is just 16 characters of plain ascii.

Correct, it's "just codepoints" in the database, as can be verified with Unicode's "Unihan" database.
http://www.unicode.org/charts/unihan.html
Although intended for Chinese characters, the Unihan lookup seems to contain the full gamut of Unicode characters. For example, if you look up the 03A6 codepoint, you'll see a Greek character.
http://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=03A6

http://www.joelonsoftware.com/articles/Unicode.html

Looks like we have ISO8859P1 characters stored in an ASCII db. The client NLS_LANG is UTF8.

Here's an example -

select name, dump(name, 1016) from users where id = 931402;
t�s�sstt� Typ=1 Len=9 CharacterSet=US7ASCII: 74,e9,73,e9,73,73,74,74,e9

Is there any way to write a sql query given the weird settings to fetch the string such that it displays in the correct format maybe using functions like convert etc? The actual string is 'tésésstté'.

Hi Tom,
We have a 10gR2 database that is:-
LANGUAGE AMERICAN
TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_NCAR_CHARACTERSET AL16UTF16

We want to now store chinese characters in the database and display chinese characters on our portal.

We have changed the content column from CLOB to NCLOB, by creating a new column as NCHAR, updating it with CLOB content and renaming the NCHAR column to the CLOB column after dropping the CLOB column.
The display still is showing 'traditional Chinese: ¿¿¿'.

We are really stumped here. Is there any way this can be done. We cannot change the database character set as the database has more than 20 schemas on it and this functionality is needed by only one.
Pleaseee can you advice if possible or any other way it can be done.
Regards,
Aru.

Hi Tom,

1) I read the 9i Globalization support guide, but couldn't understand the part "Oracle compares character strings in two steps for monolingual sorts. The first step
compares the major value of the entire string from a table of major values. Usually,letters with the same appearance have the same major value. The second step compares the minor value from a table of minor values."

where do it get the minor and major tables? where is it defined? I can understand A and a can be only sorted in ascending order in the basis of their minor values. Is it true?

2) Also in the same document I found that a Multilingual sorting has got 3 levels of sorting: i) sorting to evaluate base characters, ii) sorting among base characters (diacritic A and A ), iii) after second level of sorting it sorts with Case.

Is my understanding right?
Now why would we need a Multilingual sorting? Any enhanced effect?

Hi Tom,

I was trying to convert to UTF8 from US7ASCII, My database has CLOB columns table in SYS and SYSTEM schemas.It is not allowing me to change the character set, I got

ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists error message.

1) I heard that "alter database character set" statement is possible only when migration is done between single byte encoded character sets or multibyte encoded character sets. Probably I have violated the rule. Isn't it?
2) Now how can I migrate CLOB data?
Should I use export-drop-import steps to convert it from single byte to multibyte? But would SYS table export/import takes place in this way?

create table um2(x varchar2(1000));

insert into um2
values ('110830,Própria'); -- using sql developer

commit;

insert into um2
values ('110830,Própria'); -- using sqlplus session.

commit;

when querying in sqlplus or sqldeveloper its shown as

110830,Própria
110830,Pr�pria

while first one is what it should be second one is incorrect. both sqldeveloper and sqlplus are invoked from same machine.

dump of the values looks like

Typ=1 Len=15: 49,49,48,56,51,48,44,80,114,195,179,112,114,105,97
Typ=1 Len=16: 49,49,48,56,51,48,44,80,114,239,191,189,112,114,105,97

what could be the reason for this.

In addition to what Tom said, SQL Developer uses the JDBC Driver which does not operate exactly the way SQL*Plus does. I suggest you ask in the SQL Developer forum at https://forums.oracle.com/forums/main.jspa?categoryID=84 specifying your database character set, your SQL Developer version and NLS settings.

With SQL Developer V3 and a database in AL32UTF8, I cannot reproduce your problem.

Thank you Tom for your review comments.


Terminal I use is a MS windows XP Prof 2002 one where the language option just says English (US) no clarity as to which character set it uses, any idea as to how to get that.

One thing to note is when typing the insert command in sqlplus window it displays as it is, only upon retrieval from the DB it shows as junk.

Any help that you can offer?

thanks
Mani

The encoding for Windows console can be set using Regional Settings control panel (renamed to Region and Language in more recent versions). Details depend on the version of Windows you're using, but in general, there should be an option called "Language for non-Unicode programs" somewhere. Here you choose the encoding for all consoles.

However, I'd suggest the following: save the commands you want to run into a SQL script (in whatever encoding you choose).

Use the NLS_LANG environment variable to make the sqlplus client use the same encoding - see http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch11.htm#SQPUG021 for details. You can set it system-wide as mentioned in the documentation or just before running the script, eg. in a batch file.

Then run the script from sqlplus. If you get the encodings right, it should work.

HI Tom - here is a related and problem and hope your views will help to clear all doubts.

Database Version - Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Created a table :
09:16:47 SQL> desc ecs
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------

 A                                                  VARCHAR2(1000)
 B                                                  BLOB
 C                                                  CLOB

Settings in DB:
NLS_CHARACTERSET WE8MSWIN1252

inserted the following text into all the columns:
Forges et Aciéries de la Marine et d'Homécourt

1. when the data is viewed through Sql Developer , it is as was inserted.

2. when viewed through sql plus in the windows server box , it is as 

Forges et AciΘries de la Marine et d'HomΘcourt

What according to you could be the issue and fix for this.

I have a table which stores chinese data correctly. My Database supports Chinese data.

Now I use dbms_xmlquery to generate an XML on data mentioned above.

dbms_xmlquery.setencodingtag(l_ctx_schema);uses default encoding, which is ISO8559-1. And the XML geneated has corrupted chinese data.

Should I set UTF-8 as encoding. Would you know if ISO8559-1 supports Chinese.

I am not sure if asktom is the right forum for this query but I had no one else to look for. Please help.....

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.2.0

Malhotra, I have used extensively ISO-8559 (in special ISO 8559-15 for Portuguese) : by definition it is a Latin-alphabets compilation (same basis, some special characters in some derivated parts, such as the Euro sign, absent in 8859-1 and present in 8559-15)... To the best of my knowledge, your answer will be NO for 8859: ISO-8559 is a 8-bit (one byte) encoding, and as refered many times in this thread before, non-Latin languages needs multibyte encoding (at least two-bytes), due to the fact that much more than 255 characters exists in these languagens, even in the most simplified alphabets....
http://www.khngai.com/chinese/charmap/Chinese gives some history about the multiple character codes developed to encode Chinese characters, but Unicode is voted as the most flexible, capable to express both Simplified and Traditional symbols, so I would say go for it...
Just a point, when we talk about Unicode, you must choose between UTF-8 and UTF-16 : in my humble opinion, only if you really know as a fact that you application really will need to deal with uncommon/old symbols OR if all of your strings will be in Chinese, choose UTF-16 - if not my vote is for UTF-8, with UTF-8 you will not need to deal with any endianess, UTF-8 uses 1, 2, or 3 bytes depending on the character, so that an eventual ASCII character is represented still as 1 byte, while UTF-16 uses uniformly 16 bits to represent a character ....

Hope it helps,

J. Laurindo Chiappa

BACKGROUND

We are building a datawarehose on oracle

Version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Characterset :
"NLS_CHARACTERSET","AL32UTF8"
"NLS_NCHAR_CHARACTERSET","AL16UTF16"

We are required to store multilingaul data in our datawarehose. From a Storage perspective
since we have a UFT8 characterset we should be good.

Question I have is related to getting data from our data sources into the warehouse. Some
facts about our environment:

We will be recieveing data from 100 or so systems using files

These systems have varying DB/OS/Characterset environments
- some are Windopws others Unix
- Some are SQL Server, others Oracle
- Even the ones on oracle may have different character sets


The data from source system need to be provided using files that are sftp'ed to our
servers

OBJECTIVE

These source systems from which we are sourcing data have varying level of support for
multilingal data

Since out datawarehouse is UTF-8 which has all possible characters, we are trying to
find a solution where we can get data from various sources into the datawarehouse
along with the foreign characters coming through correctly (no garbling foreign
characters -upside down ?'s etc)

The data from source system need to be provided using files that are sftp'ed to our servers


QUESTION

How do i approach this problem.

Can systems using different DB/OS/Characterset environments - export data in a standard
unicode format that can be loaded in our datawarehouse What is this file format called ?
UNICODE ? UTF-8 ?

Or will the files exported from different source systems be encoded as per the DB/OS/
Characterset of their environment.If so how can i use them to load data in our datawarehouse
so that we do not garble foreign characters (upside down ?'s etc)


Thanks in advance!