7

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?

5
  • 1
    Has the table been registered with ArcSDE? Is there an SDE-set registered rowid?
    – Vince
    Oct 24, 2014 at 10:42
  • Yes, it is because when you open it with ArcCatalog it says SDE table, is this the confirmation? Oct 24, 2014 at 10:56
  • 1
    No, it is not. A table is only registered with ArcSDE if you have used 'sdetable -o register' on it, or have right-clicked "Register with geodatabase". If you are offered the latter, then it hasn't been done previously. ArcGIS isn't going to spontaneously register tables; you must proactively do so.
    – Vince
    Oct 24, 2014 at 11:20
  • Ok, it seems to me that I have to use ArcSDE for handling what I want, but I am using arcgis 10.1, so there is no way of using the arcsde command line, isn't it? Oct 24, 2014 at 12:37
  • 1
    Command-line was deprecated at 10.2, and will be unavailable at 10.3. You could certainly install the application server binaries for this, or just register with geodatabase. BUT, accessing the sequence may not help. If you intend to load these tables outside of ArcSDE's ken, you should not register an SDE-set rowid -- Instead, create and maintain your own sequence.
    – Vince
    Oct 24, 2014 at 12:54

2 Answers 2

11

One way of doing it:

  1. Close ArcGIS and ArcCatalog.
  2. In SQL Developer (Oracle 11g), the sequence of any Oracle table, if registered with the Geodatabase, will be created by SDE under a name similar to this one: 'R5834'
  3. If you click in SQL Developer onto the tab 'Other Users' and then onto the 'SDE' user, you should be able to see the table 'TABLE_REGISTRY'. Open it and in the 'REGISTRATION_ID' column try to find which number is associated with the table you are looking for (i.e. 5834)
  4. Go back to the table and see which is the last objectid value or either way go to the sequence R5834 in Oracle and see which is the last number. You need to remove that sequence, but save the SQL sentence first.
  5. Generate the sequence again by SQL, but this time, change INCREMENT BY 16 to INCREMENT BY 1, and add to START WITH the last number of your current sequence, but adding one extra unit (+ 1).
  6. Grant access to SDE to that sequence: grant select on R5834 to SDE;
  7. Run select DBMS_PIPE.REMOVE_PIPE('ARCSDE_IDPIPE5834') from dual;
  8. 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.

4

Find the registration ID:

SELECT 
    T.REGISTRATION_ID
    , T.TABLE_NAME 
FROM SDE.TABLE_REGISTRY T
WHERE 
    T.TABLE_NAME LIKE '%YOUR_TABLE%'

(let's say the registration ID is 555).

Build this statement:

SELECT SDE.VERSION_USER_DDL.NEXT_ROW_ID('OWNER_NAME', 555) FROM dual

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.