I am trying to create a view in our Oracle 12C SDE instance. I've tried a Python script, the Create View Tool in ArcCatalog and CREATE NEW>View by right-clicking in ArcCatalog and I get the following errors:
ORA-00928: missing SELECT keyword
ORA -00942: table or view does not exist. DBMS table not found
We are using ArcGIS 10.4.1 and SDE 10.4.1 on an Oracle 12C instance. Our geometry type is ST_Geometry. The view I am trying to create has point geometry. The script I am using inside the 'CREATE NEW>VIEW' in ArcCatalog is:
CREATE VIEW REALTIME_CRUMB_V55
AS SELECT (OBJECTID, LABEL, LOGDT, SHAPE)
WHERE OBJECTID > 0;
I am logged in as 'schemaowner' when I try to create the view so I know there's not a privilege issue. I've also confirmed that the 'schemaowner' has 'CREATE_VIEW' privilege granted to it. I've also confirmed that our st_shapelib.dll file and path are confirmed correct in Oracle.
Solved! Go to Solution.
When creating views using the Create View tool, only the select statement needs to be passed. The semi-colon is also going to throw an error and needs to be removed.
For example, just try this part:
SELECT (OBJECTID, LABEL, LOGDT, SHAPE)
WHERE OBJECTID > 0
I tried removing the semicolon in the Create View tool like this:
And it threw the following Errors:
ORA-00936: missing expression - DBMS table not found
ORA-04043: object schemaowner.REALTIME_CRUMBS does not exist.
I was able to make the view using Toad, so there must be some syntax issue with the way I am using the 'Create View' tool in catalog. Can anyone give me a working example on how to create a view with that tool?