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