This discussion is archived
6 Replies Latest reply: Apr 25, 2013 12:28 PM by Sergiusz Wolicki (Oracle) RSS

Logon Trigger to alter session NLS_CHARACTERSET

1005031 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points