Database Reference
In-Depth Information
DBMS_SQLHASH.GETHASH
: This method is available in Oracle 10
g
Release 2 and up. It supports
hash algorithms of SHA-1, MD4, and MD5. As a
SYSDBA
privileged user, you must grant
execute on this package to a user before they can access it. This package is documented in the
Oracle Database Security Guide
.
•
•
STANDARD_HASH
: This method is available in Oracle 12
c
Release 1 and up. This is a built-in SQL
function that computes a hash value on an expression using standard hash algorithms such as
SHA1
(default),
SHA256
,
SHA384
,
SHA512
, and
MD5
. The returned value is a
RAW
data type.
•
ORA_HASH
: This method is available in Oracle 10
g
Release 1 and up. This is a built-in SQL function
that takes a
VARCHAR2
value as input and (optionally) another pair of inputs that control the return
value. The returned value is a number—by default a number between 0 and 4294967295.
■
an array of hash and checksum functions are available in many programming languages, so there may be
others at your disposal outside the database. that said, if you use built-in database capabilities, you will have increased
your portability (to new languages, new approaches) in the future.
Note
The following example shows how you might use the
ORA_HASH
built-in function in Oracle 10
g
and above to
compute these hashes/checksums. The technique would also be applicable for the other listed approaches; the logic
would not be very much different, but the APIs you call would be. First, we'll start by removing the column we used in
the previous example:
EODA@ORA12CR1> alter table dept drop column last_mod;
Table altered.
And then have our application query and display the information for department 10. Note that while we query
the information, we compute the hash using the
ORA_HASH
built-in. This is the version information that we retain in
our application. Following is our code to query and display:
EODA@ORA12CR1> variable deptno number
EODA@ORA12CR1> variable dname varchar2(14)
EODA@ORA12CR1> variable loc varchar2(13)
EODA@ORA12CR1> variable hash number
EODA@ORA12CR1> begin
2 select deptno, dname, loc,
3 ora_hash( dname || '/' || loc ) hash
4 into :deptno, :dname, :loc, :hash
5 from dept
6 where deptno = 10;
7 end;
8 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select :deptno, :dname, :loc, :hash
2 from dual;
:DEPTNO :DNAME :LOC :HASH
---------- ---------- ---------- ----------
10 Accounting NEW YORK 2721972020
