Special character (€) not getting inserted into db

Karki

HI All,

I have  an insert SQL file in which I have some german characters and also character €. After insertion character is getting converted into some box or some other characters.

Any method by which I can load these characters.

KayK

Hi Karki,

which tool do you use ? Which platform ?

Find an appropriate nls_lang-parameter according to the characterset of your databse and it will work for sqlplus or sqlldr.

regards

Kay

BEDE

That depends on the database character set. If the database character set does not include that character, then there is no way to do that. The database character set is determined on database creation.

If the database character set includes that character, then there must be a problem with the character set on the client that connects to the database and how characters are converted from oen to the other.

mathguy

It is possible that what you said is true.

It is also possible (and in fact much more likely) that the character is inserted perfectly fine, and what you are seeing (some strange symbol instead of the euro symbol) is not caused by what was stored in the database, but instead it is caused by your graphical interface which does not display it correctly.

For example, copy and paste the following statement, and then copy and paste the output back here:

select '€' as symbol, dump('€') as dump_symbol from dual;

This will help us figure out if this is a "storing in the database" problem vs. a "display only" problem.

John Thorton

Karki wrote:HI All,I have an insert SQL file in which I have some german characters and also character €. After insertion character is getting converted into some box or some other characters.Any method by which I can load these characters.

Please post proof that shows you have a data storage problem & not a data presentation problem.

Data may be stored in table OK, & just not being presented correctly.

SELECT GERMAN_COLUMN, ASCiiSTR(GERMAN_COLUMN) FROM TEST_TABLE WHERE ID = 1;

Paulzip

There's a big different between client display of characters versus the DB actually storing them correctly.  Your problem could be client display, storing, or both, but without knowing your NLS character set it's hard to say.

Assuming this is a varchar2 / char / clob column you are reading back and not a NChar / NVarchar2 then tell us your NLS_CHARACTERSET and as Mathguy says, use dump(..) on one of the examples of characters not showing correctly.

Karki

Adding more information:

select '€' as symbol, dump('€') as dump_symbol from dual;

€ Typ=96 Len=3: 226,130,172

German language and such code are there in database when we are trying to load it with some other application like Java. However when executing the plsql insert script in sql developer its showing boxes in data.

Also if there is viewing issue not the data issue, what setting i change in my sql developer to fix it.  I could see the character when inserted by other medium but not when I am inserting in plsql insert script.

BrunoVroman

Hello Karki,

the situation might be more complex than it appears at first sight.

The database has a character set, this is the set of characters that can be stored in the database. We'll assume that it can store the characters like € or ß (eszett).

The client inserting the data has a character set. It can be different of the DB character set (for example we might have a client using a single byte charset WE8MSWIN1252 inserting data in a database AL32UTF8)

The client selecting the data has a character set. Once again, it can be equal or not to the DB charset, and it can also be equal or not to the charset used to load the data.

Still, everything is fine... The server side takes care of silently translating the characters between the clients and the database (as the same character can have different representations, like for example "é" that is coded on one byte in MSWIN1252 but on several bytes in AL32UTF8).  Important: if the client states that it uses a charset "X", the server trusts it (even if thsi is wrong); also: if the client states that it uses the same charset as the database, the server can "skip" the translation phase and accept the bytes as they come (or, in the other direction, sending them as they are stored)

Unfortunately there are possible issues...

For example the client might state "I am using charset X" but try to load a file using charset "Y" (this is very common, for example MSWIN1252 client loading an UTF-8 file) and then the data in the database is not what it should be (either the DB charset is "X" and then the "Y" bytes are stored as is -which is not correct- or the DB charset is "Z" and then "translateXcodestoZ" is applied on "Y" -which isn't correct either.  If this happens we have loaded wrong data.

Or the problem can also be at SELECT time: if the client states "I am using charset X" but in fact is using charset "Y", then if DB is using charset "X" the server will send bytes "as is" to the client that will have issues to present them (invalid codes), or if the DB is using "Z", it will apply "translateZcodestoX" and the client will also have issues.

And of course both errors can combine...

And a very annoying scenario that confuses the users is when a client wrongly states "I am using the same charset as you" both at insert and at select time: for this client, everything seems fine (and for example I have seen an AL32UTF8 client sending multibyte Chinese characters to an US7ASCII database and retrienving them on screen where they looked perfect... Although inside the database there are lots of invalid codes! So another client with correct settings has issues, but the "guilty clients" don't see it.

To make things worse you can also have issues on the client, for example with the "code page" and the font used in a Windows box (see command chcp) that sometimes cannot show the characters...

So it is not easy to find what is wrong (insert time? select time? both?...) but there are ways to find (check client settings, check in database if codes are valid against the declared charset)

Best regards,

Bruno Vroman.

mathguy

In most cases the operating system is irrelevant (things work the same on Windows and on Linux).

The displaying of characters on screen, through whatever user interface, is a major exception to this. The answer to your question, assuming indeed that it is a display issue, not a "what is stored in the database issue" (as it almost certainly is), depends on whether you are on Windows or on Linux. So - please tell us what OS your client software (the user interface) is running on.

odie_63

Karki wrote:select '€' as symbol, dump('€') as dump_symbol from dual;€ Typ=96 Len=3: 226,130,172

That's the byte sequence representing the EURO sign in UTF-8.

So your database character set must be AL32UTF8, right?

Could you run the same query on the column where you see the issue?

Do you see the same byte sequence for the EURO sign?

If yes, you have a display issue.

If no : you have a conversion issue while loading data in the db :

You have to determine the encoding of your input script file. Is is UTF-8 too?

Then either :

- set SQL Developer to that encoding so that it can read your file correctly.

Tools > Preferences > Environment > Encoding > UTF-8

- or, convert the file to match the encoding set in SQL Developer

but I'll rather go with the first option.

Karki

I found the issue.

The insert script were created in notepad++ and it has Encoding set as "Encode to UTF-8 Without BOM".

I copied the content and saved it in Notepad and used encoding ANSI.

Then when I loaded the same file in sql developer, it perfectly loaded all special characters.