Loop through list of fields, check against lookup table

Question

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.

Answer 1

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)

Answer 2

Here's an idea:

• Step 1 - Populate a new table with a list of tables with domain fields. You could start with a query of all_tab_columns and then tweak the results as necessary.
• Step 2 - Create a PL/SQL package that uses the table to generate a query for each column in the new table and outputs them as part of a new package definition.
• Step 3 - Save the new package created by the first package and run it anytime you need to do validation.

You will need to run through these steps again when a column in your new table changes, but you won't need to write any new code.

An alternative would be do run the SQL dynamically in step 2 rather than have it create a new package, but for performance reasons I like having it generate static SQL.

Doing it this way may be overkill. Have you considered just copying the query you have for one table 100 times and then modifying them to have the appropriate table and column names?