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

Summary

This article describes how to set up a non-geodatabase table for publishing as a feature service and subsequent editing.

Procedure

To enable editing of feature services that have been created from Oracle non-geodatabase tables, the following steps must be followed, paying particular attention to the sequence name and the trigger name. For these steps, user TEST is being used to created the table.

1. Create the table, testtab in this example, with the id column as number 38 and not null.

SQL> create table testtab (id number(38) not null, data varchar2(10), geom sdo_geometry);

Table created.

2. Insert a record into the sdo_geom_metadata view.

SQL> insert into user_sdo_geom_metadata values ('testtab', 'geom', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('', -180, 180,0.005), SDO_DIM_ELEMENT('',-90, 90, 0.005)), 4326);

1 row created.

3. Insert a record into the table and create the spatial index (a spatial index is not created on empty table)

SQL> insert into testtab values (1,'test',sdo_geometry(2001, 4326, sdo_point_type(10,10,null), null, null));

1 row created.

SQL> create index testtab_indx on testtab(geom) indextype is mdsys.spatial_index;

Index created.

SQL> commit;

Commit complete.

4. Describe the table and confirm that the ID and geometry columns are correct.

SQL> desc testtab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
 DATA                                               VARCHAR2(10)
 GEOM                                               PUBLIC.SDO_GEOMETRY

5. Create a unique index on the ID column.

SQL>  CREATE UNIQUE INDEX TEST.TESTTABINDX ON TEST.TESTTAB  
(  
 "ID"
  );

Index created.

6. Add a unique constraint to the ID column.

SQL> ALTER TABLE TESTTAB ADD UNIQUE (ID);

Table altered.

7. Add a comment on the ID column.

Note:
This is a required step

SQL> COMMENT ON COLUMN "TEST"."TESTTAB"."ID" IS 'ESRI auto-incrementing';

Comment created.

8. Create a sequence and trigger based on the object_id value for the table in all_objects.

SQL> select object_id from all_objects where owner = 'TEST' and object_name = 'TESTTAB';

 OBJECT_ID
----------
     78484

SQL> CREATE SEQUENCE  TEST.SEQ_78484  MINVALUE 1 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 7 NOCACHE  NOORDER  NOCYCLE ;

Sequence created.

SQL> CREATE OR REPLACE TRIGGER TEST.BIFER_78484
     BEFORE INSERT ON TEST.TESTTAB
             FOR EACH ROW
                     DECLARE BEGIN
                     IF :NEW.ID IS NULL THEN
                             :NEW.ID := SEQ_78484.NEXTVAL;
                            END IF;
                    EXCEPTION
                   WHEN OTHERS THEN RAISE;
           END;
           /

Trigger created.

9. Grant select on the sequence to all users who would be publishing the service as this is the user that would be used for feature service editing.

SQL> grant select on seq_78484 to gdb;

Grant succeeded.

SQL> grant select,insert,update,delete on testtab to gdb;

Grant succeeded.