Finding dodgy numbers stored in an Oracle varchar field
Using Oracle 'dbms_output'
I was recently required to process a large amount of data using Oracle - and for whatever reason - a field that was to be interpreted as a number was actually stored as a varchar. Which although not ideal, is generally fine of course until some non-numeric characters find there way into that field. When the table contains a few million records - how do you easily identify the offending records?
Below is the simple script I used...
declare
CURSOR c1 IS SELECT * FROM my_table;
n number;
BEGIN
FOR r1 IN c1 LOOP
begin
n := To_Number(r1.offending_field);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('bad='||r1.offending_field);
END;
END LOOP;
END;