AnsweredAssumed Answered

Query max ObjectID from base and Adds tables

Question asked by blake.terhune on Jul 26, 2017
Latest reply on Jul 27, 2017 by dblanchardesri-ca-esridist

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

Outcomes