I am converting a very old Oracle DB to Postgres where most of the precision of the numeric columns are missing, however, In Postgres table structure the precision to the same columns are present, I am getting a plethora of numeric overflow error while moving data from Oracle DB to Postgres.

Now, I want to update all those out of precision values to the largest number of the precision in the oracle database, so, I am trying to create an update script using a Postgres table structure that can help me to find all the damaged rows in the oracle.

My query is as below(to be run on Postgres), that I want to modify to get the update script which I can run on oracle DB to get the corrupt values.

select 'update ' ||table_name || 'set ' || column_name || '=' || (numeric_precision - numeric_scale) || ' where column_name > ' || (numeric_precision - numeric_scale) from information_schema.columns where table_schema='test' and data_type='numeric';

Now here (numeric_precision - numeric_scale) if it returns a value 3 then I want to replace it with 999 in case if it is 4 then I want it to be 9999.

Please can someone let me know how this is possible, I want to run this query on Postgres DB and then I will run the resulting script on the Oracle DB?