DBMS table not found (-37)

[AntonGonak]

When I trying to create a view with the following syntax:

sdetable -o create_view -T DPSTR_GIS.CONS_SHP_LAND_V -t DPSTR_GIS.CONS_SHP_LAND,DPSTR_GIS.CONS_LAND -c DPSTR_GIS.CONS_SHP_LAND.OBJECTID,DPSTR_GIS.CONS_SHP_LAND.SHAPE,DPSTR_GIS.CONS_SHP_LAND.NSI_ID,DPSTR_GIS.CONS_LAND.CAD_NUM,DPSTR_GIS.CONS_LAND.ALLOW_USE,DPSTR_GIS.CONS_LAND.LAND_CAT,DPSTR_GIS.CONS_LAND.DEN_AREA,DPSTR_GIS.CONS_LAND.LOCATION_O,DPSTR_GIS.CONS_LAND.TYPE_LAW,DPSTR_GIS.CONS_LAND.TYPE_RIGHT -w "DPSTR_GIS.CONS_SHP_LAND.NSI_ID = DPSTR_GIS.CONS_LAND.NSI_ID" -i 5151 -s arcinfo.sibsac -u dpstr_gis -p dpstr_g

The below generates the following error:

ArcSDE 9.2  for Oracle10g Build 775 Fri Sep 17 10:45:27  2010
Attribute        Administration Utility
-----------------------------------------------------
        Error: DBMS table not found (-37).
Error: Unable to create view DPSTR_GIS.CONS_SHP_LAND_V

But if I convert the script to the query it is executed without error. Example:

Select
DPSTR_GIS.CONS_SHP_LAND.OBJECTID,DPSTR_GIS.CONS_SHP_LAND.SHAPE,DPSTR_GIS.CONS_SHP_LAND.NSI_ID,DPSTR_GIS.CONS_LAND.CAD_NUM,DPSTR_GIS.CONS_LAND.ALLOW_USE,DPSTR_GIS.CONS_LAND.LAND_CAT,DPSTR_GIS.CONS_LAND.DEN_AREA,DPSTR_GIS.CONS_LAND.LOCATION_O,DPSTR_GIS.CONS_LAND.TYPE_LAW,DPSTR_GIS.CONS_LAND.TYPE_RIGHT
from DPSTR_GIS.CONS_SHP_LAND,DPSTR_GIS.CONS_LAND
where DPSTR_GIS.CONS_SHP_LAND.NSI_ID = DPSTR_GIS.CONS_LAND.NSI_ID;

[VinceAngelo]

Try removing the owner from the columns, and especially from the view name
(ArcSDE won't let you create a table as other than yourself) --

sdetable -o create_view ^
-T CONS_SHP_LAND_V -t CONS_SHP_LAND,CONS_LAND ^
-c CONS_SHP_LAND.OBJECTID,CONS_SHP_LAND.SHAPE,^
CONS_SHP_LAND.NSI_ID,CONS_LAND.CAD_NUM,^
CONS_LAND.ALLOW_USE,CONS_LAND.LAND_CAT,^
CONS_LAND.DEN_AREA,CONS_LAND.LOCATION_O,^
CONS_LAND.TYPE_LAW,CONS_LAND.TYPE_RIGHT ^
-w "CONS_SHP_LAND.NSI_ID = CONS_LAND.NSI_ID" ^
-i 5151 -s arcinfo.sibsac -u dpstr_gis -p ******

[AntonGonak]

Hi, I tried to do as you suggest but nothing has changed. Generates the same error. Maybe the problem is in the user privileges?

[AntonGonak]

How can I determine user privileges to create a view?

[VinceAngelo]

You only need CREATE VIEW to create a view, but if it were a privileges issue you'd
get a different error than SE_TABLE_NOEXIST.  What does 'sdetable -o describe'
return on the two tables?  

I'd direct you to Tech Support, but 9.2 is no longer supported.  You should make sure
you're using the terminal service pack (SP6) with all patches.

- V

[SachinKanaujia]

I would suggest you to try testing it out on SDE user first to eliminate access related issues.

[VinceAngelo]

'SDE' should have fewer privileges than a data creator (in general, and with respect to
the 'dpstr_gis' user's tables), so this would expose the instance to potential corruption
without providing any benefit.

- V

[SachinKanaujia]

The suggestion ofcourse is to create everything within SDE (tables and views) for testing and eliminating the possibility of a bug. At times there are patch issues or could be an issue with schema permissions. If SDE can create it then it could be a permission issue where the user does not have required permission.

ESRI should seriously work on their error and exception handling though 🙂 !!!!

'SDE' should have fewer privileges than a data creator (in general, and with respect to
the 'dpstr_gis' user's tables), so this would expose the instance to potential corruption
without providing any benefit.

- V

[JuhoVainio]

I'm having this issue also. It's so much fun trying to guess what is wrong when the error message doesn't help you at all. The most annoying thing is that it would take five seconds from ESRI to simply add the failing table name to the message and it would save me hours of tedious work.

[VinceAngelo]

That "five seconds" estimate supposes that the database has provided this information in the integer error code made available to developers.  I'd put the coding time to make 'sdetable -o create_view' easy to use on the order of 8-10 months (especially considering the potential differences of a dozen different RDBMS providers).

There is a better way to create views:  Use SQL in the RDBMS directly.  Once you've got the view working as desired you can then register it with ArcSDE via 'sdelayer -o register'.  This is best practice, and I strongly recommend it.

- V