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

【解决方案】
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.

1. 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.  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.

【创建及修改时间】
Created: 5/14/2014

Last Modified: 6/12/2014
【原文链接】
http://support.esri.com/en/kno ... 42548