Как найти нечисловые столбцы, содержащие только числовые данные?

Создайте функцию следующим образом:

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;

Затем вы можете использовать его следующим образом:

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;

Обратите внимание, что производительность этого блока PL/SQL будет низкой. Надеюсь, это разовая работа.

Возможны два подхода: динамический SQL (DSQL) и XML.

Первый уже был продемонстрирован в другом ответе, и он быстрее.

XML-подход просто для удовольствия

create or replace function to_number_udf(p in varchar2) return number
  deterministic is
  pragma udf;
begin
  return p * 0;
  exception when invalid_number or value_error then return 1;
end to_number_udf;
/

create table t_chk(str1, str2) as
select '1', '2' from dual union all
select '0001.1000', 'helloworld' from dual;

SQL> column owner format a20
SQL> column table_name format a20
SQL> column column_name format a20
SQL> with tabs_to_check as
  2  (
  3  select 'collection("oradb:/'||owner||'/'||table_name||'")/ROW/'||column_name||'/text()' x,
  4         atc.*
  5    from all_tab_columns atc
  6   where table_name = 'T_CHK'
  7     and data_type = 'VARCHAR2'
  8     and owner = user
  9  )
 10  select --+ no_query_transformation
 11         owner, table_name, column_name
 12    from tabs_to_check ttc, xmltable(x columns "." varchar2(4000)) x
 13  group by owner, table_name, column_name
 14  having max(to_number_udf(".")) = 0;

OWNER                TABLE_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
TEST                 T_CHK                STR1

PS. В Oracle 12.2 вы можете использовать to_number(... по умолчанию... при ошибке преобразования) вместо UDF.

Более быстрый способ проверить, состоит ли строка из цифр или содержит хотя бы один нецифровой символ, — это использовать функцию translate. Увы, из-за того, что Oracle обрабатывает пустые строки не по стандарту SQL, форма функции, которую мы должны использовать, немного сложна:

translate(input_string, 'z0123456789', 'z')

(z может быть любым нецифровым символом, он нам нужен, чтобы третий аргумент не был нулевым). Это работает путем перевода z в себя, а 0 и т. д. в ничто. Таким образом, если входная строка была null или состояла только из цифр, и ТОЛЬКО в этом случае, значение, возвращаемое функцией, равно null.

Кроме того: чтобы ускорить процесс, вы можете протестировать каждый столбец с условием EXISTS. Если столбец не должен быть числовым, то в большинстве случаев условие EXISTS станет истинным очень быстро, поэтому вам придется проверять очень небольшое количество значений из таких столбцов.

Когда я пытался выполнить эту работу, я столкнулся с многочисленными побочными проблемами. Предположительно, вы хотите просмотреть все схемы (кроме SYS и, возможно, SYSTEM). Поэтому вам нужно запустить процедуру (анонимную блокировку) от учетной записи с привилегиями SYSDBA. Затем я столкнулся с проблемами с нестандартными именами таблиц и столбцов (имена, начинающиеся с подчеркивания и т. д.); что напомнило идентификаторы, определенные в двойных кавычках - ужасная практика.

Для иллюстрации я буду использовать схему HR — на которой сработал подход. Возможно, вам придется настроить это дальше; Я не смог заставить его работать, изменив строку

and owner = 'HR'

to

and owner != 'SYS'

Итак, с этим длинным вступлением, вот что я сделал.

Во-первых, в «обычной» учетной записи пользователя (моя собственная, с именем INTRO — я запускаю очень маленькую базу данных только с одним «нормальным» пользователем, плюс «стандартные» пользователи Oracle, такие как SCOTT, HR и т. д.) — так, в схеме INTRO, я создал таблицу для получения имени владельца, имени таблицы и имени столбца для всех столбцов типа данных VARCHAR2, которые содержат только «числовые» значения или null (числовое определение, как вы это сделали). ПРИМЕЧАНИЕ ЗДЕСЬ: Если вы хотите чтобы действительно проверить все числовые значения, вам действительно понадобится регулярное выражение или что-то вроде того, что показал Вернфрид; В противном случае я бы все же использовал условие EXISTS, а не COUNT в анонимной процедуре.

Затем я создал анонимный блок для поиска нужных столбцов. ПРИМЕЧАНИЕ. У вас не будет схемы INTRO — поэтому меняйте ее везде в моем коде (и при создании таблицы, и в анонимном блоке). Если процедура завершится успешно, вы сможете запросить таблицу. Я показываю это и в конце.

При входе в систему как SYS (или другого пользователя с полномочиями SYSDBA):

create table intro.cols_with_numbers (
  owner_name  varchar2(128),
  table_name  varchar2(128),
  column_name varchar2(128)
);

declare x number;
begin
  execute immediate 'truncate table intro.cols_with_numbers';
  for t in ( select owner, table_name, column_name
             from   dba_tab_columns
             where  data_type like 'VARCHAR2%'
               and  owner = 'HR'
           ) 
  loop
    execute immediate 'select case when exists (
                                select *
                                from ' || t.owner || '.' || t.table_name ||
                              ' where  translate(' || t.column_name || ',
                                         ''z0123456789'', ''z'') is not null
                              ) then 1 end
                       from   dual'    
    into x;
    if x is null then
      insert into intro.cols_with_numbers (owner_name, table_name, column_name)
         values(t.owner, t.table_name, t.column_name);
    end if;
  end loop;
end;
/

Запустите эту процедуру, а затем запросите таблицу:

select * from intro.cols_with_numbers;

no rows selected

(что означает, что в таблицах схемы HR не было числовых столбцов с неправильным типом данных VARCHAR2 — или, по крайней мере, таких столбцов, которые имели бы только неотрицательные целые значения.) Вы можете продолжить тестирование, намеренно создав таблицу с такая колонка и тестирование, чтобы убедиться, что она «поймана» процедурой.

ДОБАВЛЕНО. Вот что происходит, когда я меняю владельца с 'HR' на 'SCOTT':

PL/SQL procedure successfully completed.


OWNER_NAME           TABLE_NAME           COLUMN_NAME        
-------------------- -------------------- --------------------
SCOTT                BONUS                JOB                 
SCOTT                BONUS                ENAME   

так что, кажется, работает нормально (хотя на других схемах я иногда сталкиваюсь с ошибкой... Я посмотрю, смогу ли я понять, что это такое).

В этом случае таблица пуста (нет строк!) — это один из примеров «ложного срабатывания», который вы можете обнаружить. (В более общем случае вы получите ложное срабатывание, если все в столбце VARCHAR2 равно null — во всех строках таблицы.)

ЗАМЕТЬТЕ также, что столбец может иметь только числовые значения, и все же лучшим типом данных будет VARCHAR2. Это тот случай, когда значения являются просто идентификаторами и не подразумеваются как «числа» (которые мы можем сравнивать друг с другом или с фиксированными значениями и/или с которыми мы можем производить арифметические действия). Пример — SSN (номер социального страхования) или эквивалент в других странах; SSN является «официальным» идентификатором каждого человека для ведения бизнеса с правительством. SSN является числовым (на самом деле, возможно, чтобы подчеркнуть тот факт, что он НЕ должен быть «номером», несмотря на имя, он часто пишется с парой дефисов...)