[FIXED] Why does Azure Data Factory store my ORACLE number(10,0) to PARQUET decimal(38,10) using ADF COPY?

Issue

When I open the database in Oracle Developer the datatype of MWD_ID is NUMBER(10,0)

When I check sys.all_tab_columns col the data_length is 22 and precision is 10

I use the native oracle linked service to make a connection to the Oracle database. The statements are generated based on a list from all the tables I want to export. An example of for the Oracle Query statement as source. The sink is a parquet file. It is not possible to map out certain columns to enforce casting.

When i load in the parquet file and describe the dataset the format has changed to decimal(38,18)

Solution

The NUMBER datatype in Oracle stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle Database, up to 38 digits of precision. Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):

Regarding your problem, I think your issue is in ADF, not in Parquet.

ADF uses this conversion for Oracle Number when precision and scale are defined:

NUMBER (p,s)    Decimal, String (if p > 28)

ADF Oracle data type conversions

Perhaps you must check with Microsoft Azure Support, because it is clear that it should be transformed to a decimal in ADF, but keeping the precision of 10.

Answered By - Roberto Hernandez

Answer Checked By - Katrina (PHPFixing Volunteer)