ORA01722 INVALID NUMBER

phyllis · Post by **phyllis** » Mon Oct 11, 2004 2:11 pm

What's the best way to troubleshoot "ORA-1722 Invalid Number" errors? I'm writing to an Oracle table using the ORAOCI8 stage. The event detail dialog box in the log only shows the first 24 field values that I am converting (there are actually 51 field values in the file I'm converting), so I'm also writing the data out to a sequential file, and do not see a problem with any of the numbers output there. I'm using the following conversion for a dollar amount field value: DataTypePicS9V99(ytd_amt_chg) (defined as Numeric 7,2). For example, I'm getting the ORA-1722 Invalid number error for record #1, and in the sequential file record #1 shows a value of 0.00 for ytd_amt_chg. It's frustrating not being able to tell which column is creating the error!

phyllis · Post by **phyllis** » Mon Oct 11, 2004 4:21 pm

I've finally resolved most of those (I changed some fields defined as numeric that actually contained dates MMDDYY to timestamp in DataStage and to DATE in Oracle) except now I'm getting DataTypePic9: Error, unable to convert '. ' into number for 7 rows - but there's no event detail dialog box and no indication of what row is causing the problem.

rasi · Post by **rasi** » Mon Oct 11, 2004 6:34 pm

Try the output to a sequential file first and check column by column its data type, value and any transformation. It should help.

Thanks
Siva