This is where the connection to Oracle breaks down.

Question

The purpose of this note is to explain what is happening when I connect ArcGIS Pro to Oracle.  It seems to work, but then runs into a road block.

In ArcGIS, create a DB connection

Clicking through

This form comes up for creating the new connection

Select the connection to get to the data.

Pick the table that has the addresses we are geo coding

This is the table we need to connect to.

Once this table can be added to arcgis project, we can use the process shown earlier for geo-coding the addresses.

s.schuette_ct · Answer

Once this table can be added to arcgis project, we can use the process shown earlier for geo-coding the addresses.

(...)

To use a field as a unique identifier in ArcGIS desktop apps, the field must be not null, must contain unique values, and be one of the following data types:

* Integer (32- and 64-bit positive values only)
* String
* GUID
* Date
(...)
To publish the query layer in a feature layer (a feature service) that references the registered data store, the ID must be a single, not null, unique, autoincrementing, 32-bit, integer field.
Unique identifier fields—ArcGIS Pro | Documentation

Create a query layer for data in a database—ArcGIS Pro | Documentation

Note, that your database is currently does not have the ArcGIS Enterprise Geodatabase functionality enabled, which would provide better ArcGIS integration of your Oracle database (such as topology, networks, or versioned editing).
Depending on your requirements this may or may not be an option. Check this with your consultant. https://pro.arcgis.com/en/pro-app/latest/help/data/databases/databases-and-arcgis.htm#ESRI_SECTION1_ECD14B1F96FC432CA95DD5136613A878

MillieBuckner · Answer

Changing the field REC_ID in the database table from type NUMBER to VARCHAR2(10)  seems to have solved the problem, but in an odd way, since integers saved as varchar do not sort by the integer value.  Order by rec_id saved varchar returns like this

1000000
2

The end result is what I wanted, a table created in arcgis filled with data copied from the table in the database.  So what drove the requirement to set a unique_ID field, that has to be a string instead of a number, instead identifying that unique id itself or creating one for it, as it does for csv data added to the arcgis?

The documentation says the unique id can have a data type of integer, but Oracle does not have integer as a data type.  You have to save integers and numbers and setup your app to not store numbers with decimal places in your number  field reserved for integer-use , or that will bomb.

I remember when arcgis caeme out in 1982.  I did not do much with it, but I had friends who were deep into it and I would see their work.  Similar experience 2005-2012.  It was full of stupid complications like this.  Here we are 42 year later, and what I see is a much larger more complicated product that can provide useful work,  still full of all kinds of stupid quirks that make working with it a form of torture.

MillieBuckner · Answer

Thank you for replying. I don't know exactly what "off-machine data" is.

What I wanted to see happen is for arcgis to respond to this attempt to add data the same way it responds to a request to add data from a csv file. In the case of the csv file

1.  Put the csv file into a folder the project can see.

2. Add the folder to the project

3. Click Add data

4. select the csv file

The result is a table in arcgis made from the data that was in csv file.

For the data in oracle, reached through a connection to a database, I guess arcgis is maybe wanting to connect to the database  instead of making a table within itself where it copies the data.  It would be understandable that a key for the table would be needed if arcgis is setting up to write to the table.

I don't want arcgis to connect that way.  I want it to read the table and copy its data into a table within arcgis.  I don't see a way to do that.