Oracle DBMS_SQLHASH Demos

Oracle DBMS_SQLHASH Version 11.2.0.3

General Information

Purpose

Apply a supported cryptographic hash function to a SQL statement. This function can not be used to hash a string that is not a valid SQL statement.

AUTHID

CURRENT_USER

Constants

DBMS_CRYPTO Digest Types

Name	Data Type	Value
HASH_MD4	BINARY_INTEGER	1
HASH_MD5	BINARY_INTEGER	2
HASH_SH1	BINARY_INTEGER	3

Dependencies

DBMS_CRYPTO	DBMS_SQL	UTL_RAW
DBMS_LOB

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/dbmsobtk.sql

GETHASH

Provides an interface to generate the hash value of the result set returned by a SQL query

dbms_sqlhash.gethash(
sqltext IN VARCHAR2, -- input sql statement
digest_type IN BINARY_INTEGER, -- digest algorithm type
chunk_size IN NUMBER DEFAULT 134217728) -- 128M
RETURN RAW;

set serveroutput on

DECLARE
sqlstr VARCHAR2(4000):='SELECT object_name FROM all_objects WHERE rownum = 1';
digtype PLS_INTEGER := 3;
RetVal RAW(2000);
BEGIN
SELECT dbms_sqlhash.gethash(sqlstr, dbms_crypto.hash_sh1)
INTO RetVal
FROM dual;

dbms_output.put_line(RetVal);
END;
/