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