Use existing objectid fails

[KathyJones]

Hi there,
I have a large database I'm designing in Oracle 11g. I am missing something when I create the new tables with the OBJECTID. I create empty tables via SQL in oracle, then open ArcMap 10.3 to manage ->register with geodatabase.
No matter what I do, it will not recognize OBJECTID as the objectid field.
It consistently uses the second column REGION_CODE. I have even changed REGION_CODE to a VARCHAR and it still ignores OBJECTID and creates a ESRI_OID filed instead.

I have created OBJECTID as NUMBER(38) UNIQUE, NOT NULL per all the documentation.
REGION_CODE is the primary key. Any hints on getting ESRI to recognize the OBJECTID as the object id type is most appreciated. I know OBJECTID should never be the primary key. Thank you in advance  ~ Kathy

CREATE TABLE REGION
(
OBJECTID NUMBER(38) NOT NULL
, REGION_CODE NUMBER(38) NOT NULL
, REGION_NAME VARCHAR2(30 CHAR) NOT NULL
, EFFECTIVE_DATE_START DATE
, EFFECTIVE_DATE_END DATE
, COMMENTS VARCHAR2(255 CHAR)
, LAST_UPDATE_DATE DATE
, LAST_UPDATE_BY VARCHAR2(50 CHAR)
, SHAPE MDSYS.SDO_GEOMETRY
, CONSTRAINT REGION_PK PRIMARY KEY
(
REGION_CODE
)
ENABLE
);

ALTER TABLE REGION
ADD CONSTRAINT REGION_OID UNIQUE
(
OBJECTID
)
ENABLE;

[RebeccaStrauch__GISP]

tagging https://community.esri.com/groups/geodatabase?sr=search&searchId=63048ef7-e57f-42c2-9f37-b29b94d8260...‌

[AnthonyRyanEQL]

Kathy,

Good luck with this and hope you get a response. I have tried for many days a few months ago with no luck. I placed a support call and provided feedback on the technical article with no luck either. I may have narrowed it down but uncertain if it was the problem but it relat to assigning the primary key to one of my fields. If I left it off it seemed to work. The issue I found was the steps in the technical article to create the table, constraint, etc. is different to the SQL generated when you look at a feature class within SQL Developer which was created by create feature class right click context menu on the database connection in ArcCatalog. This is an area lacking documentation and when it comes to large organisations where the DBA only uses sql files to build your database, etc it's the only way it can be done.

[KathyJones]

Oh dear...  well good to know that I'm not the only one spending 8 hours banging their head on a desk  lol.  I'll keep watch here in case anyone else has a miracle answer.  In the meantime, I guess I'll try leaving off the primary key, then adding it later. 

[KathyJones]

So just to summarize the issue for future readers, the instructions from ESRI (my version is 10.3) for creating a geodatabase in Oracle and registering it in ESRI tools do not work.  They tell you you may use an existing field (any name) in the new table as long as it's unique, non-nullable, and NUMBER(38).  The issue is when you create a table with a primary key (PK) (not the OBEJCTID field) and create the OBJECTID field per instructions, the OBJECTID is not recognized when you use ESRI tools to register the geodatabase...if there is more than one NUMBER field (any length) in the table design.

Workarounds - none of these are acceptable as this is definitely an ESRI bug, but it's the best I can come up with.  

1.  Leave off the PK until the new table has been registered in the geodatabase:  This works as ArcMap or Arc catalog then recognizes the OBJECTID field as you register it. It replaced the existing unique index on the OBJECTID field though during registration - which makes sense. (It will probably work if you just leave off the unique index in your design also). Then you have to generate the PK AFTER you've registered it. If you're generating your geodatabase with JDeveloper or some other design tool, this means you will have to

a) generate all your tables using the a database design tool, 

b) manually execute SQL to delete all your PKs,

c) manually register tables with the geodatabase in ArcMap or Catalog

d) manually execute SQL to create all your PKs.  

2. Do not use a NUMBER as your PK and do not put a unique index on your OBJECTID field:  If you can avoid using a NUMBER as your PK in your design, and leave off the unique index on OBJECTID, ESRI will recognize the OBJECTID when you manually register the featureclass in the geodatabase.

3.  Create SQL to register the geodatabase objects:  This sounds impossible and risky.

These are the best solutions I could come up with now.  Too bad there isn't a way anymore to script registration of the table in the geodatabase either.