8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

DBMS_SQLHASH Package

The DBMS_SQLHASH package allows us to generate a hash based on the result set returned by a query. We can compare hashes between two time points to check if changes have been made to the resulting data sets.

Setup
GETHASH Function
Considerations

Setup

Create a new test user with the CREATE SESSION and CREATE TABLE privileges. Grant it execute permissions on the DBMS_HASH package.

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant create session, create table to testuser1;

grant execute on dbms_sqlhash to testuser1;

The examples in this article require the following table owned by the test user.

conn testuser1/testuser1

--drop table emp purge;

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2)
);

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

GETHASH FUnction

The DBMS_SQLHASH package contains a single function called GETHASH, which returns the hash of the result set produced by the specified query.

SQL> desc dbms_sqlhash
FUNCTION GETHASH RETURNS RAW
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQLTEXT                        VARCHAR2                IN
DIGEST_TYPE                    BINARY_INTEGER          IN
CHUNK_SIZE                     NUMBER                  IN

SQL>

The DIGEST_TYPE parameter has the following allowable binary integer values.

1 : HASH_MD4
2 : HASH_MD5
3 : HASH_SH1
4 : HASH_SH256
5 : HASH_SH384
6 : HASH_SH512

The CHUNK_SIZE parameter defaults to 128MB.

We run two queries to return the hash of the resulting data sets.

select dbms_sqlhash.gethash(sqltext     => 'select * from emp',
                            digest_type => 4) as hash
from   dual;

HASH
--------------------------------------------------------------------------------
18FF3D9F4EFC12345D8925E63923BAE3F00D60F2C3BD600B20D9EB3EE5C6D52D

SQL>


select dbms_sqlhash.gethash(sqltext     => 'select sal from emp',
                            digest_type => 4) as hash
from   dual;

HASH
--------------------------------------------------------------------------------
D82E498C51E2C4AD3D4341D6A7CD0117ACA50EFCFAE5165056D011C7EC48A2B5

SQL>

We add a row into the table to alter the hash value.

insert into emp (empno, ename, sal, deptno) values (9999, 'HALL', 1000, 10);

We check the new hash values.

select dbms_sqlhash.gethash(sqltext     => 'select * from emp',
                            digest_type => 4) as hash
from   dual;

HASH
--------------------------------------------------------------------------------
8E4459B76657FD0221982AB0AA6B66CAA1C328C384614C4ADD7DFCDA4A493026

SQL>


select dbms_sqlhash.gethash(sqltext     => 'select sal from emp',
                            digest_type => 4) as hash
from   dual;

HASH
--------------------------------------------------------------------------------
15A3688E5FFE29F2472D48B3A012662FFAE8BAC2999E714C43A4E1E979E760CF

SQL>

Rollback the data change.

rollback;

Considerations

Some things to consider when using the DBMS_SQLHASH package.

You must have execute permissions on the package.
It was first documented in Oracle 11g, but has been present since at least Oracle 10gR2. It is present in recent versions, but seems to have been excluded from the documentation in some versions.
It can be used to check for changes in anything that can be queried from the database, including cpde queried from the USER_SOURCE view.
There are some precautions you need to take to make sure the results of the GETHASH function are deterministic, as described here.

For more information see:

Verifying Data Integrity with DBMS_SQLHASH

Hope this helps. Regards Tim...

Back to the Top.