Oracle hash data across rows

Question

Suppose I have an oracle table with two columns: type varchar2 and data varchar2. I want to know if there is a somewhat efficient way in plsql or straight oracle sql to compute a hash over all the data column (ideally sha1, but md5 or custom oracle hash functions are acceptable). For example, a mysql implementation of the solution might look like:

mysql dialect query:
select type, sha1(group_concat(data order by data separator '')) data_hash from my_table group by type

example output:    
+------+------------------------------------------+
| type | data_hash                                |
+------+------------------------------------------+
| a    | dbe343bfc23545c72f11fc7d2433df3263a71d0d |
| b    | b2baee034a7ed3aa1fa1bd441e141909f1b2f57c |
+------+------------------------------------------+

I'd prefer a straight query over a cursor iteration, and sha1 over other hashes.

Eh? rows can't have different types - I think you've confused rows and columns. — symcbean, Jul 01 '11 at 11:41

score 4 · Accepted Answer · answered Jul 01 '11 at 05:24

4

You could try

SELECT type, SUM(ORA_HASH(data)) FROM my_table GROUP BY type

Oracle does have a COLLECT but that doesn't work with ORA_HASH. In 11g you can do

select deptno, ora_hash(listagg(ename) within group (order by ename))
from emp
group by deptno

answered Jul 01 '11 at 05:24

Gary Myers

33,803
3
47
71

If you want sha1, look at dbms_crypto (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm). – Martin Schapendonk Jul 01 '11 at 08:27
Why does SUM do in the context of SUM(ORA_HASH(data))? I thought sum was for base10 arithmetic.... not combining hashes? – Learner Feb 22 '19 at 00:18
ORA_HASH returns a number not a hex-style VARCHAR2. If you are not concerned about the ordering of hashes then a SUM is a simple way to combine them. – Gary Myers Feb 22 '19 at 02:31

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

See my answer for getMD5 function here. Also look into DBMS_CRYPTO as others mentioned, this uses the older obfuscation toolkit, but the approach is the same.

To do your updates, something like:

update my_table
set my_hash = getMD5(col1 || col2 || col3);

edited May 23 '17 at 12:27

Community

1
1

answered Jul 01 '11 at 11:17

tbone

13,914
3
31
39

That doesn't help to get the hash of all the data in the consolidation series (but I didn't downvote since you provided some useful links :) – symcbean Jul 01 '11 at 11:43

Oracle hash data across rows

2 Answers2