DBA Hub

📋Steps in this guide1/3

DBMS_SQLHASH Package

The DBMS_SQLHASH package allows us to generate a hash based on the result set returned by a query.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Setup

Create a new test user with the and privileges. Grant it execute permissions on the package. The examples in this article require the following table owned by the test user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
--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;

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

GETHASH FUnction

The package contains a single function called , which returns the hash of the result set produced by the specified query. The parameter has the following allowable binary integer values. The parameter defaults to 128MB. We run two queries to return the hash of the resulting data sets. We add a row into the table to alter the hash value. We check the new hash values. Rollback the data change.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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>

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

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>

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

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

Considerations

Some things to consider when using the package. For more information see: - Verifying Data Integrity with DBMS_SQLHASH Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!