ORA-01722: неверный номер, найдите конкретную строку

У меня довольно большой выбор, который выдает ошибку ORA-01722: Invalid Number . Сама ошибка ясна, но я не знаю точную строку, которая ее выбрасывает.

Есть ли способ узнать точную линию? Я действительно не хочу просматривать весь выбор и сравнивать типы, поскольку это заняло бы очень одинокое время.

Всего 3 ответа


Вы должны выполнить бинарный поиск (отметка половины столбцов, отфильтровать половину данных), чтобы найти столбец и строку, где происходит сбой. К сожалению, нет лучшего способа отладки одного оператора SQL


В большинстве случаев я встречал две причины для такого поведения. Оба находятся в области ГДЕ

  1. При сравнении числа со строковым столбцом, например

    где order_num = 2000

имеющий столбец order_num, определенный как varchar2. В этом случае выполнение завершается сбоем, когда дело доходит до номера заказа, такого как «1689_new» или около того.

  1. при сравнении числового столбца со строковым столбцом,

    где имя = возраст

Во втором случае я получил столбцы с похожим именем (table.order_num и table1.order_num), но с другим типом данных (varchar2 и number)

Поэтому я всегда проверяю состояние фильтра, комментируя их. Сначала жестко закодированные значения (см. 1 выше), затем имена столбцов.

All_tab_cols полезен для быстрой проверки типов данных


Достойные клиенты SQL сообщат строку и столбец, где произошла ошибка. Если у вас нет удобной IDE, используйте DBMS_SQL .

Клиент SQL

Хорошие SQL IDE подсвечивают номер строки и столбца ошибки. Даже SQL * Plus показывает, где именно произошла ошибка неверного числа:

SQL> select *
  2  from dual
  3  where 1 = 1
  4  and 1 = 'a'
  5  and 2 = 2;
and 1 = 'a'
        *
ERROR at line 4:
ORA-01722: invalid number

DBMS_SQL

Если ваш выбор инструментов SQL ограничен, вы все равно можете найти соответствующий номер строки, используя DBMS_SQL.LAST_ERROR_POSITION .

declare
    v_sql clob := q'[
        select *
        from dual
        where 1 = 1
            and 1 = 'a'
            and 2 = 2
    ]'
    v_cursor integer;
    v_ignore number;
begin
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
    v_ignore := dbms_sql.execute(v_cursor);
exception when others then
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line('Error starts here: '||
        substr(v_sql, dbms_sql.last_error_position));
end;
/

Results:
ORA-01722: invalid number
Error starts here:  'a'
            and 2 = 2

(В этом ответе предполагается, что вы можете напрямую запустить оператор SQL. Если ошибка является частью программы PL / SQL, то после факта нет простого способа найти номер строки внутри оператора SQL, если только вы не можете его запустить. снова через DBMS_SQL . Здесь важен точный контекст.)


Есть идеи?

10000