Mapping an unconstrained Oracle NUMBER to Azure SQL Database vs. Azure Database for PostgreSQL

Short answer: PostgreSQL has an exact equivalent, unconstrained NUMERIC, so you can map the column safely without knowing what's in it. Azure SQL Database has no equivalent. There you need to profile the data and pick INT/BIGINT or a fixed DECIMAL(p,s). Neither default tool mapping should be accepted without checking: SSMA's default is float(53), and Ora2Pg's is bigint.

Why this column is hard to map

• An Oracle NUMBER with no precision or scale is an exact decimal with up to 38 significant digits and a floating scale (-84 to 127). The same column can hold 42, 3.14159 and 1E-30.
• Unconstrained NUMBER is very common in legacy schemas. It is often used for IDs filled from sequences, and just as often for money or ratios.

Azure SQL Database (T-SQL)

• SSMA default is float(53). That holds only about 15–17 significant digits and is an approximate binary type. Large IDs and financial values can lose precision without any error, and equality joins and comparisons can behave differently. Treat this default as a placeholder, not a decision.
• DECIMAL/NUMERIC is capped at 38 digits with a fixed scale. DECIMAL(38,10) gives you 28 integer digits and 10 fractional digits. No single DECIMAL(p,s) covers the full Oracle range.
• What to do: profile each column, then map it:
  • Integers only (for example, sequence-populated PKs and FKs): use BIGINT (or INT). This is the most common result and the best for performance and indexing.
  • Fractional values with a known maximum precision and scale: use DECIMAL(p,s) sized to what you observed plus some headroom.
  • Genuinely scientific or approximate data: float(53) is acceptable, as a deliberate choice.
• You can override the mapping per column or type in SSMA (Project Settings → Type Mapping) and in the Azure migration tooling.
• Quick profiling query in Oracle:

  SELECT MIN(col), MAX(col),
         SUM(CASE WHEN col <> TRUNC(col) THEN 1 ELSE 0 END) AS non_integers,
         MAX(LENGTH(TO_CHAR(ABS(col)))) AS max_chars
  FROM   t;
  

Azure Database for PostgreSQL

• Unconstrained NUMERIC is a faithful, lossless match. It supports up to 131,072 digits before the decimal point and 16,383 after, so every Oracle value converts exactly.
• Ora2Pg default trap: with PG_INTEGER_TYPE=1, an unconstrained NUMBER becomes DEFAULT_NUMERIC, which defaults to bigint. Fractional values then get rounded silently: 1.5 is stored as 2. Set DEFAULT_NUMERIC numeric unless you have confirmed the column is integer-only.
• Performance trade-off: numeric is slower and larger than bigint. For integer-only key columns, still map to bigint, and do the same on the matching FK columns so join types stay consistent. Use numeric everywhere else.

Recommended rule

After the load, validate row-level checksums or SUM/MIN/MAX per numeric column to catch any rounding.

Sources

• Oracle NUMBER semantics: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html
• SSMA for Oracle type mapping: https://learn.microsoft.com/en-us/sql/ssma/oracle/project-settings-type-mapping-oracletosql
• T-SQL decimal/numeric: https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql
• PostgreSQL numeric types: https://www.postgresql.org/docs/current/datatype-numeric.html
• Ora2Pg PG_INTEGER_TYPE / DEFAULT_NUMERIC: https://ora2pg.darold.net/documentation.html

Short answer: Don't accept either tool's default. Check the real values in each column first, then set an exact type yourself: decimal(p,s)/numeric(p,s) on Azure SQL Database, numeric(p,s) on Azure Database for PostgreSQL. Use an integer type only when the data never has fractions [3].

Azure SQL Database (SSMA)

• By default, SSMA maps an unconstrained NUMBER to float(53). The same default applies to arguments, return values and local variables. Only NUMBER with an explicit precision becomes numeric [1].
• float(53) is binary floating point. It can't store most decimal fractions exactly, so money amounts and IDs can drift [1][4].
• An Oracle NUMBER is an exact decimal with up to 38 significant digits. The safe target is decimal/numeric with a precision and scale chosen from the data, or bigint if the column only ever held integers [4].
• What to do:
  1. For each column, measure the most digits before and after the decimal point.
  2. On SSMA's Type Mapping page (project or table level), change float[53] to numeric(p,s) or bigint before you run Convert Schema.
  3. Keep float only for real scientific measurements.
  4. After migration, compare SUM() and COUNT(DISTINCT) on key columns between source and target [3][4].

Azure Database for PostgreSQL (Ora2Pg)

• PostgreSQL numeric is exact and effectively unbounded, so it can hold any Oracle NUMBER value. Ora2Pg's documented type table maps NUMBER to it [2].
• The catch is Ora2Pg's shipped settings. PG_INTEGER_TYPE is on by default and DEFAULT_NUMERIC defaults to bigint, so an out-of-the-box run outputs bigint. The fractional digits are then silently dropped at load time. This matches the Ora2Pg 25.0 source code [2][6].
• What to do:
  1. Run SHOW_COLUMN and look for columns marked # for review.
  2. Set DEFAULT_NUMERIC to numeric, or turn off PG_INTEGER_TYPE, for schemas that have decimals in unconstrained columns.
  3. Use MODIFY_TYPE for individual columns that should be integer types for speed.
  4. After loading, check the data with TEST_DATA or checksums [2].

Related points

• Azure DMS has no Oracle-to-PostgreSQL scenario. For Oracle to Azure SQL, it relies on SSMA for schema conversion, so the SSMA default above still applies [7].
• If you move data with Azure Data Factory, its Oracle connector maps NUMBER without precision to Decimal(256,130) in v2.0 and Double in v1.0. Avoid v1.0 for these columns (https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle) [8].

Verdict: Neither tool's default is safe as-is — an unconstrained Oracle NUMBER (exact decimal, up to 38 significant digits) needs an explicitly profiled decimal(p,s)/numeric(p,s) on both targets; the out-of-the-box conversion tools silently pick lossy or inconsistent defaults.

Azure SQL Database

• Fidelity: partial (effort S). Oracle NUMBER w/o precision/scale is exact decimal up to 38 digits; the safe target is decimal/numeric with explicit precision/scale from the actual data, or bigint if the column only ever held integers. — Oracle to Azure SQL Database: migration guide
• Caveat (major): SSMA for Oracle's default type mapping converts unconstrained NUMBER to float(53) (binary floating point) — only NUMBER with explicit precision maps to numeric by default — so exact monetary/identifier values can silently drift unless you override the mapping. — SSMA for Oracle: Project Settings (Type Mapping)
• Applies to Oracle 11.2–26ai (all versions listed in the mapping).

Azure Database for PostgreSQL (Flexible Server)

• Fidelity: partial (effort S). PostgreSQL numeric is exact and effectively unbounded, matching Oracle NUMBER's semantics — that's Ora2Pg's base type-table mapping. — Ora2Pg documentation (configuration directives)
• Caveat (major): Ora2Pg ships with PG_INTEGER_TYPE enabled by default, which routes unconstrained NUMBER to DEFAULT_NUMERIC (default = bigint), silently dropping fractional digits at load time unless you disable PG_INTEGER_TYPE or set DEFAULT_NUMERIC to numeric. — Ora2Pg source code: lib/Ora2Pg.pm and lib/Ora2Pg/Oracle.pm
• Applies to Oracle 11.2–26ai.

Dispute: "Unconstrained NUMBER: float(53), bigint, or numeric?"

• Claim A (Microsoft/SSMA): default conversion → float(53), which cannot represent most decimal fractions exactly.
• Claim B (Ora2Pg): documented type table says numeric, but shipped defaults (PG_INTEGER_TYPE on, DEFAULT_NUMERIC=bigint) actually emit bigint, dropping fractions.
• Recorded resolution (per Ora2Pg documentation, the resolving source): "Trust none of the defaults." Profile the real data (since unconstrained NUMBER has no dictionary-enforced bound) to find actual max precision/scale, then pin an explicit type: decimal(p,s) on Azure SQL, numeric(p,s) on PostgreSQL, or a true integer type only if sampling shows no fractional values. For Ora2Pg, override DEFAULT_NUMERIC or disable PG_INTEGER_TYPE; for SSMA, edit the Type Mapping page before running Convert Schema.

No gaps: both mappings, both caveats, and the governing dispute are all present in the dataset for this feature.

Verdict: An unconstrained Oracle NUMBER (no precision/scale) has partial fidelity on both Azure SQL Database and Azure Database for PostgreSQL Flexible Server — neither tool's default produces an exact match, and the right answer is to profile the data rather than trust the default converter. Applies to Oracle 11.2 through 26ai (core SQL type, not deprecated) — NUMBER without precision/scale summary (per the oracleFeature record).

Azure SQL Database

• Fidelity: partial (effort S). Oracle's bare NUMBER is exact, up to 38 significant digits with a floating decimal point. SSMA for Oracle's default column mapping converts it to float(53) — SQL Server's binary double — which cannot represent most decimal fractions exactly; SSMA only maps to numeric when the source NUMBER has an explicit precision. SSMA for Oracle: Project Settings (Type Mapping)
• Recommended target: decimal(p,s)/numeric(p,s) with precision/scale chosen from actual data profiling, or bigint only if the sampled data is confirmed to hold no fractions. Oracle to Azure SQL Database: migration guide

Azure Database for PostgreSQL (Flexible Server)

• Fidelity: partial (effort S), even though PostgreSQL's numeric type is arbitrary-precision and exact and is a natural fit in principle. The trap is tooling defaults, not the type itself:
  • Ora2Pg's documented base type table maps NUMBER → numeric, but its PG_INTEGER_TYPE directive is enabled by default and routes an unconstrained NUMBER to DEFAULT_NUMERIC, which itself defaults to bigint — silently dropping fractional digits at load time. Ora2Pg documentation (configuration directives)
  • This is confirmed at the source-code level: Ora2Pg.pm sets pg_integer_type=1 and default_numeric=bigint when unset, and Oracle.pm's resolver returns default_numeric for a no-precision/no-scale NUMBER, flagging the column with # in SHOW_COLUMN output for manual review. Ora2Pg source code: lib/Ora2Pg.pm and lib/Ora2Pg/Oracle.pm
• Recommended target: explicit numeric(p,s), achieved by disabling PG_INTEGER_TYPE or setting DEFAULT_NUMERIC to numeric.

Dispute: "Unconstrained NUMBER: float(53), bigint, or numeric?"

The dataset records an explicit three-way tooling disagreement over this exact case:

• Claim A (Microsoft, via SSMA docs): default converts to float(53), losing exactness for fractions.
• Claim B (Ora2Pg source, community): documented type table says numeric, but shipped defaults emit bigint, dropping fractions entirely.
• Resolution (per Ora2Pg documentation (configuration directives), the resolving source): "Trust none of the defaults. Profile the real values (unconstrained NUMBER has no dictionary bound, so sample the maximum precision and scale in the data), then pin an exact type explicitly: decimal(p,s) on Azure SQL, numeric(p,s) on PostgreSQL, or a true integer type only when the sample shows no fractions. In Ora2Pg that means overriding DEFAULT_NUMERIC or disabling PG_INTEGER_TYPE; in SSMA it means editing the Type Mapping page before Convert Schema."

No caveats in the dataset restrict this by Oracle edition or by Azure tier beyond what's stated above.