This content has been marked as final. Show 10 replies
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.
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.
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).
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 !
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;
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?
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.
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.
Using a JDBC connection, the language is determined by the JVM user.language property
Is there a way to pass a JVM property to Raptor at startup ?
I've tried sqldeveloper -Duser.language=en without success.
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).
"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.
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).