Oracle: how to learn on what line an error?

Question

Oracle

I have a varchar field that contains numbers in different formats. Not for all values ​​works f-I TO_NUMBER. It is clear that somewhere there is an error with a comma or another, but how to find these values ​​for which the f-i does not work?

Answer 1

I would do something like this:

begin
  .... // преобразование
exception when others then 
  dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
end;

and see what line the error is on...

Answer 2

If in a hurry, then create a procedure in which you iterate over your field with the cursor, calling to_number for it. If not, then in dbms_ouput.put_line output the id of the record with this field.

Answer 3

Another quick-and-dirty, declarative-style version without pl/sql is this.
To_number is based on the format of the numeric values ​​defined by your NLS settings.
For Russian localizations, the decimal separator is usually a comma.
So, you need to look for strings with other characters (not numbers and not a comma) in this field or with more than one comma (both are errors).
For example, for a comma separator like this (with regular expressions it's easier):
Of course, if you don't have "AusDollars" as a currency symbol ( see "to_number" on docs.oracle.com ).
If the separator is not a comma, we change it to another character by analogy.