ORA-01722: invalid number while executing select query

0
votes

I'm getting the error

ORA-01722: invalid number error while trying to execute "select * from TABLE_X"

I tried to filter the data to find which record is causing the error.But the difficulty is what ever I tried with Select * from the Table_x , I get the invalid number error.

Please suggest a way to fix this error.

1
sqloracle
Is table_x a view maybe? – a_horse_with_no_name
That was quick, its actually a view. – Shinoy Shaji
Then the view somewhere tries to convert a string to a number and that fails. – a_horse_with_no_name
Is there any TRIGGER associated with your table? – Ankit Bajpai
@ShinoyShaji . . . The view is broken. You -- or perhaps whoever wrote it -- needs to debug it. Perhaps what happened is that data types in underlying tables changed. But there is little you can do just by using select *. – Gordon Linoff

1 Answers

0
votes

An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a number. So somewhere along the lines, you must have tried to insert an invalid numeric value into one or more records in your table, and now it can't be selected due to it having invalid syntax like spaces between digits or disallowed non-numeric characters. Make sure you are storing numbers as integers when you are declaring data types as well.