I wrote a PL/SQL code for showing all NLS parameters executed throught Oracle iAS. The value of some parameters in NLS_SESSION_PARAMETER are different from using SQL query data by SQL*Plus (from client that not set any NLS parameters on) such as NLS_DATE_FORMAT = 'DD MON RRRR' from the PL/SQL code, NLS_DATE_FORMATE = 'DD-MON-RR' from SQL*Plus.
I would like to know where the value of each parameter in NLS_SESSION_PARAMETER table come from is and how I can force it to my format (from the server, not in the code -- DBMS.SET_NLS)?
Thanks
01-16-2003, 03:46 AM
SANJAY_G
Code:
SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-RR
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-YYYY
The values shown in NLS_SESSION_PARAMETERS are session specific. If you want to change at SYSTEM level either you can modify in init.ora or you can user 'ALTER SYSTEM SET NLS_...'
01-16-2003, 09:07 AM
peach_partner
Thank you for your reply.
I know where I can set NLS parameter at SESSION and SYSTEM level but I'm confused why NLS_DATE_FORMAT='DD MON RRRR' was returned by Oracle iAS that I never set the specific NLS paramter on the server. --by default NLS_DATE_FORMAT is 'DD-MON-RR' right?
How can I set the specific NLS parameter on Oracle iAS? :D
Rgds,
PS. Sorry to make you mistake.
01-17-2003, 02:46 AM
peach_partner
1. Query by logging in directly to the host:
SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
************************************************
2. Set the NLS_DATE_FORMAT in init.ora:
NLS_DATE_FORMAT=DD/MM/YY
************************************************
3. Shutdown and Startup DB (Oracle 8.1.7).
4. Query by logging in directly to the host (again):
SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD/MM/YY
************************************************
5. Run PL/SQL code that containing the SQL statement as the 1st and 4th via a web browser (served by Oracle iAS):
