Revised 2017-01-04

Background:

I have a list of fields:

FIELDS_W_DOMAINS_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_2    | 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 version of a lookup table/validation table).

The data 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         |
+--------------+--------------+

And 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 a 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.ERRORS_EVW
(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 ENG.DOMAIN_VALUES_VW 
    ON FIELD_1 = CODE
WHERE 
    FIELD_1 IS NOT NULL
    AND CODE IS NULL

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |   FIELD_1   |
+------------+------------+-------------+
| 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.

I'm pretty sure this can be done with PL/SQL and dynamic SQL. I'm new to PL/SQL, and have been fighting with this for over a day, but haven't come up with anything that remotely works.

I think the PL/SQL would need to:

1. Loop through all rows in FIELDS_W_DOMAINS_VW. For each row, assign the TABLE_NAME, FIELD_NAME and DOMAIN_NAME to variables.
2. In each iteration of the loop, sub the variables in place of the hardcoded table name, field name, and domain name in the query.
3. Execute each dynamic query, which would input the values into an ERRORS table (for review).

This sounds simple enough, but I'm so new to PL/SQL that it is beyond my grasp. Can it be done?