Ferhat's Blog

There will be only one database

To show NLS parameters for session-database-instance together by Pivot and Listagg on 11gr2

Posted by fsengonul on July 21, 2010

I’ve always confused and needed to check for the nls parameters. I thing it’s a good chance to play around with the pivot function on 11gR2 in order to show all of them together.

The pivot function seems to be used only with aggregate functions. How can the text values be aggregated?  At this point a new LISTAGG function ,which also comes with 11gr2, is really useful.

After that I have the problem to show the results on terminator. The headings last forever when I do not use aliases on the in clause of pivot. And lastly do not forget to use double quotes for SESSION, just because it is a reserved word.
script:

col "SESSION" format a30
col DATABASE  format a30
col INSTANCE  format a30
select * from
(select 'SESSION' SCOPE,nsp.* from nls_session_parameters nsp
union
select 'DATABASE' SCOPE,ndp.* from nls_database_parameters ndp
union
select 'INSTANCE' SCOPE,nip.* from nls_instance_parameters nip
) a
pivot  (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));
.
And the output:
22:22:53 SQL> @nls
PARAMETER                      SESSION                        DATABASE                       INSTANCE
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_SORT                       BINARY                         BINARY
NLS_CALENDAR                   GREGORIAN                      GREGORIAN
NLS_CURRENCY                   TL                             $
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_TERRITORY                  TURKEY                         AMERICA                        AMERICA
NLS_DATE_FORMAT                YYYYMMDD HH24:MI:SS            DD-MON-RR
NLS_TIME_FORMAT                HH24:MI:SSXFF                  HH.MI.SSXFF AM
NLS_CHARACTERSET                                              WE8ISO8859P9
NLS_ISO_CURRENCY               TURKEY                         AMERICA
NLS_DATE_LANGUAGE              AMERICAN                       AMERICAN
NLS_DUAL_CURRENCY              YTL                            $
NLS_RDBMS_VERSION                                             11.2.0.1.0
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR              HH.MI.SSXFF AM TZR
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_LENGTH_SEMANTICS           BYTE                           BYTE                           BYTE
NLS_TIMESTAMP_FORMAT           DD/MM/RRRR HH24:MI:SSXFF       DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET                                        AL16UTF16
NLS_NUMERIC_CHARACTERS         ,.                             .,
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RRRR HH24:MI:SSXFF TZR   DD-MON-RR HH.MI.SSXFF AM TZR
20 rows selected.
Elapsed: 00:00:00.04

Democracy is the recurrent suspicion that more than half of the people are right more than half of the time.            — E. B. White

4 Responses to “To show NLS parameters for session-database-instance together by Pivot and Listagg on 11gr2”

  1. You could following query to get same information. And it will work all Oracle versions not just 11gR2🙂

    select ndp.parameter
    , max(nsp.value) Sesssion
    , max(nip.value) Instance
    , max(ndp.value) Database
    FROM nls_session_parameters nsp
    , nls_instance_parameters nip
    , nls_database_parameters ndp
    WHERE ndp.parameter = nsp.parameter (+)
    AND ndp.parameter = nip.parameter (+)group by ndp.parameter
    ;

  2. Or following query, if you like ANSI

    select ndp.parameter
    , max(nsp.value) Sesssion
    , max(nip.value) Instance
    , max(ndp.value) Database
    FROM nls_session_parameters nsp
    FULL OUTER JOIN nls_instance_parameters nip ON nip.parameter = nsp.parameter
    FULL OUTER JOIN nls_database_parameters ndp ON ndp.parameter = nsp.parameter
    group by ndp.parameter
    ;

  3. fsengonul said

    Hi Vishal,
    You’re definitely right. Yours is a better solution.But I still wonder why you needed the max function. On 11g even the following query has the same result:

    select ndp.parameter
    , nsp.value Sesssion
    , nip.value Instance
    , ndp.value Database
    FROM nls_session_parameters nsp
    FULL OUTER JOIN nls_instance_parameters nip ON nip.parameter = nsp.parameter
    FULL OUTER JOIN nls_database_parameters ndp ON ndp.parameter = nsp.parameter
    order by 1 asc

    Thanks a lot.

  4. Hi Ferhat,
    You are right, there is no need of a max in the query. I was originally writing query differently using a group by and forgot to remove the max in the SELECT list.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: