Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to identify bad data

Shadow123Oct 13 2016 — edited Oct 13 2016

Hi All

I have around millions of rows in one of my table and when I try to execute the below query then I'm getting an error message, my question is that how to identify bad data , see my query

Query

select to_date(datum_string,'ddmmyyyyHHMIAM')

from

SELECT to_char(dt,'DDMMYYYY')||to_char(tm,'FM0000')||AM_PM_INDR||'M' datum_string 

FROM DATUM 

)

;

Error

ORA-01858: a non-numeric character was found where a numeric was expected

01858. 00000 -  "a non-numeric character was found where a numeric was expected"

*Cause:    The input data to be converted using a date format model was

           incorrect.  The input data did not contain a number where a number was

           required by the format model.

*Action:   Fix the input data or the date format model to make sure the

           elements match in number and type.  Then retry the operation.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 10 2016
Added on Oct 13 2016
15 comments
4,128 views