Database Reference
In-Depth Information
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
Search WWH ::
Custom Search