Ferhat's Blog

There will be only one database

Posts Tagged ‘listagg pivot nls’

ORA_HASH and NLS CHARACTERSET to compare code

Posted by fsengonul on November 15, 2015

Task :  Find out whether a system trigger or procedure is same in 100+ databases.
First idea : Let’s use ORA_HASH for the source code.
Action 1: Use LISTAGG to create a single line of data from dba_source. Get rid of the spaces.

SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP';

Action 2: Then use it input to ORA_HASH .


--DB Number 1;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
1418146976

--DB Number 2;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
4280078353

And here comes the problem; even though the code is the same , the hash values are grouped into two separate sets.

A little investigation shows that the NLS_CHARACTERSET parameter is different for the two sets of dbs  as AL32UTF8 and WE8ISO8859P9. For the Turkish characters the first characterset uses 2 bytes and the latter uses 1 byte only; and this changes the ora_hash result !

So let’s use CONVERT to  reach a final point:


--DB Number 1:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;

HASH         NLS
---------- ----------------------------------------------------------------
1418146976 AL32UTF8

--DB Number 2:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;
HASH        NLS
---------- ----------------------------------------------------------------
1418146976 WE8ISO8859P9

The SP used to test:

create or replace  procedure helloworld as
begin
    DBMS_OUTPUT.PUT_LINE(q'[Hell'o World ç]');
end;
/

Posted in oracle | Tagged: , , | Leave a Comment »

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

Posted in oracle | Tagged: | 4 Comments »