how to check which value is causing SQL Error: ORA-01722: invalid number

Question

Getting SQL Error:

ORA-01722: invalid number in Insert SQL.

How to check which line or which value is causing this error because it does not give exact line number. SQL is having more than 200 values. Below message it gives 133 is starting line number of SQL.

Error starting at line : 133 in command - INSERT INTO TABLE1..... . .

SQL Error: ORA-01722: invalid number

Answer 1

In the below example you could see how to find a problematic value for error "ORA-01722: invalid number" and line number.

Here I have used a variable of Number datatype, you could also use to_number() function to detect the problematic value. Also, to get the line number of the error, I would always suggest to put " DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()" in the exception section.

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

Now, find the value which is causing this error:

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;
/