topic Re: Cannot Create SDE View 10.4.1 in Geodatabase Questions

Cannot Create SDE View 10.4.1

MarkMcCart — Mon, 03 Oct 2016 16:23:17 GMT

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

Re: Cannot Create SDE View 10.4.1

ChristianWells — Mon, 03 Oct 2016 17:01:33 GMT

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

Re: Cannot Create SDE View 10.4.1

MarkMcCart — Mon, 03 Oct 2016 17:17:48 GMT

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.

Re: Cannot Create SDE View 10.4.1

MarkMcCart — Mon, 03 Oct 2016 19:00:23 GMT

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?

Re: Cannot Create SDE View 10.4.1

ChristianWells — Mon, 03 Oct 2016 20:07:42 GMT

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.

Re: Cannot Create SDE View 10.4.1

MarkMcCart — Mon, 03 Oct 2016 20:12:36 GMT

Thanks! That worked.