topic Re: Set up an Oracle DBMS spatial table for editing via a feature service in Geodatabase Questions

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

AnthonyRyanEQL — Wed, 21 Jun 2017 10:53:13 GMT

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

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

RexRobichaux2 — Mon, 26 Jun 2017 21:12:57 GMT

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.

Connect to my Oracle 12c database using the sde user in SQLplus
In SQLplus: create table testtab2 (id number(38) not null, data varchar2(10), geom sdo_geometry);
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);
insert into testtab2 values (1,'test',sdo_geometry(2001, 4326, sdo_point_type(10,10,null), null, null));
create index testtab2_indx on testtab2(geom) indextype is mdsys.spatial_index;
commit;
desc testtab2;
CREATE UNIQUE INDEX SDE.TESTTABINDX2 ON SDE.TESTTAB2
(
"ID"
);
ALTER TABLE TESTTAB2 ADD UNIQUE (ID);
COMMENT ON COLUMN "SDE"."TESTTAB2"."ID" IS 'ESRI auto-incrementing';
select object_id from all_objects where owner = 'SDE' and object_name = 'TESTTAB2';
Returned: 102380
CREATE SEQUENCE SDE.SEQ_102380 MINVALUE 1 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 7 NOCACHE NOORDER NOCYCLE ;
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;
/
grant select on seq_102380 to sde;
grant select,insert,update,delete on testtab2 to sde;
I then refreshed my sde user connection to the oracle database in ArcMap, added the new table Share As < Service
Named / attributed the service accordingly < chose Feature Access
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

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

AnthonyRyanEQL — Fri, 30 Jun 2017 06:59:54 GMT

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.

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

AnthonyRyanEQL — Mon, 03 Jul 2017 00:45:05 GMT

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?

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

MichailMarinakis1 — Tue, 08 Jun 2021 13:03:48 GMT

Hi all,

we are using the same steps as in the technical article above. In the oracle setup we have, we must create the table with one specific username, and then connect to the database with a different username. As a result the owner of the table is another user.

Our question is: Is it mandatory to create the table and connect to the database with the same oracle user?

If yes, is there any documentation of what are the privileges and grants that the "creator" account needs to have for creating tables for feature classes?

Thanks!

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

George_Thompson — Tue, 08 Jun 2021 13:44:01 GMT

Here are the privileges needed in an Oracle geodatabase: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/privileges-oracle.htm

You should be able to connect with a different user than the table owner.

If it is an Oracle database (non-geodatabase) then you will not be able to edit via ArcGIS clients and think that it may not be possible via feature services

https://enterprise.arcgis.com/en/server/latest/publish-services/windows/prepare-data-for-feature-services.htm#ESRI_SECTION1_C30D73392D964D51A8B606128A8A6E8F

https://desktop.arcgis.com/en/arcmap/latest/manage-data/databases/databases-and-arcgis.htm#ESRI_SECTION1_A188C2B1C8C54C7DA3233892FC742552

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

MichailMarinakis1 — Tue, 08 Jun 2021 14:40:38 GMT

Hi George,

thanks for the reply! The issue is the following: everything works perfectly if we publish the feature service with a query layer using the schema owner e.g. select id, data from <schema_owner>.<table_name>.

It does not work when we publish exactly the same service but the query is e.g. select id, data from <table_name> without the schema owner (we use synonyms)

Is this an expected behaviour? Do we need to always include the schema owner in the query?

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

George_Thompson — Tue, 08 Jun 2021 15:01:48 GMT

Ok, thanks for the clarification. I could see how this would be needed in case there was another table in a different schema that had the same name.

Update: It seems that the full table name is needed: https://desktop.arcgis.com/en/arcmap/latest/map/working-with-layers/creating-a-query-layer.htm

When building a query, a whole table can be added to the Query text box by either double-clicking it or dragging it from the List of Tables window into the Query text box. Likewise, you can add specific columns in a table to the query by double-clicking them or dragging them from the Columns window into the Query text box. You can also type specific queries or cut and paste a query from an external application into the Query text box.
SQL syntax specific to the database should be used when building a query layer. A common example would be as follows: SELECT * FROM Test.myuser.US_States. This would result in a query layer containing all rows from the US_States table. In the map, this would display all the United States.

I would recommend contacting Esri technical support to have them verify.