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.

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;