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

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

About these ads