Oracle PL/SQL Data Validation – Loop Through Fields and Check Against Lookup Table

data validationoracleplsql

I have a list of fields:

FIELD_DOMAIN_ENG_VW

+-------------+------------+-------------+
| TABLE_NAME  | FIELD_NAME | DOMAIN_NAME |
+-------------+------------+-------------+
| ENG.TABLE_1 | FIELD_1    | DOMAIN_ABC  |
| ENG.TABLE_1 | FIELD_2    | DOMAIN_XYZ  |
| ENG.TABLE_2 | FIELD_1    | DOMAIN_XYZ  |
+-------------+------------+-------------+

The view looks at all the tables in a geodatabase, and lists any fields that have a domain associated with them (a domain is the GIS equivalent of a lookup table/validation table).

The underlying tables look like this:

TABLE_1

+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_ABC} | {DOMAIN_XYZ} |
+--------------+--------------+
| A            | X            |
| B            | Y            |
| C            | zzzz         |
| BLACK SHEEP  |              |
+--------------+--------------+

TABLE_2

+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_XYZ} |              |
+--------------+--------------+
| Z            | ...          |
| Y            |              |
| X            |              |
| asdf         |              |
+--------------+--------------+

The domains look like this:

DOMAIN_VALUES_VW

+------------+------+-------------+
| DOMAIN     | CODE | DESCRIPTION |
+------------+------+-------------+
| DOMAIN_ABC | A    | EH          |
| DOMAIN_ABC | B    | BEE         |
| DOMAIN_ABC | C    | SEE         |
+------------+------+-------------+
| DOMAIN_XYZ | X    | EX          |
| DOMAIN_XYZ | Y    | WHY         |
| DOMAIN_XYZ | Z    | ZEE         |
+------------+------+-------------+

The source is an xml column in a single system table; I've extracted all the domains into this view.

Question

For validation purposes, I have made a query that will check if there are values in a field that do not match the corresponding domain:

INSERT INTO ENG.CV_ERRORS
(TABLE_NAME, FIELD_NAME, ERROR)
SELECT
    'TABLE_1' AS TABLE_NAME
    ,'FIELD_1' AS FIELD_NAME
    ,FIELD_1 AS ERROR
FROM 
    ENG.TABLE_1 
    LEFT JOIN 
        (
        SELECT CODE
        FROM  INFRASTR.D_CV_ENG_VW
        WHERE DOMAIN = 'DOMAIN_ABC'
        )
        ON FIELD_1 = CODE
WHERE 
    FIELD_1 IS NOT NULL
    AND CODE IS NULL

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME | ERROR       |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
+------------+------------+-------------+

However, this query is hardcoded to be run on a single field, in a single table at a time. I need to check all of the fields with domains, in all of the tables in the database – programmatically.

How can I do this? I'm pretty sure this can be done with PL/SQL and dynamic SQL, but I'm so new to PL/SQL that it is proving to be rather difficult.

Best Answer

Native dynamic SQL (in a PL/SQL anonymous block):

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05    BEGIN
06        DELETE FROM ENG.CV_ERRORS;
07        FOR list_fields IN (
08            SELECT 
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME 
12            FROM 
13                ENG.FIELD_DOMAIN_ENG_VW 
14            WHERE 
15                TABLE_NAME NOT LIKE '%ANNO%' 
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21    
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME
28                , ' || l_field_name || ' AS ERROR
29            FROM ' || 
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND 
43                CODE IS NULL'
44    
45            USING l_table_name, l_field_name, l_domain_name;
46    
47        END LOOP;
48    COMMIT;
49    END;

Result set

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

Steps

  1. Delete all existing rows in ENG.CV_ERRORS (oddly, the ODBC connection I'm using doesn't have truncate privileges for the table).
  2. Loop through the list of fields in FIELD_DOMAIN_ENG_VW.
  3. For each field, generate a dynamic query that looks for values that don't match the corresponding domain. Then insert them into ENG.CV_ERRORS.

It's not all that complicated now that it's all said and done. The hardest part was wrapping my head around bind variables vs. string-concatenated variables (correct terminology?), and when to use each (although I don't fully understand this yet).

Related questions

Bind variable vs. string-concatenated variable

Beginner PL/SQL: Return row value from dynamic SQL function (function, rather than a loop)

For each field name in a list of fields, get the unique values (union)

Related Question