CODEWITHSUNDEEP
sqloracle-databasespecial-characters
Dave Keane
Dave Keane

Reputation: 737

Oracle database special characters saving as ascii

When I try to save Özyeğin University to an oracle database, it saves as Özyeğin University => ğ = ğ

This happens in my clients database and not in mine, checked and both are using same charactersets => NLS_CHARACTERSET = AL32UTF8

Any Ideas

Upvotes: 0

Views: 896

Answers (1)

user5683823
user5683823

Reputation:

Not sure how this looks on your screen; on mine (with default U.S. settings) the character is exactly the same. With that said: often the problem is not the database character set (on the server), but the front-end character set (client) - either at input time or at retrieval time (or both). To see the exact characters stored in the database, use DUMP like so:

select 'Özyeğin' as str, dump('Özyeğin') as codes from dual;

That will tell you for sure what is stored in the DB, so you can at least narrow down the problem - is it with what is stored, or just with what is displayed when you select from the DB? (Actually, don't use DUMP() EXACTLY as I showed; rather, select dump(col_name) from table_name where id = ... where the id is some id value for the row containing the university name; you want to see what is IN A TABLE in the database, not something made up on the fly like in my example. col_name is the name of the column containing the name of the university.)

To illustrate the problem, here is a short session in SQL*Plus. Note how with my standard character set (see comments below - MY default is cp 1252 because I MADE IT TO BE) (code page 1252, Microsoft name for the Western European character set) the ğ becomes g (even in the database - it is translated by the front-end before it is even sent to the database), but if I change the character set to 1254 (for Turkish) the letter is preserved, and DUMP shows the correct code for ğ in code page 1254.

SQL> select 'Özyeğin' as str, dump('Özyeğin') as codes from dual;

STR     CODES
------- -----------------------------------------
Özyegin Typ=96 Len=7: 214,122,121,101,103,105,110


SQL> host chcp 1254
Active code page: 1254

SQL> select 'Özyeğin' as str, dump('Özyeğin') as codes from dual;

STR     CODES
------- -----------------------------------------
Özyeğin Typ=96 Len=7: 214,122,121,101,240,105,110

So: if I used the Western European character set to input data into the database, then ğ was changed to g (it doesn't matter what the DATABASE character set is, because it never received the ğ, it received g). However, if everything was fine going in, you may still see g in the output if the front-end used to look at results from the database does not use the Turkish character set. (However, in the latter case you may not get "g" - you will get whatever code 240 is in the client character set; in cp 1252 it is ð, not g...)

Upvotes: 4

Related Questions