0

I have an Oracle 18c non-spatial table that has textual IDs as the unique ID:

  • The table doesn't have a ObjectID or numeric ID. I'm not able to add new columns to the table.
  • The table isn't registered with a GDB.
ID            
--------------
Bridge-100A
Bridge-100B
Bridge-100C
Struct1
Struct1-2
Struct5-2a
BR-King-1-East

I want to use the table in an ArcGIS Pro 2.9 query layer. So I need a unique ID column.

I could use Oracle's ROWNUM pseudocolumn as the unique ID:

cast(rownum as number(38,0)) as rownum

But I wonder if it would be better to use a real table column as the query layer's unique ID.

One option would be to use the textual column as-is, since I believe unique textual columns are supported by query layers.

But, I suspect it might be safer to represent the textual IDs as integers — via a calculated column in an SQL query. I suspect query layers work best with unique IDs that are integers, not text.


How can I convert the textual values to integers in a way that would be recognized by ArcGIS Pro?

1 Answer 1

0

I think I can use the ORA_HASH() function to get the numeric hash.

select 
    ora_hash(id) id_hash
from 
    my_tbl

   ID_HASH
----------
 877186355
3699931891
 307189058
2638611691
4284978767
 176185254
 858279639

Oracle Help: ORA_HASH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.