Set up an Oracle DBMS spatial table for editing via a feature service

[AnthonyRyan3]

Found this Esri support article (http://support.esri.com/technical-article/000012057) posted in May 2016 which goes through the steps to create database table, sequence, trigger that is suitable to create a feature class in Oracle Spatial RDBMS and pubish a feature service to allow editing of data. There is a note at the end of the article stating its for Server/Desktop 10.2.2.

I have been through the steps using 10.3.1 with no success (publishing error 00166 - SEVERITY High - Layer's ObjectID is not maintained by the database and the feature service is being published with "Create" capability enabled, when being analyised)

I was able to create a feature class within the Oracle Spatial RDBMS using the new->feature class right click context menu on the database connection file (.sde) within ArcMap/ArcCatalog and publish a feature service using that feature class with editing being possible when used in a WebMap.

I have the correct permissions on the database for the schema I'm connecting to (eg. create table, index, sequence, trigger, package, procedure, function).

Does anybody know where the documentation/steps are located to correctly create the feature class using SQL DDL statements only to allow a DBA to perform the task without needing any Esri application (eg. ArcMap/ArcCatalog)

Thanks

[RexRobichaux2]

Hi Anthony, 

 I have followed through this article with two different test tables and I was able to successfully publish the table out with feature access enabled, and pull the service back into ArcMap (or web apps) for querying/editing with both tests. This was done at 10.5.1 Desktop and 10.5 ArcGIS Server so admittedly I haven't tested this out at 10.3.x release. However- here are the steps I took on the most recent table create within SQLplus and publishing:

It's worth noting that for testing I used the sde user (gdb admin user in my case within my Oracle 12c database) although I don't think that should matter if the following steps are taken. This database IS geodatabase enabled, however at no point was the table registered with the geodatabase. 

1. Connect to my Oracle 12c database using the sde user in SQLplus
2. In SQLplus: create table testtab2 (id number(38) not null, data varchar2(10), geom sdo_geometry);
3. insert into user_sdo_geom_metadata values ('testtab2', 'geom', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('', -180, 180,0.005), SDO_DIM_ELEMENT('',-90, 90, 0.005)), 4326);
4. insert into testtab2 values (1,'test',sdo_geometry(2001, 4326, sdo_point_type(10,10,null), null, null));
5. create index testtab2_indx on testtab2(geom) indextype is mdsys.spatial_index;
6. commit;
7. desc testtab2;
8. CREATE UNIQUE INDEX SDE.TESTTABINDX2 ON SDE.TESTTAB2
   (
   "ID"
   );
9. ALTER TABLE TESTTAB2 ADD UNIQUE (ID);
10. COMMENT ON COLUMN "SDE"."TESTTAB2"."ID" IS 'ESRI auto-incrementing';
11. select object_id from all_objects where owner = 'SDE' and object_name = 'TESTTAB2';
12. Returned: 102380
13. CREATE SEQUENCE SDE.SEQ_102380 MINVALUE 1 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 7 NOCACHE NOORDER NOCYCLE ;
14. CREATE OR REPLACE TRIGGER SDE.BIFER_102380
    BEFORE INSERT ON SDE.TESTTAB2
    FOR EACH ROW
    DECLARE BEGIN
    IF :NEW.ID IS NULL THEN
    :NEW.ID := SDE.SEQ_102380.NEXTVAL;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN RAISE;
    END;
    /
15. grant select on seq_102380 to sde;
16. grant select,insert,update,delete on testtab2 to sde;
17. I then refreshed my sde user connection to the oracle database in ArcMap, added the new table Share As < Service
18. Named / attributed the service accordingly < chose Feature Access
19. Published the table out / viewed it successfully through server manager / and was able to pull the service down into ArcMap.

Can you try these steps above and see if you are able to publish the table after another try? It might be just a matter of one step not running successfully, a different user used to publish the data, grant permissions, etc. I hope this helps!

Best,

Rex R

[AnthonyRyan3]

Rex,

Thank you for the SQL. I had to alter the trigger relating to getting the next value from the sequence to get it work. It worked the first time I ran the SQL. Made some changes to table name, etc. to minic a new feature class and no luck (getting the same error as above). Currently doing more testing. It is very odd behavour.

[AnthonyRyan3]

I have managed to create the SQL that will consistently allow me to publish a map service with feature access from.

SQL

Create Table TESTTAB1008(
                         OBJECTID INTEGER,
                         SHAPE MDSYS.SDO_GEOMETRY,
                         TEXT_DATATYPE NVARCHAR2(50),
                         Constraint PK_TESTTAB1008_OBJECTID Primary Key(OBJECTID)
                             Using Index (Create Unique Index IDX_TESTTAB1008_OBJECTID On TESTTAB1008(OBJECTID))
                        );

Comment on Column TESTTAB1008.OBJECTID Is 'ESRI auto-incrementing';

Delete From USER_SDO_GEOM_METADATA
Where TABLE_NAME='TESTTAB1008'
  And COLUMN_NAME='SHAPE';

Insert Into USER_SDO_GEOM_METADATA
    Values (
            'TESTTAB1008',
            'SHAPE',
            MDSYS.SDO_DIM_ARRAY(
                                MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .00000005),
                                MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .00000005)
                               ),
            4283);

Create Index IDX_TESTTAB1008_SHAPE On TESTTAB1008(SHAPE)
    INDEXTYPE Is MDSYS.SPATIAL_INDEX Parameters('SDO_COMMIT_INTERVAL = 1000') NOPARALLEL;

Select OBJECT_ID
From ALL_OBJECTS
Where OBJECT_TYPE='TABLE'
  And OWNER='PLANTRATING_MAIN'
  And OBJECT_NAME='TESTTAB1008';

Create Sequence SEQ_9197425  -- OBJECT_ID from above query
MinValue 1
Start With 1
Increment By 1
MaxValue 2147483647
NoCache
Order;

Create Or Replace Trigger BIFER_9197425  -- OBJECT_ID from above query
    Before Insert
    On TESTTAB1008
    For Each Row
   
    Declare
       
    Begin
        -- Assign the primary to the new record --
        Select SEQ_9197425.NextVal Into :new.OBJECTID From DUAL;
       
    End;
Grant Select On TESTTAB1008 To Public;
Commit;

The next problem is it won't allow me to insert data via a webmap created in Portal 10.3.1

It's generating an error without the editor popping up

Console error

Request body

Response body

Results in Oracle database

Our database is Oracle Spatial 10g using Oracle 11g client on my desktop with Oracle 12c client on ArcGIS server

The feature class in Oracle has a SRID set against it (SRID: 4283 - GDA94)

The feature class I created before using the database connection's new->feature class right click context menu uses the same SRID without any issues.

Does this happen with your version?