Appendix C: Vector Creation Logic
C.1 SQL Types (Vector + optional element list)
Run once (schema-level):
/* ============================================================================
TYPE: T_VECTOR6_OBJ
Purpose: Fixed-width numeric vector (6 elements).
This is ideal for identity matching, hashing, indexing, and join performance.
============================================================================ */
CREATE OR REPLACE TYPE t_vector6_obj AS OBJECT (
v1 NUMBER,
v2 NUMBER,
v3 NUMBER,
v4 NUMBER,
v5 NUMBER,
v6 NUMBER
);
/
C.2 Package Spec
CREATE OR REPLACE PACKAGE vector_util AUTHID DEFINER AS
/* ==========================================================================
Package: VECTOR_UTIL
Purpose: Canonicalize input and produce a deterministic numeric vector.
Overloads:
- to_vector(p_value VARCHAR2) RETURN t_vector6_obj
- to_vector(p_value NUMBER) RETURN t_vector6_obj
Notes:
- Uses STANDARD_HASH (SHA256) for stable hashing.
- Canonicalization is intentionally strict + deterministic.
- Error handling follows a POS + ASSERT pattern (customizable).
========================================================================== */
FUNCTION to_vector(
p_value IN VARCHAR2,
p_null_ok IN BOOLEAN DEFAULT FALSE,
p_max_len IN PLS_INTEGER DEFAULT 4000
) RETURN t_vector6_obj;
FUNCTION to_vector(
p_value IN NUMBER,
p_null_ok IN BOOLEAN DEFAULT FALSE
) RETURN t_vector6_obj;
END vector_util;
/
C.3 Package Body (canonicalization + hash → vector)
CREATE OR REPLACE PACKAGE BODY vector_util AS
/* ==========================================================================
INTERNAL: POS / ASSERT / ERROR HANDLING
Replace these stubs with YOUR project’s POS/assert/error framework.
- POS is a string you set at the start of each routine/step.
- ASSERT raises a controlled exception with clear message.
========================================================================== */
g_pos VARCHAR2(4000);
PROCEDURE set_pos(p_pos IN VARCHAR2) IS
BEGIN
g_pos := p_pos;
END;
PROCEDURE assert_true(p_condition IN BOOLEAN, p_msg IN VARCHAR2) IS
BEGIN
IF NOT p_condition THEN
/* Replace with your assert mechanism */
RAISE_APPLICATION_ERROR(-20001, 'ASSERT FAILED at ' || g_pos || ': ' || p_msg);
END IF;
END;
PROCEDURE raise_err(p_msg IN VARCHAR2) IS
BEGIN
/* Replace with your error manager / logging framework */
RAISE_APPLICATION_ERROR(-20002, 'ERROR at ' || g_pos || ': ' || p_msg);
END;
/* ==========================================================================
INTERNAL: canonicalize_text
Purpose:
Produce a deterministic canonical form for matching.
Canonicalization rules (safe defaults):
- TRIM
- UPPER
- collapse internal whitespace to single space
- remove common punctuation that causes drift
- keep letters/digits/spaces only
========================================================================== */
FUNCTION canonicalize_text(
p_value IN VARCHAR2,
p_max_len IN PLS_INTEGER
) RETURN VARCHAR2 IS
l_val VARCHAR2(4000);
BEGIN
set_pos('vector_util.canonicalize_text');
IF p_value IS NULL THEN
RETURN NULL;
END IF;
assert_true(p_max_len BETWEEN 1 AND 4000, 'p_max_len out of range');
/* 1) trim and upper */
l_val := UPPER(TRIM(p_value));
/* 2) normalize whitespace (tabs/newlines -> space) */
l_val := REGEXP_REPLACE(l_val, '[[:space:]]+', ' ');
/* 3) remove punctuation that commonly varies (keep A-Z 0-9 space only) */
l_val := REGEXP_REPLACE(l_val, '[^A-Z0-9 ]', '');
/* 4) trim again (in case punctuation removal introduced edges) */
l_val := TRIM(l_val);
/* 5) enforce max length deterministically */
IF LENGTH(l_val) > p_max_len THEN
l_val := SUBSTR(l_val, 1, p_max_len);
END IF;
RETURN l_val;
EXCEPTION
WHEN OTHERS THEN
raise_err('canonicalize_text failed: ' || SQLERRM);
RETURN NULL; /* never reached */
END canonicalize_text;
/* ==========================================================================
INTERNAL: canonicalize_number
Purpose:
Deterministic number-to-text conversion for hashing and matching.
Rules:
- TO_CHAR with a stable format (no locale dependence)
- trim
========================================================================== */
FUNCTION canonicalize_number(p_value IN NUMBER) RETURN VARCHAR2 IS
l_val VARCHAR2(4000);
BEGIN
set_pos('vector_util.canonicalize_number');
IF p_value IS NULL THEN
RETURN NULL;
END IF;
/* Stable conversion: avoid locale and scientific notation surprises.
Using TM9 is usually stable; if your system uses extreme precision,
you may want a fixed format model. */
l_val := TO_CHAR(p_value, 'TM9');
l_val := TRIM(l_val);
RETURN l_val;
EXCEPTION
WHEN OTHERS THEN
raise_err('canonicalize_number failed: ' || SQLERRM);
RETURN NULL;
END canonicalize_number;
/* ==========================================================================
INTERNAL: hash_to_number
Purpose:
Convert a canonical string to a deterministic NUMBER suitable for
fast equality comparisons.
Technique:
- STANDARD_HASH(..., 'SHA256') returns hex string
- Convert chunks of hex into NUMBERs
- Each chunk is <= 15 hex chars to fit safely into NUMBER precision
Output:
- returns 6 numeric elements in t_vector6_obj for a single input.
========================================================================== */
FUNCTION sha256_hex(p_canon IN VARCHAR2) RETURN VARCHAR2 IS
l_hex VARCHAR2(64);
BEGIN
set_pos('vector_util.sha256_hex');
/* STANDARD_HASH returns a hex string for SHA256 */
l_hex := STANDARD_HASH(p_canon, 'SHA256');
assert_true(l_hex IS NOT NULL AND LENGTH(l_hex) = 64, 'Unexpected SHA256 hex length');
RETURN l_hex;
EXCEPTION
WHEN OTHERS THEN
raise_err('sha256_hex failed: ' || SQLERRM);
RETURN NULL;
END sha256_hex;
FUNCTION hex_chunk_to_number(p_hex IN VARCHAR2) RETURN NUMBER IS
l_num NUMBER;
BEGIN
set_pos('vector_util.hex_chunk_to_number');
assert_true(p_hex IS NOT NULL, 'hex chunk is NULL');
/* Convert hex chunk to number; prefix with X format */
l_num := TO_NUMBER(p_hex, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
RETURN l_num;
EXCEPTION
WHEN OTHERS THEN
raise_err('hex_chunk_to_number failed for chunk [' || p_hex || ']: ' || SQLERRM);
RETURN NULL;
END hex_chunk_to_number;
FUNCTION hex_to_vector6(p_hex64 IN VARCHAR2) RETURN t_vector6_obj IS
/* 64 hex chars; split into 6 chunks.
Chunks chosen to fit comfortably into NUMBER and be deterministic. */
l1 VARCHAR2(15);
l2 VARCHAR2(15);
l3 VARCHAR2(10);
l4 VARCHAR2(10);
l5 VARCHAR2(7);
l6 VARCHAR2(7);
BEGIN
set_pos('vector_util.hex_to_vector6');
assert_true(p_hex64 IS NOT NULL AND LENGTH(p_hex64) = 64, 'Expected 64-char hex');
/* Split deterministically (sum = 15+15+10+10+7+7 = 64) */
l1 := SUBSTR(p_hex64, 1, 15);
l2 := SUBSTR(p_hex64, 16, 15);
l3 := SUBSTR(p_hex64, 31, 10);
l4 := SUBSTR(p_hex64, 41, 10);
l5 := SUBSTR(p_hex64, 51, 7);
l6 := SUBSTR(p_hex64, 58, 7);
RETURN t_vector6_obj(
hex_chunk_to_number(l1),
hex_chunk_to_number(l2),
hex_chunk_to_number(l3),
hex_chunk_to_number(l4),
hex_chunk_to_number(l5),
hex_chunk_to_number(l6)
);
EXCEPTION
WHEN OTHERS THEN
raise_err('hex_to_vector6 failed: ' || SQLERRM);
RETURN NULL;
END hex_to_vector6;
/* ==========================================================================
PUBLIC: to_vector (VARCHAR2)
========================================================================== */
FUNCTION to_vector(
p_value IN VARCHAR2,
p_null_ok IN BOOLEAN DEFAULT FALSE,
p_max_len IN PLS_INTEGER DEFAULT 4000
) RETURN t_vector6_obj IS
l_canon VARCHAR2(4000);
l_hex VARCHAR2(64);
BEGIN
set_pos('vector_util.to_vector(VARCHAR2)');
IF p_value IS NULL THEN
IF p_null_ok THEN
/* Vector of NULLs: caller can treat as "missing identity" */
RETURN t_vector6_obj(NULL, NULL, NULL, NULL, NULL, NULL);
ELSE
raise_err('Input string is NULL and p_null_ok=FALSE');
END IF;
END IF;
l_canon := canonicalize_text(p_value, p_max_len);
IF l_canon IS NULL OR LENGTH(l_canon) = 0 THEN
IF p_null_ok THEN
RETURN t_vector6_obj(NULL, NULL, NULL, NULL, NULL, NULL);
ELSE
raise_err('Canonicalized input is empty (after normalization)');
END IF;
END IF;
l_hex := sha256_hex(l_canon);
RETURN hex_to_vector6(l_hex);
EXCEPTION
WHEN OTHERS THEN
raise_err('to_vector(VARCHAR2) failed: ' || SQLERRM);
RETURN NULL;
END to_vector;
/* ==========================================================================
PUBLIC: to_vector (NUMBER)
========================================================================== */
FUNCTION to_vector(
p_value IN NUMBER,
p_null_ok IN BOOLEAN DEFAULT FALSE
) RETURN t_vector6_obj IS
l_canon VARCHAR2(4000);
BEGIN
set_pos('vector_util.to_vector(NUMBER)');
IF p_value IS NULL THEN
IF p_null_ok THEN
RETURN t_vector6_obj(NULL, NULL, NULL, NULL, NULL, NULL);
ELSE
raise_err('Input number is NULL and p_null_ok=FALSE');
END IF;
END IF;
l_canon := canonicalize_number(p_value);
IF l_canon IS NULL OR LENGTH(l_canon) = 0 THEN
IF p_null_ok THEN
RETURN t_vector6_obj(NULL, NULL, NULL, NULL, NULL, NULL);
ELSE
raise_err('Canonicalized number is empty');
END IF;
END IF;
/* Reuse the VARCHAR2 overload for hashing/vectorization */
RETURN to_vector(l_canon, p_null_ok => p_null_ok, p_max_len => 4000);
EXCEPTION
WHEN OTHERS THEN
raise_err('to_vector(NUMBER) failed: ' || SQLERRM);
RETURN NULL;
END to_vector;
END vector_util;
/
C.4 Quick usage examples
/* String input */
SELECT vector_util.to_vector(' James Martinez ') AS vec FROM dual;
/* Number input */
SELECT vector_util.to_vector(12345) AS vec FROM dual;
/* Allow NULL */
SELECT vector_util.to_vector(NULL, p_null_ok => TRUE) AS vec FROM dual;
Robert Diekmann's Dev Demo Platform