Work Around for PL/SQL to do column validation

Question

I need to create a procedure to validate number of digits between 2 columns. I have some reason that this handling won't do in Java so it needs to be a stored procedure.

It first will get the template(result_format) from one of my table and itself contain data like

5,5,5,5,5,5,5,5,5,5,4,4,4,4,4,4,4,4,4,4,3,3,3,2,2,2,2

then the argument P_RESULT will have input like

16768,74300,56212,38614,12250,52274,73018,32467,12618,48801,4257,6831,5436,4757,9395,5294,3687,3408,2803,1680,848,695,479,81,58,28,27

then I need to compare and count the first data from first result [16768] to the result_format [5] to see whether it contains 5 digits as per the result_format, then continue till end of the result. if it detect different in the length of the result to the result format it will throw exception.

My procedure is below, it has compilation errors, it's because of it can't search my temporary table when i trying to put them into a temporary table and start my validation. [line 28]

create or replace procedure     RESULT_VALIDATION(P_LOTTERY     VARCHAR2,
                                                    P_RESULT      VARCHAR2 ) as

V_TEMPLATE          VARCHAR2(10 BYTE);
V_RESULT            RESULTS.RESULT%TYPE;
V_RESULT_FORMAT     VARCHAR2(100);

BEGIN

SELECT TEMPLATE INTO V_TEMPLATE FROM LOTTERYS WHERE ID = P_LOTTERY;

BEGIN
    SELECT RESULT_FORMAT INTO V_RESULT_FORMAT FROM LOTTERYS WHERE ID = V_TEMPLATE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN;
END;

execute immediate '
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_RESULT_VALIDATION (
results INT,
formats INT
)
ON COMMIT DROP DEFINITION ;
';


INSERT INTO ORA$PTT_RESULT_VALIDATION(results, formats)
select a.results, b.formats from (
select distinct rownum idx, regexp_substr(P_RESULT, '[^,]+', 1, LEVEL) results from dual
connect by regexp_substr(P_RESULT, '[^,]+', 1, level) is not null order by idx 
) a full join 
(
select distinct rownum idx, regexp_substr(V_RESULT_FORMAT, '[^,]+', 1, LEVEL) formats from dual
connect by  regexp_substr(V_RESULT_FORMAT, '[^,]+', 1, level) is not null order by idx
) b on a.idx = b.idx order by b.idx;

begin
for i in (select * from ORA$PTT_RESULT_VALIDATION) loop
    if REGEXP_COUNT(i.results, '\d') != i.formats then
    commit; 
    RAISE_APPLICATION_ERROR (
          num => -20000,
          msg => 'Invalid Result Format');
    end if;
end loop;
end;
commit;
END RESULT_VALIDATION;

is there any workaround that i can do something like this. or maybe not by the method of temporary table?

Please consider _fixing_ your data model and not storing CSV in your columns. Your current database design is not optimal. — Tim Biegeleisen, Dec 18 '19 at 07:29
ya, but the current schema wont be changing as i know. i have no choice although i know its not a good design. — SicaYoumi, Dec 18 '19 at 07:35

score 3 · Accepted Answer · answered Dec 18 '19 at 07:46

You can achieve it using the following query directly:

SQL> with template(result_format) as
  2  (select '5,5,5,5,5,5,5,5,5,5,4,4,4,4,4,4,4,4,4,4,3,3,3,2,2,2,2' from dual),
  3  dataa(p_result) as
  4  (select '16768,74300,56212,38614,12250,52274,73018,32467,12618,48801,4257,6831,5436,4757,9395,5294,3687,3408,2803,1680,848,695,479,81,58,28,27' from dual)
  5  SELECT
  6      CASE
  7          WHEN RES >= 1 THEN 'validation failed'
  8          ELSE 'validation passed'
  9      END AS FINAL_RESULT
 10  FROM
 11      (
 12          SELECT
 13              SUM(CASE
 14                  WHEN LENGTH(REGEXP_SUBSTR(P_RESULT, '[^,]+', 1, LEVEL)) <> REGEXP_SUBSTR(RESULT_FORMAT, '[^,]+', 1, LEVEL) THEN 1
 15                  ELSE 0
 16              END) RES
 17          FROM
 18              DATAA D
 19              CROSS JOIN TEMPLATE T
 20          CONNECT BY
 21              REGEXP_SUBSTR(P_RESULT, '[^,]+', 1, LEVEL) IS NOT NULL
 22      );

FINAL_RESULT
-----------------
validation passed

Testing with values that fails. see first value in template, I have set it to 1 but its related value in dataa is 16768 (length: 5). So it must fail.

SQL> with template(result_format) as
  2  (select '1,5,5,5,5,5,5,5,5,5,4,4,4,4,4,4,4,4,4,4,3,3,3,2,2,2,2' from dual),
  3  dataa(p_result) as
  4  (select '16768,74300,56212,38614,12250,52274,73018,32467,12618,48801,4257,6831,5436,4757,9395,5294,3687,3408,2803,1680,848,695,479,81,58,28,27' from dual)
  5  SELECT
  6      CASE
  7          WHEN RES >= 1 THEN 'validation failed'
  8          ELSE 'validation passed'
  9      END AS FINAL_RESULT
 10  FROM
 11      (
 12          SELECT
 13              SUM(CASE
 14                  WHEN LENGTH(REGEXP_SUBSTR(P_RESULT, '[^,]+', 1, LEVEL)) <> REGEXP_SUBSTR(RESULT_FORMAT, '[^,]+', 1, LEVEL) THEN 1
 15                  ELSE 0
 16              END) RES
 17          FROM
 18              DATAA D
 19              CROSS JOIN TEMPLATE T
 20          CONNECT BY
 21              REGEXP_SUBSTR(P_RESULT, '[^,]+', 1, LEVEL) IS NOT NULL
 22      );

FINAL_RESULT
-----------------
validation failed

SQL>

Note: This solution assumes that the number of values in both the strings is same.

Cheers!!

If the strings could have a NULL element, the regex of the form `[^,]+` will cause the comparison to give incorrect results because it does not handle NULLs. Use `REGEXP_SUBSTR(P_RESULT, '(.*?)(,|$)', 1, LEVEL, NULL, 1)` instead. See this post for more info: https://stackoverflow.com/a/31464699/2543416 — Gary_W, Dec 18 '19 at 14:24

Work Around for PL/SQL to do column validation

1 Answers1