English

How To: 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 := TEST.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.