Ferhat's Blog

There will be only one database

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;
/

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: