Validation checks in PL/SQL-CodePudding

As per my project requirement, I need to store all validation check queries in one table and validate all records of another table and update each record with its validation status. For example, I have two tables called EMP and VALIDATIONS

Validation table has two columns as below:

-------------------    --------------
Validation_desc        Validation_sql
------------------     --------------
EID_IS_NULL            related SQL should be here
SAL_HIGH               related SQL should be here

EMPtable has normal columns like eid,ename,sal,dept,is_valid,val_desc.

I should write PL/SQL code which will fetch all validation sql's from VALIDATIONS table and check each record of EMP table and validate them. If first record got success with all validations which are available in VALIDATIONS table then EMP table IS_VALID column should be updated with 1 and Validation_desc should be null for that particular record. If second record got failed with 2 checks then that record's IS_VALID column should be updated with 0 and Validation_desc should be updated with those Validation_descwith comma separated, like wise it should check all validations for all records of EMP table.

I have tried below code to fetch all details from both the tables but not able to write logic for validations.

CREATE  PROCEDURE P_VALIDATION
as
    TYPE REC_TYPE IS  RECORD( Validation_desc     VARCHAR2(4000),
                              Validation_sql      VARCHAR2(4000));

    TYPE VAL_CHECK_TYPE IS TABLE OF REC_TYPE;

    LV_VAL_CHECK     VAL_CHECK_TYPE;

    CURSOR CUR_FEED_DATA IS SELECT * FROM EMP;

    LV_FEED_DATA    EMP%ROWTYPE;
BEGIN

    SELECT Validation_desc, Validation_sql
    BULK COLLECT INTO LV_VAL_CHECK FROM VALIDATIONS;

    OPEN CUR_FEED_DATA;
    LOOP
        FETCH CUR_FEED_DATA INTO LV_FEED_DATA;

        EXIT WHEN CUR_FEED_DATA%NOTFOUND;

        FOR I IN LV_VAL_CHECK.FIRST .. LV_VAL_CHECK.LAST LOOP
            ----SOME VALIDATIONS LOGIC HERE--
        END LOOP;

    END LOOP;

    CLOSE CUR_FEED_DATA;

END;

CodePudding user response：

There is no single type of validation. Validations can be is null, is not null, is true/false, returns rows/no rows etc. There are a couple of ways to tackle this

write your own assertion package with a procedure for each type of validation. In your table you'd store the type of validation and the expression to be evaluated. This is quite a bit of work.
leverage an open source testing framework like utPLSQL and modify that a bit to suit your needs. All kind of validations have already been implemented there. If you decide to go this route, note that there are major differences between version 2 and 3

CodePudding user response：

First off pay attention to @KoenLostrie opening sentence: There is no single type of validation. This basically says no single process will solve the problem. But using the database's built in validations should be your first line of attack. Both of the example validation simply cease to be necessary with simple predefined constraints:

Use a constraint to define ... — a rule that restricts the values in a database. Oracle Database lets you create six types of constraints ...

For the table you outlined constraints can handle validations you outlined and a couple extras:

 ------------- -------- ---------------------------------- ----------------------------------------------------- 
| Validation  | Column | Constraint Type                  | Description                                         |
 ------------- -------- ---------------------------------- ----------------------------------------------------- 
| Eid_is_Null | eid    | Primary Key                      | Guarantees eid is not null and is unique in table   |
 ------------- -------- ---------------------------------- ----------------------------------------------------- 
| Sal_High    | salary | Check (salary <= 50000)          | Guarantees salary column is not greater than 50,000 |
 ------------- -------- ---------------------------------- ----------------------------------------------------- 
| Dept_OK     | dept   | not null                         | Ensures column is present                           |
                        ---------------------------------- ----------------------------------------------------- 
|             |        | Foreign key to Departments table | Guarantees value exists in reference table          |
 ------------- -------- ---------------------------------- ----------------------------------------------------- 
| Name_Ok     | ename  | not null                         | Ensures column is present                           |
 ------------- -------- ---------------------------------- -----------------------------------------------------

For the listed above, and any other constraint added, you do not need a query to validate - the database manager simply will not allow an invalid row to exist. Of course now your code needs to handle the resulting exceptions.
Unfortunately constraints cannot handle every validation so you will still need validation routines to record those. However, you should not create a comma separated list (not only do they violate 1st normal form they are always more trouble than they are worth - how do you update the list when the error is resolved?) Instead create a new table - emp_violations as:

create table emp_violations ( 
             emp_vio_id       integer generated always as identity
           , emp_id           integer              -- or type of emp_id
           , vol_id           integer              -- or type of vio_id (the pk of violations table)
           , date_created     date
           , date_resolved    date
           , constraint       emp_violations_pk
                              primary key (emp_violation_id)
           , constraint       emp_violations_2_emp_fk
                              foreign key (emp_id)
                              references emp(mp_id)
           , constraint       emp_violations_2_violations_fk
                              foreign key (vio_id)
                              references violations(vio_id)                              
           );

With this it is easy to see what non-constraint violations have, or currently exist, and when they were resolved. Also remove the columns Validation_desc (no longer needed) and is_valid (derivable from unresolved emp_violations requiring no additional column maintenance).
If you absolutely must get is_valid and a comma separated list of violations then create a view with the LISTAGG function.