You get this error when your SQL tries to convert a non-numeric string into a number. This conversion might be explicit – to_number(‘I am looking for trouble’) – or implicit.
This error often arises when you have a table with a varchar2 column in which you store nothing but numbers. You know that this is bad practice, but you know you will get away with it as long as you strictly store nothing but numbers in the column and all your procedures treat the contents of the column as numeric. But then, one day, you hire a new developer and, seeing that the column is varchar2, he inserts a ‘two’ – instead of 2 – into it. And suddenly all the procedures and functions that reference this column clutch their chests and die.