I am working in ArcGIS 10.1 with tables from Oracle, now under control of SDE, after making use of the option: 'Register with Geodatabase'. All these tables have a column named 'Objectid' with an associated sequence controlled by SDE. However, my goal here is not just using ArcGIS exclusively but keeping the old functionality in Oracle, i.e. through SQL Developer, not via SDE. My biggest problem now is how I could handle the sequence associated with the 'Objectid' field.
So, where in the system is this sequence stored, I cannot see it in my Oracle schema's sequences.
In the end, how could I add records to the tables directly in Oracle without corrupting and/or badly affecting the SDE sequence?
Best Answer
One way of doing it:
INCREMENT BY 16
toINCREMENT BY 1
, and add toSTART WITH
the last number of your current sequence, but adding one extra unit (+ 1).grant select on R5834 to SDE;
select DBMS_PIPE.REMOVE_PIPE('ARCSDE_IDPIPE5834') from dual;
Create an INSERT trigger that does the following:
CREATE OR REPLACE TRIGGER "schema"."trigger_name" BEFORE INSERT ON schema.table FOR EACH ROW BEGIN IF :NEW.OBJECTID = -1 THEN select SDE.VERSION_USER_DDL.NEXT_ROW_ID('schema',5834) INTO :NEW.OBJECTID FROM DUAL; END IF; END;
Resulting in:
If you use ArcGIS, the OBJECTID field will be populated by SDE automatically, but if you use Oracle directly, for instance from SQL Developer, just by typing -1 in the OBJECTID column in every new row, these values (-1) will be replaced by valid numbers from the sequence when you commit.
You can just use in the trigger any number you like, -1 is just an example, but choose a negative one.
I haven't tested this however at multiple editing by different users and/or by using Oracle and ArcGIS at the same time.