如何检查哪个值导致SQL错误: ORA-01722:无效编号

Question

获取SQL错误：

ORA-01722:插入SQL中的无效编号。

如何检查哪一行或哪个值导致此错误，因为它没有给出确切的行号。SQL有200多个值。下面的消息给出133是SQL的起始行号。

错误从行开始: 133在命令-插入到TABLE1……。。 SQL错误: ORA-01722:无效编号

Answer 1

在下面的示例中，您可以看到如何为错误"ORA-01722:无效数字“和行号找到一个有问题的值。

这里我使用了一个数字数据类型变量，您还可以使用to_number()函数来检测有问题的值。此外，为了获得错误的行号，我建议将“DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()”放在异常部分。

create table customer (id varchar2(10));
insert into customer values (1);
insert into customer values (2);

select to_number(id) from customer; 

insert into customer values ('3'); -- oracle implicitly convert it to number datatype

select to_number(id) from customer; -- no error

insert into customer values ('a'); -- inserting a character

select to_number(id) from customer; -- throws error, ORA-01722: invalid number

现在，查找导致此错误的值：

DECLARE
  v_char_err customer.id%type;
  v_to_num NUMBER;
BEGIN
  FOR i IN
  (SELECT id FROM customer
  )
  LOOP
    v_char_err:=i.id;
    v_to_num  :=i.id;    
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line (v_char_err);                                           -- will print problematic value
  dbms_output.put_line (SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),1,4000)); -- will give line number
END;
/

Answer 2

用于第一个案例

insert into customer values ('a');

select to_number(id) from customer;

您正在尝试从一个列中转换数字，在该列中添加了非数字字符('a')，如果只插入了数字字符，则该列实际上会抛出错误无效的数字。

在第二例中

v_char_err:=i.id;

v_to_num  :=i.id; 

您再次尝试将包含非数字值的表列中的varchar类型分配给数值变量。

只需从列中删除任何非数字字符，然后测试它。不会抛出错误。