Query max ObjectID from base and Adds tables

BlakeTerhune — Fri, 10 Dec 2021 20:14:21 GMT

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 <owner>.R<registration_id> 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‌

Re: Query max ObjectID from base and Adds tables

DavidBlanchard — Thu, 27 Jul 2017 14:56:16 GMT

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.

topic Re: Query max ObjectID from base and Adds tables in ArcGIS Enterprise Questions

Query max ObjectID from base and Adds tables

Re: Query max ObjectID from base and Adds tables