11 Replies Latest reply: Apr 18, 2012 1:45 AM by chrissy2 RSS

    problem with submitting slovak diacritics to database

      Hi all,

      I have an application where users enter values containing slovak diacritics (i.e.: caron: Š).
      After submit to database this letters are shown as ¿.
      My apex language (PlsqlNLSLanguage) is set to "AMERICAN_AMERICA.AL32UTF8". I'm having no problems with french diacritics like accents or cedilles.
      Can anybody give me some hint, what to do???

        • 1. Re: problem with submitting slovak diacritics to database
          Jozef Demovic SVK
          I developed a few applications in this language :)
          Which charset have you set in the database ? (AL32UTF8)


          J :D
          • 2. Re: problem with submitting slovak diacritics to database
            Hi jozef,

            like already mentioned in my first post I haveAMERICAN_AMERICA.AL32UTF8 ...

            • 3. Re: problem with submitting slovak diacritics to database
              Jozef Demovic SVK
              Hi Here is my setting of DB:

              PARAMETER VALUE
              NLS_CURRENCY $
              NLS_SORT BINARY
              NLS_DUAL_CURRENCY $
              NLS_COMP BINARY

              And APEX:
              HTTP_ACCEPT_LANGUAGE sk,en-us;q=0.7,en;q=0.3
              REQUEST_CHARSET AL32UTF8

              Maybe you can find some differences.

              J :D
              • 4. Re: problem with submitting slovak diacritics to database
                Hi Jozef,

                where do you set these stuff in ApEx ...
                I think for database these are nearly the same values, because it seems you are also using AMERICAN_AMERICA.AL32UTF8.

                • 5. Re: problem with submitting slovak diacritics to database
                  Jozef Demovic SVK

                  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 :(

                  J :D
                  • 6. Re: problem with submitting slovak diacritics to database
                    Hi Chrissy,

                    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:
                      SELECT ASCIISTR('s')
                        FROM dual;
                    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.

                    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:
                      CREATE TABLE Z_TEST (
                         MY_CHAR VARCHAR2(50));
                      INSERT INTO Z_TEST 
                       VALUES (UNISTR('\0161'));
                    Now, check the reverse:
                      SELECT ASCIISTR(my_char )
                        FROM z_test;
                    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.

                    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...

                    • 7. Re: problem with submitting slovak diacritics to database
                      Hi Ron,

                      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????

                      • 8. Re: problem with submitting slovak diacritics to database
                        Hi jozef,

                        we are using an Oracle 10g database and I think it is only the english version ... but I must check this with our Database Administrator.

                        • 9. Re: problem with submitting slovak diacritics to database
                          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.


                          • 10. Re: problem with submitting slovak diacritics to database
                            Start by verifying actual db character set:
                            SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';
                            Use dump() function to find actual stored character codes (also shows char set), e.g.

                            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
                            • 11. Re: problem with submitting slovak diacritics to database

                              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:

                              PARAMETER VALUE
                              NLS_NCHAR_CHARACTERSET AL16UTF16
                              NLS_CHARACTERSET WE8ISO8859P1

                              I will talk to my DBA for change to AL32UTF8 and let you know, when I'm still having problems.