Query max ObjectID from base and Adds tables

Question

Joshua Bixby wrote: Blake, why do you want "current RowID"? Or, are you after the highest/largest ObjectID in use? I assumed they would be the same. Is it possible for a new feature to get an ObjectID lower than the highest ObjectID already assigned? My case is that we're trying to build a database trigger (yeah, I know, probably shaking your head already) that will assign our organization's custom "GlobalID" we call EID (for Enterprise ID). It's just a long integer assigned from a sequence. The trigger calling the sequence is built on the Adds table. When a record is created in the Adds table, it generates an EID value if: * Current EID is Null (for new records) * If new ObjectID > max ObjectID (for features that are copy/pasted or split) The extra catch I ran into was that finding the max ObjectID needs to look at both the Base table and the Adds table or else a brand new feature will get a new EID every time it's modified until it's been compressed to the Base table. My Original idea was to just query the base table and adds table in the trigger but apparently that causes a "mutation" error in Oracle. So my current solution is a function that queries the base and adds tables for the max ObjectID. CREATE OR REPLACE FUNCTION GIS.MaxObjectID(p_owner VARCHAR2, p_tablename VARCHAR2) RETURN NUMBER IS max_objectid NUMBER; regid NUMBER; vc2SqlStr VARCHAR2(1000); BEGIN BEGIN select tr.REGISTRATION_ID into regid from SDE.TABLE_REGISTRY tr where tr.OWNER = p_owner and tr.TABLE_NAME = p_tablename; EXCEPTION WHEN OTHERS THEN regid := 0; END; vc2SqlStr := 'select NVL(max(objectid), 0) from ( select max(OBJECTID) as objectid from '||p_owner||'.'||p_tablename|| ' union all select max(OBJECTID) as objectid from '||p_owner||'.A'||regid|| ')'; BEGIN EXECUTE IMMEDIATE vc2SqlStr INTO max_objectid; EXCEPTION WHEN OTHERS THEN max_objectid := -1; END; RETURN max_objectid; END MaxObjectID; This function is called in the trigger. BEFORE INSERT ON WS.A658 -- Adds Table for each row declare max_objectid WS.A658.objectid%type:=0; begin max_objectid := GIS.MaxObjectID('WS', 'HYDRANT_PNT'); -- Base Table Owner, Name if max_objectid <> -1 and (:new.eid is NULL or :new.objectid > max_objectid) then SELECT HYDRANT_EID_SEQ.NEXTVAL INTO :NEW.EID FROM DUAL; end if; end; So I'm looking for a better way to determine the max ObjectID (or just whether a feature is new). I was hoping the .R sequence would be the magic bullet I could call CURRVAL on but the value it returns does not seem to be correct. Also paging David Blanchardâ

DavidBlanchard · Answer

I'm not familiar with SDE in Oracle so I can't comment specifically on that.

However, I can tell you that an OBJECTID is not guaranteed to be larger than the previously added record. If multiple processes are writing to the table, one process could reserve a slice of OBJECTIDs for itself. Then, if a second process starts adding records before the first one is done, the lower OBJECTIDs of the first process will be intermixed with the higher OBJECTIDs of the second process.