debug numeric overflow issue in Oracle

Question

In short out of ~8.2 million records I think there is one or two records that is causing a hiccup with this statement:

to_number(regexp_substr(meas_value, '\d+(\.\d+)?', 1, 1)) num1

I have parsed out the dataset into 45 smaller ones via a SAS macro running the Oracle query. 44 queries work, 1 does not.

For the life of me I don't know how to "debug" the query in Oracle that I can find the problem observation. I am using Oracle SQL developer.

Thank you in advance

Answer 1

You may use validate_conversion function to filter such problematic values:

with a (l, num) as (
  select
    level,
    lpad('9', level*30, '9')
  from dual
  connect by level < 7
)
select *
from a
where validate_conversion(num as number) = 0

 L | NUM                                                                                                                                                                                 
-: | :-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 5 | 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999                              
 6 | 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

db<>fiddle here