Introduction:

I have an object-relational Oracle database (ESRI geodatabase) that has validation tables (a.k.a. lookup tables). In the Geographic Information Systems (GIS) world, the validation tables are called domains. Unlike typical validation tables, domains aren't stored in individual tables. They're stored in an XML column in a single system table called GDB_Items.

The domains are used in a cartography application called ArcMap. When a user is editing data, he/she is forced to choose a domain item from a drop-down list. A domain code is stored in the database, but a domain description is displayed in the application. Using domains is a method of enforcing data integrity at the application level.

Unfortunately, there are easy ways for users to unintentionally enter values into the fields that do not match the domains. An example would be the Field Calculator tool, which is often used to batch-populate a field (multiple rows at a time). The field calculator is essentially an update query, except it runs VBScript or Python, not SQL. Using tools like the field calculator to subvert domains is a common source of error in the data.

Presently, to check for erroneous values, I need to manually run a select query on each field with a domain, in each table in the database. There are multiple fields with domains in each table, and there are approximately 100 tables in the database, so this task is far too labor-intensive to be even remotely practical (hundreds of queries, run manually each quarter).

As far as I know, this problem is largely ignored by the GIS community. But I can't ignore it; I run critical joins and calculations on these fields to produce corporate reports, and it's disastrous when there are erroneous values.

The Data:

Tables:

TABLE_1
+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_123} | {DOMAIN_ABC} |
+--------------+--------------+
|       1      | A            |
|       2      | B            |
|       3      | BLACK_SHEEP  |
|      -99     |              |
+--------------+--------------+

TABLE_2
+--------------+---------+
| FIELD_1      | FIELD_2 |
| {DOMAIN_ABC} |         |
+--------------+---------+
| A            |         |
| B            |         |
| b*           |         |
| "nuLL"**     |         |
+--------------+---------+
*Oracle joins seem to be case sensitive, so this lowercase b would cause unexpected results in a join/query.
**Believe it or not, users enter the word "null" as text, instead of setting the state of the cell to <null>.

Domains:

DOMAIN_123
+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
|    1 | ONE         |
|    2 | TWO         |
|    3 | THREE       |
+------+-------------+

DOMAIN_ABC
+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
| A    | EH          |
| B    | BEE         |
| C    | SEE         |
+------+-------------+

List of all domain values:

--ALL_DOMAIN_VALUES_VW
SELECT
    i.NAME Domain
    ,SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code'),1,255) AS Code 
FROM 
    SDE.GDB_ITEMS_VW i 
JOIN SDE.GDB_ITEMTYPES it ON i.Type = it.UUID, TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues 


Result set:
+------------+------+
|   DOMAIN   | CODE |
+------------+------+
| DOMAIN_123 | 1    |
| DOMAIN_123 | 2    |
| DOMAIN_123 | 3    |
+------------+------+
| DOMAIN_ABC | A    |
| DOMAIN_ABC | B    |
| DOMAIN_ABC | C    |
+------------+------+

List of fields with domains (extracted from an XML column in the GDB_ITEMS system table):

SELECT       
    i.NAME AS table_name
    ,EXTRACTVALUE(coded_values.COLUMN_VALUE, 'GPFieldInfoEx/Name') AS field_name
    ,EXTRACTVALUE(coded_values.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') AS domain_name 
FROM        
     SDE.GDB_ITEMS_VW i 
     JOIN SDE.GDB_ITEMTYPES it ON i.Type = it.UUID
     CROSS JOIN TABLE(XMLSEQUENCE(XMLType(definition).Extract('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx'))) coded_values
WHERE        
      EXTRACTVALUE(coded_values.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') IS NOT NULL 


Result set:
+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME | DOMAIN_NAME |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | DOMAIN_123  |
| TABLE_1    | FIELD_2    | DOMAIN_ABC  |
| TABLE_2    | FIELD_1    | DOMAIN_ABC  |
+------------+------------+-------------+

The Question:

I can identify errors in a single field, in a single table, by running this query:

SELECT     
    'TABLE_1' AS TABLE_NAME
    ,'FIELD_1' AS FIELD_NAME
    ,FIELD_1 AS ERROR
FROM
   ENG.TABLE_1
LEFT JOIN
    (
    SELECT CODE
    FROM  ENG.ALL_DOMAIN_VALUES_VW
    WHERE DOMAIN = 'DOMAIN_123'
    ) ON FIELD_1 = CODE
WHERE
    FIELD_1 IS NOT NULL
    AND CODE IS NULL


Result set:
+------------+------------+-------+
| TABLE_NAME | FIELD_NAME | ERROR |
+------------+------------+-------+
| TABLE_1    | FIELD_1    |   -99 |
+------------+------------+-------+

However, I want to run this query on all fields with domains, in a single batch process.

Can I loop through the 'List of fields with domains', and run the query on each field with a domain? Can I dynamically generate a query for each field, by subbing in the TABLE_NAME, FIELD_NAME, and DOMAIN_NAME values as variables?

I'm not picky about the output. It could be a unioned select query, or inserted values into an ERRORS table. Whatever works.