I couldn't find anything "built-in" (if you do know of such a function, please comment below) - so I wrote my own.
Please note - I don't use MD5 - I use ORA_HASH - but I think this is "close enough for government work". I multiply the output of ORA_HASH by the base-10 log of the column number in the result set (+1) so that:
SELECT 'X' AS x, 'Y' AS y FROM dual
and
SELECT 'Y' AS x, 'X' AS y FROM dual;
Do not have the same checksums...
Anyway - here goes - hopefully this works well for you:
CREATE OR REPLACE FUNCTION marketplace."GET_QUERY_CHECKSUM" (
p_query IN CLOB,
p_output_checksum_query IN INTEGER DEFAULT 0,
p_use_result_cache_hint IN INTEGER DEFAULT 0,
p_primary_key_column_name IN all_tab_columns.column_name%TYPE DEFAULT NULL
)
RETURN NUMBER
AUTHID CURRENT_USER
IS
/***********************************************************************************************
Package : GET_QUERY_CHECKSUM
Author : Philip Moore
Creation Date : 05-FEB-2014 A.D.
Version : 1.0
Purpose: This function will return a checksum for a query which is passed in. It can be used
to quickly determine if the result set of the query had changed. There is a low
risk for collisions (false positive match) - however the risk is quite low.
------------------------------------------------------------------------------------------------
Modification History
Date: Version: Modified by: Modifications:
---------- -------- ------------ --------------------------------------------------
***********************************************************************************************/
-- Variables
l_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_descTbl DBMS_SQL.desc_tab;
l_colCnt NUMBER;
l_return NUMBER;
l_hash_column_sql CLOB;
l_this_column_sql VARCHAR2 (32767);
l_primary_key_column_sql VARCHAR2 (32767);
l_sql_stmt CLOB;
-- Private-scope procedure
PROCEDURE cleanup
IS
BEGIN
IF DBMS_SQL.is_open (c => l_theCursor)
THEN
DBMS_SQL.close_cursor (c => l_theCursor);
END IF;
END cleanup;
BEGIN
-- Parse the Query
DBMS_SQL.parse (c => l_theCursor,
STATEMENT => p_query,
language_flag => DBMS_SQL.native);
-- Get the column names, data types, etc..
DBMS_SQL.describe_columns (c => l_theCursor,
col_cnt => l_colCnt,
desc_t => l_descTbl);
-- Close our cursor
DBMS_SQL.close_cursor (c => l_theCursor);
-- Build our HASH SQL...
<>
FOR i IN 1 .. l_colCnt
LOOP
l_this_column_sql :=
' + ('
|| TO_CHAR (LOG (10, i + 1))
|| ' * '
|| CASE WHEN l_descTbl (i).col_null_ok THEN 'COALESCE (' END
|| 'ORA_HASH ('
|| DBMS_ASSERT.enquote_name (str => l_descTbl (i).col_name)
|| ')'
|| CASE
WHEN l_descTbl (i).col_null_ok
THEN
', ORA_HASH ('
|| DBMS_ASSERT.enquote_literal (
str => l_descTbl (i).col_name)
|| '))'
END
|| ')';
l_hash_column_sql :=
l_hash_column_sql
|| l_this_column_sql;
IF ( p_primary_key_column_name IS NOT NULL
AND l_descTbl (i).col_name = p_primary_key_column_name
)
OR
( p_primary_key_column_name IS NULL
AND i = 1
) THEN
l_primary_key_column_sql := LTRIM (l_this_column_sql, ' +');
END IF;
END LOOP column_loop;
l_sql_stmt :=
'SELECT '
|| CASE
WHEN p_use_result_cache_hint = 1 THEN '/*+ result_cache */ '
END
|| 'COUNT (*) + ROUND (COALESCE (SUM ('
|| l_primary_key_column_sql
|| ' * ('
|| LTRIM (l_hash_column_sql, ' +')
|| ')), 0), 0) AS hash_total FROM ('
|| p_query
|| ')';
IF (p_output_checksum_query = 1)
THEN
DBMS_OUTPUT.PUT_LINE (SUBSTR (l_sql_stmt, 1, 32767));
END IF;
EXECUTE IMMEDIATE l_sql_stmt INTO l_return;
RETURN l_return;
EXCEPTION
WHEN OTHERS
THEN
cleanup ();
RAISE;
END get_query_checksum;
/
p_query IN CLOB,
p_output_checksum_query IN INTEGER DEFAULT 0,
p_use_result_cache_hint IN INTEGER DEFAULT 0,
p_primary_key_column_name IN all_tab_columns.column_name%TYPE DEFAULT NULL
)
RETURN NUMBER
AUTHID CURRENT_USER
IS
/***********************************************************************************************
Package : GET_QUERY_CHECKSUM
Author : Philip Moore
Creation Date : 05-FEB-2014 A.D.
Version : 1.0
Purpose: This function will return a checksum for a query which is passed in. It can be used
to quickly determine if the result set of the query had changed. There is a low
risk for collisions (false positive match) - however the risk is quite low.
------------------------------------------------------------------------------------------------
Modification History
Date: Version: Modified by: Modifications:
---------- -------- ------------ --------------------------------------------------
***********************************************************************************************/
-- Variables
l_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_descTbl DBMS_SQL.desc_tab;
l_colCnt NUMBER;
l_return NUMBER;
l_hash_column_sql CLOB;
l_this_column_sql VARCHAR2 (32767);
l_primary_key_column_sql VARCHAR2 (32767);
l_sql_stmt CLOB;
-- Private-scope procedure
PROCEDURE cleanup
IS
BEGIN
IF DBMS_SQL.is_open (c => l_theCursor)
THEN
DBMS_SQL.close_cursor (c => l_theCursor);
END IF;
END cleanup;
BEGIN
-- Parse the Query
DBMS_SQL.parse (c => l_theCursor,
STATEMENT => p_query,
language_flag => DBMS_SQL.native);
-- Get the column names, data types, etc..
DBMS_SQL.describe_columns (c => l_theCursor,
col_cnt => l_colCnt,
desc_t => l_descTbl);
-- Close our cursor
DBMS_SQL.close_cursor (c => l_theCursor);
-- Build our HASH SQL...
<
FOR i IN 1 .. l_colCnt
LOOP
l_this_column_sql :=
' + ('
|| TO_CHAR (LOG (10, i + 1))
|| ' * '
|| CASE WHEN l_descTbl (i).col_null_ok THEN 'COALESCE (' END
|| 'ORA_HASH ('
|| DBMS_ASSERT.enquote_name (str => l_descTbl (i).col_name)
|| ')'
|| CASE
WHEN l_descTbl (i).col_null_ok
THEN
', ORA_HASH ('
|| DBMS_ASSERT.enquote_literal (
str => l_descTbl (i).col_name)
|| '))'
END
|| ')';
l_hash_column_sql :=
l_hash_column_sql
|| l_this_column_sql;
IF ( p_primary_key_column_name IS NOT NULL
AND l_descTbl (i).col_name = p_primary_key_column_name
)
OR
( p_primary_key_column_name IS NULL
AND i = 1
) THEN
l_primary_key_column_sql := LTRIM (l_this_column_sql, ' +');
END IF;
END LOOP column_loop;
l_sql_stmt :=
'SELECT '
|| CASE
WHEN p_use_result_cache_hint = 1 THEN '/*+ result_cache */ '
END
|| 'COUNT (*) + ROUND (COALESCE (SUM ('
|| l_primary_key_column_sql
|| ' * ('
|| LTRIM (l_hash_column_sql, ' +')
|| ')), 0), 0) AS hash_total FROM ('
|| p_query
|| ')';
IF (p_output_checksum_query = 1)
THEN
DBMS_OUTPUT.PUT_LINE (SUBSTR (l_sql_stmt, 1, 32767));
END IF;
EXECUTE IMMEDIATE l_sql_stmt INTO l_return;
RETURN l_return;
EXCEPTION
WHEN OTHERS
THEN
cleanup ();
RAISE;
END get_query_checksum;
/
Demo of usage:
SELECT get_query_checksum (p_query => 'SELECT * FROM all_objects') AS checksum
FROM dual;
FROM dual;