10 Replies Latest reply: Feb 21, 2006 8:43 PM by 306078 RSS

    How change nls session parameters

    314575
      Hi, i have a problem with the nls_session_parameters i don't now how chage this , when raptor start
        • 1. Re: How change nls session parameters
          Kris Rice-Oracle
          We currently don't have a login.sql like sqlplus does. The only workaround would be to open a worksheet and issue your changes there. The naviation shares that connection so it should take effect.

          -kris
          • 2. Re: How change nls session parameters
            264807
            Just tried that, setting NLS_DATE_FORMAT to 'mm/dd/yyyy hh24:mi:ss' in SQL*Worksheet.
            But when I look at Table Data, I'm getting column data of DATE type in 'yyyy-mm-dd' format.
            • 3. Re: How change nls session parameters
              300540
              Changing the nls-parameters in the worksheet seems to change the values in nls_session_parameters, but does not give the expected results when displaying dates (or language).
              An example:
              alter session set nls_date_format='dd.mm.yy hh24:mi:ss';
              select * from nls_session_parameters; result as expected
              select sysdate from dual; result 2006-01-03 !
              • 4. Re: How change nls session parameters
                451875
                This is a bit of a show stopper for those of us connecting to Oracle Applications databases. Because all of the translation tables use NLS_LANG to determine which information a session can see the inability to change it means that a lot of data cannot be easily queried using Raptor.

                Raptor appears to inherit my NLS settings from the operating system and because I'm not in North America the Oracle Applications english language values are not accessible to me.

                The workaround with the thick client is to always set my NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1. This isn't possible in SQL according to this FAQ;

                http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm

                After a little bit of digging around I discovered that what I can do is set the NLS_LANGUAGE value for my session. To view the English translations of Oracle Applications data I have to issue the following command for each connection;

                alter session set nls_language='AMERICAN';

                Which works but isn't particularly great. Any chance of being able to set this in some sort of preference?
                • 5. Re: How change nls session parameters
                  306078
                  As another Apps site outside the US, I was surprised that I hadn't hit this myself as we have had problems with this where the NLS_LANG wasn't set correctly in the Oracle registry.

                  However, I have set a Windows environment variable NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1 and it would appear that this is being picked up by Raptor. Checking NLS_SESSION_PARAMETERS, my NLS_LANGUAGE is AMERICAN and my NLS_TERRITORY is AMERICA. Selecting USERENV('LANG') from dual returns US and I can happily select on the various _VL views and see results.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                  • 6. Re: How change nls session parameters
                    306078
                    Unfortunately, I have found that the NLS_LANG environment variable is not affecting Raptor - my Regional settings on WinXP are actually set to US where it affects the USERENV('LANG') setting and set to Australia for other settings.
                    • 7. Re: How change nls session parameters
                      485857
                      Using a JDBC connection, the language is determined by the JVM user.language property
                      (see http://www.oracle.com/pls/db92/db92.to_URL?remark=drilldown&urlname=http:%2F%2Fdownload-west.oracle.com%2Fdocs%2Fcd%2FB10501_01%2Fjava.920%2Fa96654%2Fadvanc.htm%231024745)

                      Is there a way to pass a JVM property to Raptor at startup ?
                      I've tried sqldeveloper -Duser.language=en without success.
                      • 8. Re: How change nls session parameters
                        306078
                        In my opinion, it is not a matter of us as developers being able to pass parameters through to the JVM.

                        We are running an Oracle development tool, not just a JVM.

                        The development tool should be catering for this sort of thing - checking for the standard way that this information is set in all of the other Oracle development tools I have used (ie environment variables and Windows registry).

                        Until this sort of this is sorted out (to work without having to go through special hoops for SQL Developer), TOAD (or it's equivalents) will remain easier to use for the general user who doesn't understand why a view works in TOAD but not in SQL Developer (because it is based on a restriction to USERENV('LANG') and SQL Developer can't set this properly based on Oracle registry settings).
                        • 9. Re: How change nls session parameters
                          448576
                          "checking for the standard way that this information is set in all of the other Oracle development tools I have used (ie environment variables and Windows registry)."

                          I disagree. SQL Developer shouldn't have to rely on other Oracle software already installed on the client or varied interpretations of which Oracle_Home it needs to pick settings from.
                          I would like a 'login.sql' type thing where we could define ALTER SESSION or DBMS_SESSION.SET_NLS commands to run when a connection is made.
                          • 10. Re: How change nls session parameters
                            306078
                            I am not saying that SQL Developer should HAVE to rely on other Oracle software already installed - and it doesn't.

                            I am saying that IF other Oracle software is already installed (or appropriate environment variables have been set) that SQL Developer should not ignore this - but it does.

                            As I said previously, I don't think it should necessary to do extra "special" setup to get SQL Developer to pick up my existing NLS settings. If SQL*Plus can pick up my NLS settings without the use of a login.sql, then SQL Developer should be able to do the same. I know that, architecturally, SQL*Plus and SQL Developer are completely different - however, they both are functionally tools for developing with Oracle (and part of SQL Developer underlying design principal is to provide all of the functionality available in SQL*Plus).