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:
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
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.