Cannot Create SDE View 10.4.1

817
5
Jump to solution
10-03-2016 09:23 AM
Highlighted
Occasional Contributor

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)
FROM schemaowner.TRUCK_HISTORY_3HR
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.

Any suggestions?

Thanks,
Mark

Tags (3)
Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Esri Contributor

This simple select works for me:

EDIT: This is from the New > View context menu. However, the GP tools works with the same inputs for me. 

View solution in original post

5 Replies
Highlighted
Esri Contributor

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)
FROM schemaowner.TRUCK_HISTORY_3HR
WHERE OBJECTID > 0

Highlighted
Occasional Contributor

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.

Reply
0 Kudos
Highlighted
Occasional Contributor

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?

Reply
0 Kudos
Highlighted
Esri Contributor

This simple select works for me:

EDIT: This is from the New > View context menu. However, the GP tools works with the same inputs for me. 

View solution in original post

Highlighted
Occasional Contributor

Thanks! That worked.

Reply
0 Kudos