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

Home » Articles » Misc » Here

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

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.