I have 50 tables, each with millions of data rows in the Staging Table, that need to be validate by Business Rules. each record and each field have n number of validation,
Example Validation Description- 1.
(STAGING_FACT_ORG_ID IS NULL)
To validate above PLSQL code used
IF STAGING_FACT_ORG_ID Is null THEN
Log('STAGING_FACT_ORG_ID IS NULL');
END IF;
Example Validation Description- 2.
(DB_NUMBER must be 9 digits numeric value)
To validate above PLSQL code used
IF (REGEXP_INSTR(DB_NUMBER, '[^0-9]') > 0 OR LENGTH(DB_NUMBER) <> 9) THEN
Log('DB_NUMBER must be 9 digits numeric value');
END IF;
Example Validation Description- 3.
(PAYMENT_TYPE is Bulk Check Contract and BRANCH_CODE is null)
To validate above PLSQL code used
IF PAYMENT_TYPE = 'B' And BRANCH_CODE Is NULL THEN
Log('PAYMENT_TYPE is Bulk Check Contract and BRANCH_CODE is null');
END IF;
All validations will be stored into a TABLE. (MAP_VALIDATIONS)
CREATE TABLE MAP_VALIDATIONS
(
VALIDATION_ID NUMBER,
VALIDATION_MESSAGE VARCHAr2(2000),
VALIDATION_CODE VARCHAR2(2000)
)
;
Instead of writing the code for each validation stored into the table MAP_VALIDATIONS build the dynamic plsql block at runtime and the same to be execute by Execute Immediate. It is taking huge time. Performance of execution hit very badly.
Note: v_String value will be build on run time by a procedure and will executed by Execute Immediate in side the procedure.
Refer the attached URL.
https://livesql.oracle.com/apex/livesql/file/content_D70IFT3XA63TPM9LH20QLSGO4.html https://livesql.oracle.com/apex/f?p=590:43:102772199759156:::43:P43_ID:88391266213389241377937284286177253496&cs=3EruiegYCsfN-HRLSu7cF7A3L0RWaqJ_fFx4JMz27Lmw91JHwx55_8D5iCCwVZebc5ZTUCBxy72tj_ry3XjB40w DECLARE
Bv_Error_Log NUMBER;
y NUMBER := NULL;
v_String VARCHAR2(3000) := 'BEGIN IF :STAGING_FACT_ORG_ID IS NULL THEN :Bv_Error_log := 1; END IF; END;';
BEGIN
FOR i IN 1 .. 1000000 LOOP
EXECUTE IMMEDIATE (v_String)
USING IN Y, OUT Bv_Error_Log;
END LOOP;
END;
So... for each row in your tables you're passing it to your PL/SQL validation routine?
This will be a horrendously slow way of doing this!
Much better is to do this in pure SQL. For example:
insert into invalid_data
select * from t
where col is null;
insert into invalid_data
select * from t
where (regexp_instr(db_number, '[^0-9]') > 0 or length(db_number) <> 9);
You could even do this in a single pass of your table, making the process quicker:
insert into invalid_data
select case when col is null then 'Y' else 'N' end null_check,
case when (regexp_instr(db_number, '[^0-9]') > 0 or length(db_number) <> 9)
then 'Y' else 'N' end num_check,
t.*
from t
where (regexp_instr(db_number, '[^0-9]') > 0 or length(db_number) <> 9) or
(col is null);
Either way, these will almost certainly come out much faster than dynamically building PL/SQL to inspect each row!