3

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.

3

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)

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?

  • Thanks Leigh. I've considered copying the query, and then modifying it for each field with a domain, but I think this would just be a nightmare to manage. The database is in a constant state of flux, and the queries would get out of hand. I've been trying your dynamic SQL idea, but have been struggling a bit (I'm new to PL/SQL). I'll put what I've come up with in the question as an update. – Wilson Jan 4 '17 at 17:15
  • 1
    @Wilson Unfortunately, teaching how to do dynamic SQL is beyond the scope of this question. Checkout the documentation on Native Dynamic SQL - docs.oracle.com/cd/E11882_01/appdev.112/e25519/…. – Leigh Riffel Jan 5 '17 at 14:20
  • That's fair. I've updated the question with my attempted Native Dynamic SQL. – Wilson Jan 5 '17 at 16:28
  • 1
    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 – Leigh Riffel Jan 5 '17 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.