I am a somewhat newbie DBA to the Oracle world. I have a question
though. I have a sql script that pulls in data based on the select
criteria and inserts it into a staging table for reporting purposes.
This has worked properly for years and years, and the script has not
changed, so I know that shouldnt be problem. Recently the sql script
keeps bombing choking on bad data and fails. Presumably the
(user-imput) is bad. The table it hits has 22 million rows, so
eyeballing the data is a futile excercise. In the script I am using
the to_number function on an varchar2 field and the error states
(ORA-01722: invalid number). I did not design this database and the
reason for a money value being in varchar2 is because no math is done
on that field. Though in my script I compare a true number field to
that field (after converting with to_number) in the select to populate
a staging table. Is there a way to see the data that the sql script is
running at the time that it bombs, therefore identifying the bad row of
data? I was thinking someone slipped a comma in the varchar2 (money)
field. The app and structure CANNOT be changed to accomdate our needs
as it would invalidate our support on this enterprise app. We are
doing this for reporting purposes against this database. I have TOAD
at my disposal. Thanks again for any suggestions.
Received on Thu Dec 16 2004 - 14:15:26 CST
