CODEWITHSUNDEEP
oracle-database
user810853
user810853

Reputation:

Looping through tables with Oracle Subqueries

I want to run an Oracle validation function against every table in the database that has a suitable column. The validation can be run against one table simply:

SELECT
count (*)
FROM
Table_name t
Where
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(t.Column_name, 0.005) <> 'TRUE';

This works fine for an individual table, but as there are over 100 to test I wanted to merge it with the Oracle Metadata table using a subquery and thus automate the whole thing. I've come up with two variants but neither works, I guess because it's having trouble passing variables from inside the subquery. My two attempts are:

SELECT TABLE_NAME tab, COLUMN_NAME col, (select count(*) from tab where sdo_geom.validate_geometry(tab.col, 0.005) <> 'TRUE')
From
All_Tab_Columns
where 
owner = 'WCCDATA' and DATA_TYPE = 'SDO_GEOMETRY'

Which returns:

SQL Error: ORA-00904: "TAB"."COL": invalid identifier 00904. 00000 - "%s: invalid identifier"

and

SELECT count(*)
From 
(SELECT
TABLE_NAME, COLUMN_NAME as col
FROM
All_Tab_Columns
where 
owner = 'WCCDATA' and DATA_TYPE = 'SDO_GEOMETRY') subquery
WHERE sdo_geom.validate_geometry(subquery.col, 0.005) <> 'TRUE';

which returns:

ORA-06512: at "MDSYS.SDO_GEOM", line 2204 00942. 00000 - "table or view does not exist"

Anyone have any thoughts? Thanks.

Upvotes: 1

Views: 7416

Answers (1)

Allan
Allan

Reputation: 17429

You can't ever reference column values as identifiers (tables, columns, etc.). In order to do this, you'll need to write some PL/SQL to create and execute the SQL dynamically, perhaps like this:

DECLARE
   CURSOR cur_tables IS
      SELECT   table_name,
                  'SELECT count(*) From '
               || table_name
               || ' WHERE sdo_geom.validate_geometry('
               || column_name
               || ', 0.005) <> ''TRUE'''
                  AS dsql
        FROM   all_tab_columns
       WHERE   owner = 'WCCDATA' AND data_type = 'SDO_GEOMETRY';
   v_count   NUMBER;
BEGIN
   FOR r_tables IN cur_tables LOOP
      EXECUTE IMMEDIATE r_tables.dsql INTO   v_count;

      DBMS_OUTPUT.put_line(r_tables.table_name || ': ' || v_count);
   END LOOP;
END;

Upvotes: 5

Related Questions