oracle database have data in columns which are non utf8 it seems but have no issue in current oracle usage. but when we try to do ora2pg it gives error like unicode surrogate illegal. we dont know what all tables will have this issue and which column. we have around 400+ tables and most of tables have more than 100GB of data.
we need help on how to find which table and whcih column have this issue and how to fix it at source before running ora2pg tool to export the data. such that export file is all utf8 and can be easily imported in postgres.
i have tried finding non utf characters in a table specific column using below query. but this is per column and after getting to know failed rows but not sure how to fix. also if i have to do this for each table and mostly all columns are varchar in 400+ tables it will be painfull. any query which can give all erroneous columns in a table if not whole database that will help.
select hold_release_rsn,CASE
INSTR (
RAWTOHEX (
utl_raw.cast_to_raw (
utl_i18n.raw_to_char (
utl_raw.cast_to_raw ( hold_release_rsn )
, 'utf8'
)
)
)
, 'EFBFBD'
)
WHEN 0 THEN 'OK'
ELSE 'FAIL'
END as testcase from req_hold.