Validation
Package provides methods for validating values against rules
example
sql
SET FEEDBACK OFF;
PROMPT ========== REQUIRED ==========
-- TEST 1.1: required - POSITIVE (non-empty value)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('John', '{"type":"required","message":"Name is required"}');
DBMS_OUTPUT.put_line('TEST 1.1 (required - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 1.2: required - NEGATIVE (empty value)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('', '{"type":"required","message":"Name is required"}');
DBMS_OUTPUT.put_line('TEST 1.2 (required - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MIN-LENGTH ==========
-- TEST 2.1: min-length - POSITIVE (length meets minimum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello', '{"type":"min-length","params":3,"message":"Min 3 chars"}');
DBMS_OUTPUT.put_line('TEST 2.1 (min-length - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 2.2: min-length - NEGATIVE (length below minimum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hi', '{"type":"min-length","params":3,"message":"Min 3 chars"}');
DBMS_OUTPUT.put_line('TEST 2.2 (min-length - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MAX-LENGTH ==========
-- TEST 3.1: max-length - POSITIVE (length within maximum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hi', '{"type":"max-length","params":5,"message":"Max 5 chars"}');
DBMS_OUTPUT.put_line('TEST 3.1 (max-length - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 3.2: max-length - NEGATIVE (length exceeds maximum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('HelloWorld', '{"type":"max-length","params":5,"message":"Max 5 chars"}');
DBMS_OUTPUT.put_line('TEST 3.2 (max-length - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EQUALS ==========
-- TEST 4.1: equals - POSITIVE (values match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('admin', '{"type":"equals","params":"admin","message":"Must be admin"}');
DBMS_OUTPUT.put_line('TEST 4.1 (equals - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 4.2: equals - NEGATIVE (values do not match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user', '{"type":"equals","params":"admin","message":"Must be admin"}');
DBMS_OUTPUT.put_line('TEST 4.2 (equals - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EQUALS-NOT ==========
-- TEST 5.1: equals-not - POSITIVE (values are different)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user', '{"type":"equals-not","params":"admin","message":"Cannot be admin"}');
DBMS_OUTPUT.put_line('TEST 5.1 (equals-not - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 5.2: equals-not - NEGATIVE (values are the same)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('admin', '{"type":"equals-not","params":"admin","message":"Cannot be admin"}');
DBMS_OUTPUT.put_line('TEST 5.2 (equals-not - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== SAME-AS ==========
-- TEST 6.1: same-as - POSITIVE (values match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"same-as","params":"SecurePass123","message":"Passwords must match"}');
DBMS_OUTPUT.put_line('TEST 6.1 (same-as - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 6.2: same-as - NEGATIVE (values do not match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"same-as","params":"DifferentPass","message":"Passwords must match"}');
DBMS_OUTPUT.put_line('TEST 6.2 (same-as - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== STARTS-WITH ==========
-- TEST 7.1: starts-with - POSITIVE (starts with prefix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('https://example.com', '{"type":"starts-with","params":"https","message":"Must start with https"}');
DBMS_OUTPUT.put_line('TEST 7.1 (starts-with - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 7.2: starts-with - NEGATIVE (does not start with prefix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('http://example.com', '{"type":"starts-with","params":"https","message":"Must start with https"}');
DBMS_OUTPUT.put_line('TEST 7.2 (starts-with - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== ENDS-WITH ==========
-- TEST 8.1: ends-with - POSITIVE (ends with suffix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.com', '{"type":"ends-with","params":".com","message":"Must end with .com"}');
DBMS_OUTPUT.put_line('TEST 8.1 (ends-with - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 8.2: ends-with - NEGATIVE (does not end with suffix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.org', '{"type":"ends-with","params":".com","message":"Must end with .com"}');
DBMS_OUTPUT.put_line('TEST 8.2 (ends-with - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== CONTAINS ==========
-- TEST 9.1: contains - POSITIVE (contains substring)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello World', '{"type":"contains","params":"World","message":"Must contain World"}');
DBMS_OUTPUT.put_line('TEST 9.1 (contains - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 9.2: contains - NEGATIVE (does not contain substring)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello There', '{"type":"contains","params":"World","message":"Must contain World"}');
DBMS_OUTPUT.put_line('TEST 9.2 (contains - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== GREATER-THAN ==========
-- TEST 10.1: greater-than - POSITIVE (value greater than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('100', '{"type":"greater-than","params":50,"message":"Must be > 50"}');
DBMS_OUTPUT.put_line('TEST 10.1 (greater-than - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 10.2: greater-than - NEGATIVE (value not greater than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('25', '{"type":"greater-than","params":50,"message":"Must be > 50"}');
DBMS_OUTPUT.put_line('TEST 10.2 (greater-than - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== LESS-THAN ==========
-- TEST 11.1: less-than - POSITIVE (value less than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('30', '{"type":"less-than","params":50,"message":"Must be < 50"}');
DBMS_OUTPUT.put_line('TEST 11.1 (less-than - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 11.2: less-than - NEGATIVE (value not less than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('75', '{"type":"less-than","params":50,"message":"Must be < 50"}');
DBMS_OUTPUT.put_line('TEST 11.2 (less-than - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IN-RANGE ==========
-- TEST 12.1: in-range - POSITIVE (value within range)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('15', '{"type":"in-range","params":[10,20],"message":"Must be between 10 and 20"}');
DBMS_OUTPUT.put_line('TEST 12.1 (in-range - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 12.2: in-range - NEGATIVE (value outside range)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('25', '{"type":"in-range","params":[10,20],"message":"Must be between 10 and 20"}');
DBMS_OUTPUT.put_line('TEST 12.2 (in-range - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== INCLUDES ==========
-- TEST 13.1: includes - POSITIVE (value in list)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('green', '{"type":"includes","params":["red","green","blue"],"message":"Invalid color"}');
DBMS_OUTPUT.put_line('TEST 13.1 (includes - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 13.2: includes - NEGATIVE (value not in list)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('yellow', '{"type":"includes","params":["red","green","blue"],"message":"Invalid color"}');
DBMS_OUTPUT.put_line('TEST 13.2 (includes - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== SET ==========
-- TEST 14.1: set - POSITIVE (value in set)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('active', '{"type":"set","params":["active","inactive","pending"],"message":"Invalid status"}');
DBMS_OUTPUT.put_line('TEST 14.1 (set - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 14.2: set - NEGATIVE (value not in set)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('deleted', '{"type":"set","params":["active","inactive","pending"],"message":"Invalid status"}');
DBMS_OUTPUT.put_line('TEST 14.2 (set - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EMAIL ==========
-- TEST 15.1: email - POSITIVE (valid email)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.com', '{"type":"email","message":"Invalid email"}');
DBMS_OUTPUT.put_line('TEST 15.1 (email - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 15.2: email - NEGATIVE (invalid email)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('notanemail', '{"type":"email","message":"Invalid email"}');
DBMS_OUTPUT.put_line('TEST 15.2 (email - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== URL ==========
-- TEST 16.1: url - POSITIVE (valid URL)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('https://example.com/path', '{"type":"url","message":"Invalid URL"}');
DBMS_OUTPUT.put_line('TEST 16.1 (url - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 16.2: url - NEGATIVE (invalid URL)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('not a url', '{"type":"url","message":"Invalid URL"}');
DBMS_OUTPUT.put_line('TEST 16.2 (url - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== PASSWORD ==========
-- TEST 17.1: password - POSITIVE (valid password)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"password","message":"Invalid password"}');
DBMS_OUTPUT.put_line('TEST 17.1 (password - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 17.2: password - NEGATIVE (password too short)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Pass1', '{"type":"password","message":"Invalid password"}');
DBMS_OUTPUT.put_line('TEST 17.2 (password - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IP ==========
-- TEST 18.1: ip - POSITIVE (valid IPv4)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('192.168.1.1', '{"type":"ip","message":"Invalid IP"}');
DBMS_OUTPUT.put_line('TEST 18.1 (ip - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 18.2: ip - NEGATIVE (invalid IPv4)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('999.999.999.999', '{"type":"ip","message":"Invalid IP"}');
DBMS_OUTPUT.put_line('TEST 18.2 (ip - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== REGEXP ==========
-- TEST 19.1: regexp - POSITIVE (matches pattern)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('ABC123', '{"type":"regexp","params":"^[A-Z]{3}[0-9]{3}$","message":"Invalid format"}');
DBMS_OUTPUT.put_line('TEST 19.1 (regexp - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 19.2: regexp - NEGATIVE (does not match pattern)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('XYZ', '{"type":"regexp","params":"^[A-Z]{3}[0-9]{3}$","message":"Invalid format"}');
DBMS_OUTPUT.put_line('TEST 19.2 (regexp - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IS-JSON ==========
-- TEST 20.1: is-json - POSITIVE (valid JSON)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('{"key":"value"}', '{"type":"is-json","message":"Invalid JSON"}');
DBMS_OUTPUT.put_line('TEST 20.1 (is-json - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 20.2: is-json - NEGATIVE (invalid JSON)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('not json', '{"type":"is-json","message":"Invalid JSON"}');
DBMS_OUTPUT.put_line('TEST 20.2 (is-json - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MULTI-RULE CASE ==========
-- TEST 21: Multiple rules - all must pass
DECLARE
v_msg VARCHAR2(4000);
v_rules CLOB := '[
{"type":"required","message":"Email is required"},
{"type":"min-length","params":5,"message":"Email must be at least 5 chars"},
{"type":"email","message":"Must be a valid email"}
]';
BEGIN
v_msg := pck_api_validate.validate('user@example.com', v_rules);
DBMS_OUTPUT.put_line('TEST 21 (multi-rule - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 22: Multiple rules - fails on first violation
DECLARE
v_msg VARCHAR2(4000);
v_rules CLOB := '[
{"type":"required","message":"Email is required"},
{"type":"min-length","params":5,"message":"Email must be at least 5 chars"},
{"type":"email","message":"Must be a valid email"}
]';
BEGIN
v_msg := pck_api_validate.validate('a@b.c', v_rules);
DBMS_OUTPUT.put_line('TEST 22 (multi-rule - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== NO MESSAGE CASE ==========
-- TEST 23: Rule without custom message - uses default
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('123', '{"type":"required"}');
DBMS_OUTPUT.put_line('TEST 23 (no message - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 24: Rule without custom message - negative case shows default message
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('', '{"type":"required"}');
DBMS_OUTPUT.put_line('TEST 24 (no message - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/specification
sql
CREATE OR REPLACE PACKAGE odbvue.pck_api_validate AS -- Package provides methods for validating values against rules
FUNCTION rule ( -- Function to create a validation rule in JSON format
p_type IN VARCHAR2, -- Type of validation (e.g., 'email', 'required', 'password')
p_params IN VARCHAR2 DEFAULT NULL, -- Additional parameters for the rule in JSON format
p_message IN VARCHAR2 -- Error message if validation fails
) RETURN CLOB; -- Returns rule in JSON format
FUNCTION validate ( -- Function validates the provided value against the specified rules in JSON format
p_value IN VARCHAR2, -- Value to validate
p_rules IN CLOB -- Validation rules in JSON format [{type, params, message}]
) RETURN VARCHAR2; -- Returns NULL if valid; otherwise error message if invalid
PROCEDURE validate (
p_field IN VARCHAR2, -- Name of the field being validated
p_value IN VARCHAR2, -- Value to validate
p_rules IN CLOB, -- Validation rules in JSON format [{type, params, message}]
r_error OUT VARCHAR2, -- Output parameter for error message if invalid
r_errors OUT SYS_REFCURSOR -- Output parameter for error messages [{name, message}]
);
END pck_api_validate;
/
-- sqlcl_snapshot {"hash":"cac872219b9b4e3af141e591813052113aa537a2","type":"PACKAGE_SPEC","name":"PCK_API_VALIDATE","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_validate AS
FUNCTION regexp_like_2 ( -- experimental feature
p_value IN VARCHAR2,
p_pattern IN VARCHAR2
) RETURN BOOLEAN IS
v_pattern VARCHAR2(32767) := p_pattern;
v_sub_pattern VARCHAR2(32767);
v_start_pos PLS_INTEGER;
v_end_pos PLS_INTEGER;
v_paren_count PLS_INTEGER;
v_char CHAR(1);
v_len PLS_INTEGER;
v_in_class BOOLEAN;
v_escaped BOOLEAN;
v_cleaned_pattern VARCHAR2(32767);
BEGIN
-- Check if regexp contains (?=...) lookaheads and do in loops each part
LOOP
v_start_pos := instr(v_pattern, '(?=');
EXIT WHEN v_start_pos = 0;
v_paren_count := 1;
v_len := length(v_pattern);
v_end_pos := 0;
v_in_class := FALSE;
v_escaped := FALSE;
FOR i IN v_start_pos + 3..v_len LOOP
v_char := substr(v_pattern, i, 1);
IF v_escaped THEN
v_escaped := FALSE;
ELSE
IF v_char = '\' THEN
v_escaped := TRUE;
ELSIF v_in_class THEN
IF v_char = ']' THEN
v_in_class := FALSE;
END IF;
ELSE
IF v_char = '[' THEN
v_in_class := TRUE;
ELSIF v_char = '(' THEN
v_paren_count := v_paren_count + 1;
ELSIF v_char = ')' THEN
v_paren_count := v_paren_count - 1;
END IF;
END IF;
END IF;
IF v_paren_count = 0 THEN
v_end_pos := i;
EXIT;
END IF;
END LOOP;
IF v_end_pos = 0 THEN
RETURN FALSE;
END IF;
v_sub_pattern := substr(v_pattern, v_start_pos + 3, v_end_pos -(v_start_pos + 3));
-- Clean up escaped brackets in character classes - remove them entirely
v_cleaned_pattern := regexp_replace(v_sub_pattern, '\\\[\\\]', '', 1, 0,
'');
IF NOT regexp_like(p_value, v_cleaned_pattern) THEN
RETURN FALSE;
END IF;
v_pattern := substr(v_pattern, 1, v_start_pos - 1)
|| substr(v_pattern, v_end_pos + 1);
END LOOP;
RETURN regexp_like(p_value, v_pattern);
END;
FUNCTION num_try (
p_str IN VARCHAR2,
p_ok OUT BOOLEAN
) RETURN NUMBER IS
v_num NUMBER;
BEGIN
IF p_str IS NULL THEN
p_ok := FALSE;
RETURN NULL;
END IF;
BEGIN
-- force dot decimal regardless of session NLS
v_num := TO_NUMBER ( TRIM(BOTH '"' FROM p_str) );
p_ok := TRUE;
RETURN v_num;
EXCEPTION
WHEN OTHERS THEN
p_ok := FALSE;
RETURN NULL;
END;
END;
FUNCTION str_len (
p_str IN VARCHAR2
) RETURN PLS_INTEGER IS
BEGIN
RETURN nvl(
length(p_str),
0
);
END;
FUNCTION starts_with (
p_str IN VARCHAR2,
p_prefix IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN
p_prefix IS NOT NULL
AND p_str IS NOT NULL
AND substr(p_str,
1,
length(p_prefix)) = p_prefix;
END;
FUNCTION ends_with (
p_str IN VARCHAR2,
p_suffix IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN
p_suffix IS NOT NULL
AND p_str IS NOT NULL
AND substr(p_str,
-length(p_suffix)) = p_suffix;
END;
FUNCTION contains_substr (
p_str IN VARCHAR2,
p_part IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN
p_part IS NOT NULL
AND p_str IS NOT NULL
AND instr(p_str, p_part) > 0;
END;
FUNCTION is_email (
p_str IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
-- close to the JS one: /^[\w.-]+@([\w-]+\.)+[\w-]{2,4}$/
RETURN
p_str IS NOT NULL
AND regexp_like(p_str, '^[A-Za-z0-9._%+-]+@([A-Za-z0-9-]+\.)+[A-Za-z0-9-]{2,}$');
END;
FUNCTION is_url (
p_str IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
-- Approx of: ^(https?:\/\/)?([\w-]+\.)+[\w-]+(\/\S*)?$
RETURN
p_str IS NOT NULL
AND regexp_like(p_str, '^(https?://)?([A-Za-z0-9-]+\.)+[A-Za-z0-9-]+(/.*)?$');
END;
FUNCTION is_password (
p_str IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN
p_str IS NOT NULL
AND length(p_str) >= 8
AND regexp_like(p_str, '[A-Za-z]')
AND regexp_like(p_str, '[0-9]')
AND regexp_like(p_str, '^[A-Za-z0-9]+$');
END;
FUNCTION is_ipv4 (
p_str IN VARCHAR2
) RETURN BOOLEAN IS
v1 VARCHAR2(3);
v2 VARCHAR2(3);
v3 VARCHAR2(3);
v4 VARCHAR2(3);
n1 NUMBER;
n2 NUMBER;
n3 NUMBER;
n4 NUMBER;
ok BOOLEAN;
BEGIN
IF p_str IS NULL THEN
RETURN FALSE;
END IF;
-- Split into four octets
v1 := regexp_substr(p_str, '^[0-9]{1,3}', 1, 1);
v2 := regexp_substr(p_str, '\.([0-9]{1,3})', 1, 1, NULL,
1);
v3 := regexp_substr(p_str, '\.([0-9]{1,3})', 1, 2, NULL,
1);
v4 := regexp_substr(p_str, '\.([0-9]{1,3})', 1, 3, NULL,
1);
IF v1 IS NULL
OR v2 IS NULL
OR v3 IS NULL
OR v4 IS NULL THEN
RETURN FALSE;
END IF;
n1 := num_try(v1, ok);
IF NOT ok THEN
RETURN FALSE;
END IF;
n2 := num_try(v2, ok);
IF NOT ok THEN
RETURN FALSE;
END IF;
n3 := num_try(v3, ok);
IF NOT ok THEN
RETURN FALSE;
END IF;
n4 := num_try(v4, ok);
IF NOT ok THEN
RETURN FALSE;
END IF;
RETURN
n1 BETWEEN 0 AND 255
AND n2 BETWEEN 0 AND 255
AND n3 BETWEEN 0 AND 255
AND n4 BETWEEN 0 AND 255
AND regexp_like(p_str, '^[0-9]{1,3}(\.[0-9]{1,3}){3}$');
END;
FUNCTION is_json_text (
p_str IN VARCHAR2
) RETURN BOOLEAN IS
el json_element_t;
BEGIN
IF p_str IS NULL THEN
RETURN FALSE;
END IF;
BEGIN
el := json_element_t.parse(p_str);
RETURN el IS NOT NULL;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
END;
FUNCTION json_get_string (
o json_object_t,
k VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
IF o IS NULL
OR NOT o.has(k) THEN
RETURN NULL;
END IF;
RETURN o.get_string(k);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION json_get_elem (
o json_object_t,
k VARCHAR2
) RETURN json_element_t IS
BEGIN
IF o IS NULL
OR NOT o.has(k) THEN
RETURN NULL;
END IF;
RETURN o.get(k);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION array_includes (
arr json_array_t,
needle VARCHAR2
) RETURN BOOLEAN IS
i PLS_INTEGER := 0;
e json_element_t;
BEGIN
IF arr IS NULL THEN
RETURN FALSE;
END IF;
FOR i IN 0..arr.get_size - 1 LOOP
e := arr.get(i);
IF e.is_scalar THEN
IF e.to_string = needle THEN
RETURN TRUE;
END IF;
END IF;
END LOOP;
RETURN FALSE;
END;
FUNCTION elem_to_string (
e json_element_t
) RETURN VARCHAR2 IS
BEGIN
IF e IS NULL THEN
RETURN NULL;
END IF;
IF e.is_array
OR e.is_object THEN
RETURN e.to_string;
END IF;
RETURN replace(
trim(BOTH '"' FROM e.to_string),
'\\',
'\'
);
END;
FUNCTION eval_rule (
p_value IN VARCHAR2,
p_type IN VARCHAR2,
p_params IN json_element_t,
p_message IN VARCHAR2
) RETURN VARCHAR2 IS
v_msg VARCHAR2(4000) := nvl(p_message, 'Validation failed: ' || p_type);
v_ok BOOLEAN := TRUE;
v_num NUMBER;
v_num_ok BOOLEAN;
v_min NUMBER;
v_max NUMBER;
v_arr json_array_t;
v_str VARCHAR2(4000);
BEGIN
CASE lower(p_type)
WHEN 'required' THEN
IF p_value IS NULL
OR p_value = '' THEN
RETURN v_msg;
END IF;
WHEN 'min-length' THEN
v_num := num_try(
elem_to_string(p_params),
v_num_ok
);
IF NOT v_num_ok
OR str_len(p_value) < v_num THEN
RETURN v_msg;
END IF;
WHEN 'max-length' THEN
v_num := num_try(
elem_to_string(p_params),
v_num_ok
);
IF NOT v_num_ok
OR str_len(p_value) > v_num THEN
RETURN v_msg;
END IF;
WHEN 'equals' THEN
IF p_value != elem_to_string(p_params) THEN
RETURN v_msg;
END IF;
WHEN 'equals-not' THEN
IF p_value = elem_to_string(p_params) THEN
RETURN v_msg;
END IF;
WHEN 'same-as' THEN
IF p_value != elem_to_string(p_params) THEN
RETURN v_msg;
END IF;
WHEN 'starts-with' THEN
IF NOT starts_with(p_value,
elem_to_string(p_params)) THEN
RETURN v_msg;
END IF;
WHEN 'ends-with' THEN
IF NOT ends_with(p_value,
elem_to_string(p_params)) THEN
RETURN v_msg;
END IF;
WHEN 'contains' THEN
IF NOT contains_substr(p_value,
elem_to_string(p_params)) THEN
RETURN v_msg;
END IF;
WHEN 'greater-than' THEN
v_num := num_try(
elem_to_string(p_params),
v_num_ok
);
IF NOT v_num_ok THEN
RETURN v_msg;
END IF;
DECLARE
v_val NUMBER;
v_val_ok BOOLEAN;
BEGIN
v_val := num_try(p_value, v_val_ok);
IF NOT v_val_ok
OR v_val <= v_num THEN
RETURN v_msg;
END IF;
END;
WHEN 'less-than' THEN
v_num := num_try(
elem_to_string(p_params),
v_num_ok
);
IF NOT v_num_ok THEN
RETURN v_msg;
END IF;
DECLARE
v_val NUMBER;
v_val_ok BOOLEAN;
BEGIN
v_val := num_try(p_value, v_val_ok);
IF NOT v_val_ok
OR v_val >= v_num THEN
RETURN v_msg;
END IF;
END;
WHEN 'in-range' THEN
IF p_params IS NULL
OR NOT p_params.is_array THEN
RETURN v_msg;
END IF;
v_arr := treat(p_params AS json_array_t);
IF v_arr.get_size < 2 THEN
RETURN v_msg;
END IF;
v_min := num_try(v_arr.get(0).to_string,
v_num_ok);
IF NOT v_num_ok THEN
RETURN v_msg;
END IF;
v_max := num_try(v_arr.get(1).to_string,
v_num_ok);
IF NOT v_num_ok THEN
RETURN v_msg;
END IF;
DECLARE
v_val NUMBER;
v_val_ok BOOLEAN;
BEGIN
v_val := num_try(p_value, v_val_ok);
IF NOT v_val_ok
OR v_val < v_min
OR v_val > v_max THEN
RETURN v_msg;
END IF;
END;
WHEN 'includes' THEN
IF p_params IS NULL
OR NOT p_params.is_array THEN
RETURN v_msg;
END IF;
v_arr := treat(p_params AS json_array_t);
IF NOT array_includes(v_arr, p_value) THEN
RETURN v_msg;
END IF;
WHEN 'set' THEN
IF p_params IS NULL
OR NOT p_params.is_array THEN
RETURN v_msg;
END IF;
v_arr := treat(p_params AS json_array_t);
IF NOT array_includes(v_arr, p_value) THEN
RETURN v_msg;
END IF;
WHEN 'password' THEN
IF NOT is_password(p_value) THEN
RETURN v_msg;
END IF;
WHEN 'email' THEN
IF NOT is_email(p_value) THEN
RETURN v_msg;
END IF;
WHEN 'url' THEN
IF NOT is_url(p_value) THEN
RETURN v_msg;
END IF;
WHEN 'ip' THEN
IF NOT is_ipv4(p_value) THEN
RETURN v_msg;
END IF;
WHEN 'regexp' THEN
v_str := elem_to_string(p_params);
IF v_str IS NULL
OR NOT regexp_like_2(p_value, v_str) THEN
RETURN v_msg;
END IF;
WHEN 'is-json' THEN
IF NOT is_json_text(p_value) THEN
RETURN v_msg;
END IF;
WHEN 'custom' THEN
-- JS allows a function; PL/SQL cannot execute passed code.
RETURN nvl(p_message, 'Validation failed: custom rule is not supported in PL/SQL');
ELSE
RETURN 'Validation failed: unknown rule type "'
|| p_type
|| '"';
END CASE;
RETURN NULL; -- passed
END;
FUNCTION validate (
p_value IN VARCHAR2,
p_rules IN CLOB
) RETURN VARCHAR2 IS
el json_element_t;
rulese json_element_t;
ruleo json_object_t;
rulesa json_array_t;
i PLS_INTEGER;
v_type VARCHAR2(200);
v_msg VARCHAR2(4000);
v_err VARCHAR2(4000);
BEGIN
IF p_rules IS NULL
OR dbms_lob.getlength(p_rules) = 0 THEN
RETURN NULL; -- no rules => valid
END IF;
el := json_element_t.parse(p_rules);
-- Accept either: rule object, array of rule objects, or { "rules": [...] }
IF el.is_object THEN
ruleo := treat(el AS json_object_t);
IF ruleo.has('rules') THEN
rulese := ruleo.get('rules');
IF rulese.is_array THEN
rulesa := treat(rulese AS json_array_t);
ELSE
-- single object inside "rules"
IF rulese.is_object THEN
rulesa := NEW json_array_t();
rulesa.append(rulese);
ELSE
RETURN 'Invalid JSON: "rules" must be an array or object';
END IF;
END IF;
ELSE
-- a single rule object
rulesa := NEW json_array_t();
rulesa.append(el);
END IF;
ELSIF el.is_array THEN
rulesa := treat(el AS json_array_t);
ELSE
RETURN 'Invalid JSON: rules must be an object or an array';
END IF;
-- Iterate and fast-fail
FOR i IN 0..rulesa.get_size - 1 LOOP
ruleo := treat(rulesa.get(i) AS json_object_t);
IF ruleo IS NULL THEN
RETURN 'Invalid rule at index ' || i;
END IF;
v_type := lower(json_get_string(ruleo, 'type'));
v_msg := json_get_string(ruleo, 'message');
v_err := eval_rule(
p_value => p_value,
p_type => v_type,
p_params => json_get_elem(ruleo, 'params'),
p_message => v_msg
);
IF v_err IS NOT NULL THEN
RETURN v_err; -- fast fail
END IF;
END LOOP;
RETURN NULL; -- all good
EXCEPTION
WHEN OTHERS THEN
RETURN 'Validation error: ' || sqlerrm;
END;
FUNCTION rule (
p_type IN VARCHAR2,
p_params IN VARCHAR2 DEFAULT NULL,
p_message IN VARCHAR2
) RETURN CLOB IS
v_rule json_object_t := NEW json_object_t();
BEGIN
v_rule.put('type', p_type);
IF p_params IS NOT NULL THEN
v_rule.put('params', p_params);
END IF;
v_rule.put('message', p_message);
RETURN v_rule.to_clob;
END;
PROCEDURE validate (
p_field IN VARCHAR2,
p_value IN VARCHAR2,
p_rules IN CLOB,
r_error OUT VARCHAR2,
r_errors OUT SYS_REFCURSOR
) IS
BEGIN
FOR r IN (
SELECT
*
FROM
JSON_TABLE ( p_rules, '$[*]'
COLUMNS (
type VARCHAR2 ( 200 ) PATH '$.type',
params CLOB PATH '$.params',
message VARCHAR2 ( 4000 ) PATH '$.message'
)
)
) LOOP
r_error := validate(
p_value => p_value,
p_rules => rule(
p_type => r.type,
p_params => r.params,
p_message => r.message
)
);
IF r_error IS NOT NULL THEN
OPEN r_errors FOR SELECT
p_field AS "name",
r_error AS "message"
FROM
dual;
RETURN;
END IF;
END LOOP;
END;
END pck_api_validate;
/
-- sqlcl_snapshot {"hash":"325f71963e3f366187d03eb30e23e0554c566930","type":"PACKAGE_BODY","name":"PCK_API_VALIDATE","schemaName":"ODBVUE","sxml":""}RULE
Function to create a validation rule in JSON format
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | Returns rule in JSON format | ||
| P_TYPE | IN | VARCHAR2 | Type of validation (e.g., 'email', 'required', 'password') | |
| P_PARAMS | IN | VARCHAR2 | NULL | Additional parameters for the rule in JSON format |
| P_MESSAGE | IN | VARCHAR2 | Error message if validation fails |
VALIDATE
Function validates the provided value against the specified rules in JSON format
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | VARCHAR2 | Returns NULL if valid; otherwise error message if invalid | ||
| P_VALUE | IN | VARCHAR2 | Value to validate | |
| P_RULES | IN | CLOB | Validation rules in JSON format [{type, params, message}] |
VALIDATE
Function validates the provided value against the specified rules in JSON format
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_FIELD | IN | VARCHAR2 | Name of the field being validated | |
| P_VALUE | IN | VARCHAR2 | Value to validate | |
| P_RULES | IN | CLOB | Validation rules in JSON format [{type, params, message}] | |
| R_ERROR | OUT | VARCHAR2 | Output parameter for error message if invalid | |
| R_ERRORS | OUT | REF CURSOR | Output parameter for error messages [{name, message}] |