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;

  Export this post to PDF document

Add a comment

Posted by Hendrik on 15 March 2012 2:33:17 PM #

I am forever needing to look these up, so here they are once more....

  Export this post to PDF document

Add a comment

Posted by Hendrik on 13 March 2012 12:08:00 AM #