Building Hash Keys with SQL macro

View All Scripts Login to Run Script

Script Name Building Hash Keys with SQL macro
Description An example how to build hash keys for arbitrary table using SQL macro function. Hash keys are used in Data Vault 2.0 modelling approach. Two hash keys are added dynamically to the query result: one for the table key columns (given via parameter) and another for all table columns (used e.g. for comparing rows)
Area SQL General
Contributor Andrej_SQL
Created Monday October 12, 2020

Statement 1

We'll work with the table EMP

select * from scott.emp


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT  - 17-NOV-81 5000  - 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850  - 30
7782 CLARK MANAGER 7839 09-JUN-81 2450  - 10
7566 JONES MANAGER 7839 02-APR-81 2975  - 20
7788 SCOTT ANALYST 7566 19-APR-87 3000  - 20
7902 FORD ANALYST 7566 03-DEC-81 3000  - 20
7369 SMITH CLERK 7902 17-DEC-80 800  - 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100  - 20
7900 JAMES CLERK 7698 03-DEC-81 950  - 30
7934 MILLER CLERK 7782 23-JAN-82 1300  - 10

14 rows selected.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	17-NOV-81	5000	-	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	-	10
7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20
7369	SMITH	CLERK	7902	17-DEC-80	800	-	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	23-MAY-87	1100	-	20
7900	JAMES	CLERK	7698	03-DEC-81	950	-	30
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10

Statement 2

The function accepts a parameter of DBMS_TF.TABLE_T, so we can pass a table, view or named subquery and have the metadata about the column names and their data types. Just use the parameter name (t) in the returning string to have the make query select from whatever table was passed.

Create a table SQL macro function

CREATE OR REPLACE FUNCTION add_hash_columns(t DBMS_TF.TABLE_T  
                                          , key_cols DBMS_TF.COLUMNS_T)   
RETURN VARCHAR2 SQL_MACRO  
AS  
 v_hdiff clob ;  
 v_hkey  clob ;  
 v_str   varchar2(200);  
 v_delimiter varchar2(9):= '||''#''||';  
 v_name dbms_id;  
BEGIN  
  FOR I IN 1..t.column.count LOOP  
    v_name := t.column(i).description.name;  
    IF t.column(i).description.type = dbms_tf.type_varchar2 THEN   
      v_str := v_name;  
    ELSIF t.column(i).description.type = dbms_tf.type_number THEN  
      v_str := 'to_char('||v_name||')';  
    ELSIF t.column(i).description.type = dbms_tf.type_date THEN  
      v_str := 'to_char('||v_name||',''YYYYMMDD'')';  
    END IF;  
    v_hdiff := v_hdiff || v_delimiter || v_str;  
    IF v_name MEMBER OF key_cols THEN  
      v_hkey := v_hkey || v_delimiter || v_str;  
    END IF;   
  END LOOP;  
  v_hdiff := LTRIM(v_hdiff,'|''#');  
  v_hkey := LTRIM(v_hkey,'|''#');  
  RETURN 'SELECT STANDARD_HASH('||v_hkey||',''MD5'') hash_key, '||  
         '       STANDARD_HASH('||v_hdiff||',''MD5'') hash_diff, '||  
         ' t.* FROM t';  
END;

Function created.

Statement 3

Using Table SQL macro in the FROM clause

SELECT e.* 
FROM   add_hash_columns (scott.emp, COLUMNS(empno)) e


HASH_KEY HASH_DIFF EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
CA91C5464E73D3066825362C3093A45F A617AE4E7D4AF94C7464879DC53DBE39 7839 KING PRESIDENT  - 17-NOV-81 5000  - 10
C570C225D1FB8A72AD79995DD17A77BC A074E846273379C125C3605142D72A6E 7698 BLAKE MANAGER 7839 01-MAY-81 2850  - 30
AC5C482277858D6FE45065D0A3F92B0C 13034DF7218D2DE99D10B699BF88CE4C 7782 CLARK MANAGER 7839 09-JUN-81 2450  - 10
B937384A573B94C4D7CC6004C496F919 912804FC3834E965885F8967A9435818 7566 JONES MANAGER 7839 02-APR-81 2975  - 20
866C7EE013C58F01FA153A8D32C9ED57 60804C69F8C188BEE437076704079564 7788 SCOTT ANALYST 7566 19-APR-87 3000  - 20
66FE2BCC701BB627E111BE6847A8436C 6E58F4EAB5F85337CAAD98B55FAAB31C 7902 FORD ANALYST 7566 03-DEC-81 3000  - 20
0D7F9017FBDA691900187B22404B8A1F 42CB6932B451A10BEAD2C294FFAC41B6 7369 SMITH CLERK 7902 17-DEC-80 800  - 20
7A2B33C672CE223B2AA5789171DDDE2F AA63299F7217297D4796BFFD0D87FF72 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
E1E1F667CE4596E5644BE6FAB627C226 27332DD16B39AA6229A9FC3ABA4222EE 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
E2A7555F7CABD6E31AEF45CB8CDA4999 FD14FC582110CD1494F4853D660CFE30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
B356E7AED7EE82589E54A466E0DCA157 5AF47CCB5985386B0D979A8CA9E0FF72 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
42DAB56861D81108EE356D037190C315 833E312F54A3DCE34F57366891646345 7876 ADAMS CLERK 7788 23-MAY-87 1100  - 20
400C3241004B5DB7CA7F5ABFEF2794F2 A9B3AA96C283F6E29DF94291EBE32DA2 7900 JAMES CLERK 7698 03-DEC-81 950  - 30
6C90885B28E58D1F44856D787DA2078F 2369C4ED66B38F052276F2D182BFBE50 7934 MILLER CLERK 7782 23-JAN-82 1300  - 10

14 rows selected.

HASH_KEY	HASH_DIFF	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
CA91C5464E73D3066825362C3093A45F	A617AE4E7D4AF94C7464879DC53DBE39	7839	KING	PRESIDENT	-	17-NOV-81	5000	-	10
C570C225D1FB8A72AD79995DD17A77BC	A074E846273379C125C3605142D72A6E	7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30
AC5C482277858D6FE45065D0A3F92B0C	13034DF7218D2DE99D10B699BF88CE4C	7782	CLARK	MANAGER	7839	09-JUN-81	2450	-	10
B937384A573B94C4D7CC6004C496F919	912804FC3834E965885F8967A9435818	7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
866C7EE013C58F01FA153A8D32C9ED57	60804C69F8C188BEE437076704079564	7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
66FE2BCC701BB627E111BE6847A8436C	6E58F4EAB5F85337CAAD98B55FAAB31C	7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20
0D7F9017FBDA691900187B22404B8A1F	42CB6932B451A10BEAD2C294FFAC41B6	7369	SMITH	CLERK	7902	17-DEC-80	800	-	20
7A2B33C672CE223B2AA5789171DDDE2F	AA63299F7217297D4796BFFD0D87FF72	7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
E1E1F667CE4596E5644BE6FAB627C226	27332DD16B39AA6229A9FC3ABA4222EE	7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
E2A7555F7CABD6E31AEF45CB8CDA4999	FD14FC582110CD1494F4853D660CFE30	7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
B356E7AED7EE82589E54A466E0DCA157	5AF47CCB5985386B0D979A8CA9E0FF72	7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
42DAB56861D81108EE356D037190C315	833E312F54A3DCE34F57366891646345	7876	ADAMS	CLERK	7788	23-MAY-87	1100	-	20
400C3241004B5DB7CA7F5ABFEF2794F2	A9B3AA96C283F6E29DF94291EBE32DA2	7900	JAMES	CLERK	7698	03-DEC-81	950	-	30
6C90885B28E58D1F44856D787DA2078F	2369C4ED66B38F052276F2D182BFBE50	7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10

Statement 4

See the SQL statement actually executed

DECLARE 
  l_clob CLOB; 
BEGIN 
  DBMS_UTILITY.expand_sql_text ( 
    input_sql_text  => q'!  
               SELECT e.* 
               FROM   add_hash_columns (scott.emp, COLUMNS(empno)) e !', 
    output_sql_text => l_clob  ); 
  DBMS_OUTPUT.put_line(l_clob); 
END;

Statement processed.
SELECT "A1"."HASH_KEY" "HASH_KEY","A1"."HASH_DIFF" "HASH_DIFF","A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO" "DEPTNO" FROM  (SELECT "A3"."HASH_KEY" "HASH_KEY","A3"."HASH_DIFF" "HASH_DIFF","A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAME","A3"."JOB" "JOB","A3"."MGR" "MGR","A3"."HIREDATE" "HIREDATE","A3"."SAL" "SAL","A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO" FROM  (SELECT STANDARD_HASH(TO_CHAR("A4"."EMPNO"),'MD5') "HASH_KEY",STANDARD_HASH(TO_CHAR("A4"."EMPNO")||'#'||"A4"."ENAME"||'#'||"A4"."JOB"||'#'||TO_CHAR("A4"."MGR")||'#'||TO_CHAR("A4"."HIREDATE",'YYYYMMDD')||'#'||TO_CHAR("A4"."SAL")||'#'||TO_CHAR("A4"."COMM")||'#'||TO_CHAR("A4"."DEPTNO"),'MD5') "HASH_DIFF","A4"."EMPNO" "EMPNO","A4"."ENAME" "ENAME","A4"."JOB" "JOB","A4"."MGR" "MGR","A4"."HIREDATE" "HIREDATE","A4"."SAL" "SAL","A4"."COMM" "COMM","A4"."DEPTNO" "DEPTNO" FROM  (SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB","A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "A2") "A4") "A3") "A1"

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database