Nls_session_parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Nls_session_parameter

  1. #1
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29

    Question Nls_session_parameter

    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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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_...'
    Last edited by SANJAY_G; 01-16-2003 at 03:51 AM.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    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?

    Rgds,


    PS. Sorry to make you mistake.

  4. #4
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    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):

    -----------------
    NLS Session is
    -----------------
    Parameters Values
    ------------------------ ------------------
    NLS_DATE_FORMAT DD MON RRRR

    ************************************************


    # Why is NLS_DATE_FORMAT = 'DD MON RRRR'?
    It should be NLS_DATE_FORMAT = 'DD/MM/YY' or not?

    # If Oracle iAS can specify the NLS parameter, how can I do?
    (I would not like to specify its from my code as using DBMS_SESSION.SET_NLS or other)

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width