6 Replies Latest reply: Apr 25, 2013 2:28 PM by Sergiusz Wolicki-Oracle RSS

    Logon Trigger to alter session NLS_CHARACTERSET

    1005031
      Hi,
      I'd like to call a PL/SQL stored procedure from an IBM MessageBroker ESQL code.
      My problem is that character encoding does not match between the two systems. NLS_CHARACTERSET = EE8ISO8859P2 ,NLS_NCHAR_CHARACTERSET = UTF8 but message broker character set = AL32UTF8. The accents do not appear well in the database.
      My question:
      Can I solve this problem with a logon trigger which alters session that the NLS_CHARACTERSET be AL32UTF8?

      Help me, please.
        • 1. Re: Logon Trigger to alter session NLS_CHARACTERSET
          DK2010
          Hi,

          Welcome to the forum,

          yes You can set using logon trigger
          ALTER SESSION SET NLS_CHARACTERSET = AL32UTF8
          • 2. Re: Logon Trigger to alter session NLS_CHARACTERSET
            Sergiusz Wolicki-Oracle
            No, you cannot!!! NLS_CHARACTERSET is not a session parameter in Oracle.


            Assuming IBM MessageBroker connects through OCI, setting the NLS_LANG variable in its environment to .EE8ISO8859P2 could help. You should also review MessageBroker's documentation, especially any configuration parameters for Oracle, to see if the character set is not configurable there. There is nothing to be done on the Oracle side.

            By the way, can you name any particular characters that do not show up correctly and tell me what you see in place of them?



            -- Sergiusz
            • 3. Re: Logon Trigger to alter session NLS_CHARACTERSET
              1005031
              Thank you for your answer.
              Unfortunately when I try this instructions it gives me an 'ORA-00922: missing or invalid' error.
              Now I read that a database's character set can not be changed. :(
              • 4. Re: Logon Trigger to alter session NLS_CHARACTERSET
                DK2010
                Hi,

                i misread the thread, apologies for that NLS_CHRACTERSET command above , it only can be changed with CSSCAN utilty if it required.
                • 5. Re: Logon Trigger to alter session NLS_CHARACTERSET
                  1005031
                  S. Wolicki, Oracle wrote:
                  No, you cannot!!! NLS_CHARACTERSET is not a session parameter in Oracle.


                  Assuming IBM MessageBroker connects through OCI, setting the NLS_LANG variable in its environment to .EE8ISO8859P2 could help. You should also review MessageBroker's documentation, especially any configuration parameters for Oracle, to see if the character set is not configurable there. There is nothing to be done on the Oracle side.

                  By the way, can you name any particular characters that do not show up correctly and tell me what you see in place of them?



                  -- Sergiusz
                  Hi Sergiusz,

                  I replace back and forth the accented characters, but for large queries, it takes a very long time:
                  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c,'ő','&#x0151'),
                  'Ő','&#x0150'),'ű','&#x0171'),'Ű','&#x0170'),'é','&#x00E9'),'É','&#x00C9'),'á','á'),'Á','&#x00C1'),
                  'ú','&#x00FA'),'Ú','&#x00DA'),'ö','&#x00F6'),'Ö','&#x00D6'),'ü','&#x00FC'),
                  'Ü','&#x00DC'),'ó','&#x00F3'),'Ó','&#x00C3'),'í','&#x00ED'),'Í','&#x00CD');
                  (The semicolons let down for display.)
                  I thought that I would change the nls_lang on the client side in a Java node but it is not sure that will work.

                  Thank you for your answer.

                  Sada

                  Edited by: 1002028 on 2013.04.24. 13:48

                  Edited by: 1002028 on 2013.04.24. 13:49
                  • 6. Re: Logon Trigger to alter session NLS_CHARACTERSET
                    Sergiusz Wolicki-Oracle
                    the nls_lang on the client side in a Java node
                    I do not know MessageBroker. Is it a Java-based application connecting through JDBC? If so, then NLS_LANG will make no difference. However, the characters should be retrieved correctly.

                    By the way, I made a mistake. The NLS_LANG value to try is .AL32UTF8, not .EE8ISO8859P2.

                    On the other hand, you say it works when you replace characters with HTML escapes. I wonder if your problem is not with displaying of the data retrieved from the MessageBroker (as opposed to the data encoding itself).


                    -- Sergiusz