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?

• Leigh Riffel: You've named your variable the same as the column name, while this might work, it's bad practice. You have a USING variable you aren't referencing. If you remove the USING clause your static version should work. To make it dynamic the variable in the query you want to get from the USING clause should have a colon before it. Here are some examples oracle-base.com/articles/8i/native-dynamic-sql
• User1974: Hi Leigh. I'm looking back at this old question of mine...from the back at the beginning of my career. And I have to admit, I'm kind of horrified at how bad it is. The example data in the question makes absolutely no sense. And to my dismay, the question has been viewed 5,000 times. Yikes. I'd really like to delete this question. But I don't think I can, since there's an answer from someone other than me (you) that has upvotes. I hate to be a bother, but how would you feel about deleting your answer, so that I can delete this old post? Thoughts?
• Paul White: @User1974 It's not so bad so I'm not going to delete it. Your options are enumerated at dba.stackexchange.com/help/…