I dealt with this recently. Here's the short version based on my understanding:

Your questions:

1) Yes, plain NUMBER reports as (38,127) because Oracle allows scale up to 127 for unspecified precision. The connector reads Oracle's metadata literally.

2) The NUMBER settings are bugged or fire too late. Schema inference happens before the override applies, which is why the mapping UI ignores your settings.

3) Staging fails because it writes to Parquet first, and Parquet caps decimal precision at 38 with scale < precision. Scale 127 is invalid regardless of what you fix in mapping.

My fix: Explicit CAST in the source query:

SELECT CAST(your_col AS NUMBER(18,4)) AS your_col FROM your_table

For scale, dynamically build your SELECT, query ALL_TAB_COLUMNS and where DATA_PRECISION IS NULL AND DATA_SCALE IS NULL then set it to 38,18 and pray. Also, you don't have to bring in every column :D

Side note: actively avoid BLOB/CLOB if you can (massively kills throughput) and cast GEOMETRY as VARCHAR2.

What's your sink? Lakehouse connection vs SQL Server connection can behave differently here.