Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long

Bud — Thu, 06 Mar 2025 21:50:09 GMT

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB
(will be upgraded to Pro 3.3.5; Oracle 19c 11.3 EGDB soon)

Background:

I have inherited an unregistered database view (non-spatial). The view has some joins.

select 
    --cast(rownum as number(38,0)) as rownum_,
    cast(rownum as int) as rownum_,
    sn.rdsec, 
    sn.ownership, 
    sn.name, 
    sn.from_, 
    sn.to_, 
    sn.boundary_rd, 
    sn.boundary_juris, 
    sn.sort, 
    sn.surf_mater, 
    sn.surf_width, 
    sn.shold_type, 
    sn.shold_width, 
    sn.row_, 
    sn.desrow, 
    sn.platw, 
    sn.environ, 
    sn.fenviron, 
    sn.tcyear1, 
    sn.tcaadt,
    sn.aspd,
    sn.replac_surf_width, 
    sn.class_op_grouped, 
    sn.recon_activity, 
    sde.st_length(sn.shape) as road_length, 
    rs.length_assumed_only,
    sde.st_length(sn.shape) - rs.length_assumed_only as length_vs_assumed,
    sn.numb_lanes, 
    sn.lane_km, 
    sn.strad_wavg, 
    sn.surface_condition_wavg, 
    sn.surf_year, 
    sn.base_year, 
    sn.gran_year, 
    sn.surf_age, 
    sn.base_age, 
    sn.gran_age, 
    sn.det_model_constr_type, 
    sn.next_forecast_activity, 
    sn.next_forecast_activity_descr, 
    sn.next_forecat_year, 
    sn.last_forecast_activity, 
    sn.last_forecast_activity_descr, 
    sn.last_forecat_year, 
    arw.replacement_cost
from 
    infrastr.strln_svw sn 
left join 
    infrastr.ar_road_vw arw 
on 
    sn.rdsec = arw.inventory_asset_id 
left join 
    infrastr.am_excluded_roads_sd er 
on 
    sn.rdsec = er.rdsec
left join
    (
    select
        rdsec,
        sum(evt_to - evt_from) as length_assumed_only
    from
        infrastr.road_assm_status
    where
        assm_status = 'A'
    group by
        rdsec
    ) rs
on
    sn.rdsec = rs.rdsec
where 
    er.rdsec is null 
    and (sn.aspd <> 'UNIMPROVED' or sn.aspd is null) 
    and rs.length_assumed_only is not null

Regarding casting ROWNUM as an integer:

cast(rownum as int) as rownum_,

ArcGIS Pro is treating the ROWNUM_ column as a double:

So ROWNUM_ can't be used as the unique ID when dragging the view from Contents to the map:

However, if I were to cast ROWNUM as a number(38,0), then ArcGIS Pro would treat it as a long integer, which is good:

cast(rownum as number(38,0))

Question:

Why does Pro treat cast(rownum as int) as a double?

Re: Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long

VinceAngelo — Tue, 04 Mar 2025 19:15:52 GMT

Which Pro? 3.2 introduced support for 64-bit integers. Up until then wide integers (except "NUMBER(38)") mapped to 64-bit float. [3.2.0 had issues with arcpy.ArcSDESQLExecute processing of 64-bit ints as 16-bit (massive overflow/underflow), but that was fixed by 3.2.1.]

The root of this issue is the lack of standard numeric types (short/int/long/real/float) in Oracle (everything is a "NUMBER"), forcing Esri to have to decide what type to map each NUMBER flavor to... Is NUMBER(10) a long integer, or a float (over 2.14e+10 INT_MAX)?

If Oracle implemented an "int" type, the "cast(rownum as int)" would work, but instead it maps it to a NUMBER which has resolution that overflows 32-bit storage (and isn't "NUMBER(38)"), so it falls back to double precision. I wonder if NUMBER(10) won't map to 64-bit integer once you get to Pro 3.3.x...

- V

PS: And be careful with casting rownum to a registered rowid column, because changing the ORDER BY would change the order (and so can the optimizer, at its whim). Since the point of a registered rowid column is to link queries to a specific row, with a WHERE clause, then the rownum values could differ on subsequent query.

topic Re: Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long in Geodatabase Questions

Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long

Re: Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long