PL/SQL Script to Find VARCHAR2 Values that Cannot be Converted to Number

This script contains a PL/SQL block to find VARCHAR2 values that cannot be converted to a number. It can be used to help resolve ORA-1722 errors.

Sometimes intermittent ORA-1722 “invalid number” errors are found when using the to_number function on VARCHAR2 columns in SQL statements. Depending on the access path you may or may not hit upon the invalid number error. This anonymous PL/SQL block lets you easily find those values that cause the ORA-1722 error.

The script

The script is as follows:

set serveroutput on
set verify off

declare
   a number;
   b rowid;
   c varchar2(2000);
begin
   for recd in (select t.rowid, t.&&col FROM &&tab t) loop
      b := recd.rowid;
      select to_number(recd.&&col) into a from &&tab where rowid=b;
   end loop;
   dbms_output.put_line('All rows valid for this column.');
exception
   when invalid_number then
      select &&col into c from &&tab where rowid = b;
      dbms_output.put_line(b||' : '||c);
end;
/

undefine col
undefine tab

Sample Output

Consider the beloe table:

SQL> select * from test;

TCOL
--------------------
1
2
C
4
5
B

6 rows selected.

If you run the above script on this table:

SQL> @savedscript
Enter value for col: tcol
Enter value for tab: test
AAAH13AAIAAAABKAAE : C

PL/SQL procedure successfully completed.

Also if you run the same script on a table column which has no invalid numbers:

SQL> @savedscript
Enter value for col: deptno
Enter value for tab: dept
All rows valid for this column.

PL/SQL procedure successfully completed.