How to find non-numeric columns containing only numeric data?

Question

I like to find all columns in my Oracle database schema that only contain numeric data but having a non-numeric type. (So basically column-candidates with probably wrong chosen data types.)

I have a query for all varchar2-columns:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM user_tab_cols
WHERE DATA_TYPE = 'VARCHAR2';

Furthermore I have a query to check for any non-numeric data inside a table myTable and a column myColumn:

SELECT 1
FROM myTable
WHERE NOT REGEXP_LIKE(myColumn, '^[[:digit:]]+$');

I like to combine both queries in that way that the first query only returns the rows where not exists the second.

The main problem here is that the first query is on meta layer of the data dictionary where TABLE_NAME and COLUMN_NAME comes as data and I need that data as identifiers (and not as data) in the second query.

In pseudo-SQL I have something like that in mind:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM user_tab_cols
WHERE DATA_TYPE = 'VARCHAR2'
AND NOT EXISTS
(SELECT 1 from asIdentifier(TABLE_NAME) 
WHERE NOT REGEXP_LIKE(asIdentifier(COLUMN_NAME), '^[[:digit:]]+$'));

Answer 1

Create a function as this:

create or replace function isNumeric(val in VARCHAR2) return INTEGER AS
res NUMBER;
begin
   res := TO_NUMBER(val);
   RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

Then you can use it like this:

DECLARE
  r integer;
BEGIN
   For aCol in (SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_cols WHERE DATA_TYPE = 'VARCHAR2') LOOP
      -- What about CHAR and CLOB data types?
      execute immediate 'select count(*) from '||aCol.TABLE_NAME||' WHERE isNumeric('||aCol.COLUMN_NAME||') = 0' into r;
      if r = 0 then
         DBMS_OUTPUT.put_line(aCol.TABLE_NAME ||' '||aCol.COLUMN_NAME ||' contains numeric values only');
      end if;
   end loop;
end;

Note, the performance of this PL/SQL block will be poor. Hopefully this is a one-time-job only.