This content has been marked as final. Show 11 replies
Hi Here is my setting of DB:
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
Maybe you can find some differences.
It is only a "above" information from apex. I can't find any attribute where i can set this.
Try to investigate this post:
after apex application export and import wrong umlaute
Which version of Oracle DB do you have ?
I had some similar problem with oracle XE english version. I installed a XE multilanguage and everythig worked fine...
But i am not sure if it was the same problem there :(
A few of questions for you:
1) When you see the ¿, is that in Apex, or is that in Toad or command-line SQL*Plus? It sounded like it might be in the apex form, but I want to be sure what the client was. This can be a couple of things. a) it could be that the data is corrupt, or b) it could be that the data is fine in the database and you're just not rendering the proper glyph based on what output window you're using and it's support for that code point. Seeing Wingdings is not uncommon...
2) Since what the db is storing is actually a code point, you can see what code point is actually being stored. You'll know whether your storage is fine or not this way. Use ASCIISTR in a select from your table -- from the SQL Commands window in Apex for now. Example:
The easiest approach I think would be to create a single table and insert your character into it like you are now, select from it like you are now and confirm you're getting the ¿ returned (simple test case this way), then get the ASCIISTR output for that.
SELECT ASCIISTR('s') FROM dual;
Check the return value here: http://www.unicode.org/charts/. If it returns your character, then storage is not the problem -- it's your client. It doesn't know what to do with that character. If the correct value is not returned, then the input is not making it to the db correctly.
3) IF it isn't being stored correctly, the next thing to check is whether it CAN store it properly. You can use UNISTR to bypass your input method and just insert the value directly to the table. Example:
Now, check the reverse:
CREATE TABLE Z_TEST ( MY_CHAR VARCHAR2(50)); INSERT INTO Z_TEST VALUES (UNISTR('\0161'));
If you get the correct code back, then your db is taking it in fine and storing it alright. See if you can view it through whatever client method you're using. If that all works, then you're method of input appears to be faulty. Start there.
SELECT ASCIISTR(my_char ) FROM z_test;
4) IF it is being stored correctly, then it's just your client rendering the proper glyph that's the problem. Check your client, use a browser to validate your data instead whatever other method might be used. Report here what the findings are and we can check different client settings.
Hope it gives a bit of a roadmap...
I see the reverted question mark in ApEx and in SQL Developer.
So I have now done the following: Created the table and done the insert and select like you metionend.
It seems that the problem already occurs while storing in database, because selecting the inserted value with ASCIISTR returns "\00BF" which is the unicode for the reverted question mark.
Selecting UNICODE('\0161') returns the correct value, so my client can show these characters.
What can I do now????
If you're running AL32UTF8 (sorry - was taking that part for granted) you should be good on the DB side. Definitely double-check with your DBA.
Unless a DBA is really thinking about it during the install, they'll either get the US7ASCII characterset or Western European (the installer picks up the default from the OS) by default and stay with it. It isn't as obvious as I'd like that they should change it, and Oracle's own recommendation that UTF8 be the characterset is not built in as the default which drives me nuts.
For your DBA, if you're running US7ASCII -- it's a subset of UTF8, so conversion's a piece of cake. By subset I mean that every character that's in the US7ASCII..it's code point is the same in UTF-8. US7ASCII is what you'll find is the default on most Unix/Linux systems (most -- not all).
If you're running WE8MSWIN1252 (Western European 8-bit MS Windows Code Page 1252...now you see why they abbreviate), that can be a pain to migrate to AL32UTF8 depending on what data you have. It isn't a perfect subset of UTF-8. There's a scan utility that can help with the migration if you need it. Most Windows installations have this as the default. If you're on Windows, I'd suspect this is the case.
The typical argument against Unicode that goes back to the old NLS days is that it takes a ton more space to run with a Unicode characterset than a non-unicode. If that's argued, tell your DBA to post here or dig into some of the recent docs. Now, Oracle's recommendation is to do Unicode from the start, even if you don't plan to go international. It doesn't hurt. It stores characters as 1, 2, or 3 bytes per character, so the letter A is still only stored as a single byte. The NLS characterset is different - it's either 2 or 4 bytes per character, so that would take more space...BUT you don't want to use that. You're using the db characterset that's UTF-8, so moving to that would be best long-term regardless of this issue.
Start by verifying actual db character set:
Use dump() function to find actual stored character codes (also shows char set), e.g.
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';
select column, dump(column, 1016) from table where suitable_condition ... ;
Please also post version, at least four positions.
However, Rons test likely have already confirmed that your database is not AL32UTF8.
Edited by: orafad on Apr 17, 2012 9:16 PM
Edited by: orafad on Apr 17, 2012 9:17 PM
thanks for your explanation, I will check this with my DBA.
thanks for the sql statement. I think together with the explanation of Ron, I have now found my problem. The query returns:
I will talk to my DBA for change to AL32UTF8 and let you know, when I'm still having problems.