ORA-22901 when attempting 'select distinct' in Oracle spatial view

[ChrisBeaudette]

I'm using Oracle v10.2.0.4, and I have spatial view that works fine as long as I don't define 'select distinct' in the view sql. 

My view was created first by creating a simple view with a subset of columns from just my spatial layer:

sdetable -o create_view -T VW_ERA_POINTS -t GIS_FP_POINTS 
-c "OBJECTID,PK,NAME,TYPECODE,LAT,LON,SHAPE" 

I then modified the view SQL as follows:

CREATE OR REPLACE FORCE VIEW "SDE"."VW_ERA_POINTS" ("OBJECTID", "PK", "NAME", "APP_USER_SEQ", "TYPECODE", "LAT", "LON", "SHAPE")
AS
SELECT 
  "GFP"."OBJECTID",
  "GFP"."PK", 
  "GFP"."NAME",
  "EAU"."USER_SEQ" as "APP_USER_SEQ", 
  "GFP"."TYPECODE", 
  "GFP"."LAT", 
  "GFP"."LON", 
  "GFP"."SHAPE"
FROM 
  SDE.APP_USER_ERA_ENV_INT_GROUP EAU, 
  SDE.GIS_FP_POINTS GFP, 
  SDE.ENV_INT FEI, 
  SDE.ENV_INT_TYPE EIT,
  SDE.ENV_INT_GROUP EIG
WHERE
  EAU.ERA_ENV_INT_GROUP_SEQ = EIG.ERA_ENV_INT_GROUP_SEQ
  AND EIG.ERA_ENV_INT_GROUP_SEQ = EIT.ERA_ENV_INT_GROUP_SEQ
  AND EIT.ENV_INT_TYPE_SEQ = FEI.ENV_INT_TYPE_SEQ
  AND GFP.PK = FEI.FACILITY_SEQ
  AND GFP.ISFAC = 'Y';

This works fine and I can view the view in ArcMap, etc. but returns duplicate rows because of the table relationships.  If I change 'SELECT ...' to 'SELECT DISTINCT...', it reports the following error:

ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 -  "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause:    Comparison of nested table or VARRAY or LOB attributes of an
           object type was attempted in the absence of a MAP or ORDER
           method.
*Action:   define a MAP or ORDER method for the object type.

And in fact it does the same when I run the SELECT outside of the view, i.e w/o the 'CREATE OR REPLACE FORCE VIEW...'.


From what I've read on this KB article, it looks like it may be having a problem with the SHAPE column, but if I change '"GFP"."SHAPE" to 'TO_CHAR("GFP"."SHAPE")' per that article, then it throws a different error:

ORA-00932: inconsistent datatypes: expected NUMBER got SDE.ST_GEOMETRY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

My table describes, etc. are as follows (can't do 'describe long' since it exceeds 10,000 char limit of post):

>sdetable -o describe -t GIS_FP_POINTS  -- my spatial layer w/ SHAPE


ArcSDE 10.0  for Oracle10g Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------

Table GIS_FP_POINTS:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10        SDE Set
PK                      SE_NSTRING       NOT NULL      254
NAME                    SE_NSTRING       NULL          254
PRGM                    SE_NSTRING       NULL          254
PGRMPK                  SE_NSTRING       NULL          254
LAT                     SE_FLOAT64       NULL           38,8
LON                     SE_FLOAT64       NULL           38,8
COL                     SE_NSTRING       NULL          254
TYPECODE                SE_NSTRING       NULL          254
ISFAC                   SE_NSTRING       NULL          254
COORD_ID                SE_NSTRING       NULL          254
SHAPE                   SE_SHAPE         NULL            0


>sdetable -o describe -t APP_USER_ERA_ENV_INT_GROUP 

ArcSDE 10.0  for Oracle10g Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------

Table APP_USER_ERA_ENV_INT_GROUP:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
APP_USER_ENV_INT_GROUP_SEQ SE_INT32         NOT NULL       10
USER_SEQ                SE_INT32         NOT NULL       10
ERA_ENV_INT_GROUP_SEQ   SE_INT32         NOT NULL       10
OBJECTID                SE_INT32         NOT NULL       10        SDE Set


>sdetable -o describe -t ENV_INT 

ArcSDE 10.0  for Oracle10g Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------

Table ENV_INT:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
ENV_INT_SEQ             SE_INT32         NOT NULL       10
FACILITY_SEQ            SE_INT32         NOT NULL       10
ENV_INT_TYPE_SEQ        SE_INT32         NOT NULL       10
ENV_INT_FACILITY_PK_ID  SE_STRING        NOT NULL       21
ENV_INT_START_DATE      SE_DATE          NULL            0
ENV_INT_END_DATE        SE_DATE          NULL            0
ENV_INT_NOTES           SE_STRING        NULL         2000
ENV_INT_LAST_INSPEC_DATE SE_DATE          NULL            0
LAST_MERGED_DATE        SE_DATE          NULL            0
OBJECTID                SE_INT32         NOT NULL       10        SDE Set


>sdetable -o describe -t ENV_INT_TYPE 

ArcSDE 10.0  for Oracle10g Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------

Table ENV_INT_TYPE:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
ENV_INT_TYPE_SEQ        SE_INT32         NOT NULL       10
ENV_INT_TYPE_CODE       SE_STRING        NOT NULL       10
ENV_INT_TYPE_DESC       SE_STRING        NULL          100
ENV_INT_TYPE_DESC_LONG  SE_STRING        NULL         2500
DIVISION_SEQ            SE_INT32         NULL           10
START_DATE_DESC         SE_STRING        NULL           50
END_DATE_DESC           SE_STRING        NULL           50
START_DATE_DESC_LONG    SE_STRING        NULL         1000
END_DATE_DESC_LONG      SE_STRING        NULL         1000
PRIMARY_APP_USER_SEQ    SE_INT32         NULL           10
ORIGINATING_SYSTEM_SEQ  SE_INT32         NULL           10
ENV_INT_START_PAGE_URL  SE_STRING        NULL          200
ENV_INT_FACILITY_PAGE_URL SE_STRING        NULL         1000
ALWAYS_AUTO_DELETE      SE_INT16         NULL            1
EPA_EI_TYPE_DESC        SE_STRING        NULL          100
IS_EXTERNAL_DATA        SE_STRING        NULL            1
HIDE_GEOCODES           SE_STRING        NULL            1
HIDE_LEGAL_COORDS       SE_STRING        NULL            1
ERA_ENV_INT_GROUP_SEQ   SE_INT32         NULL           10
OBJECTID                SE_INT32         NOT NULL       10        SDE Set


>sdetable -o describe -t ERA_ENV_INT_GROUP 

ArcSDE 10.0  for Oracle10g Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------

Table ERA_ENV_INT_GROUP:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
ERA_ENV_INT_GROUP_SEQ   SE_INT32         NOT NULL       10
ERA_ENV_INT_GROUP_CODE  SE_STRING        NOT NULL       10
ERA_ENV_INT_GROUP_DESC  SE_STRING        NOT NULL      100
ERA_ENV_INT_GROUP_LONG_DESC SE_STRING        NOT NULL     2500
GROUP_WEB_URL           SE_STRING        NULL          200
OBJECTID                SE_INT32         NOT NULL       10        SDE Set

Any help would be greatly appreciated.

[VinceAngelo]

This is more of an Oracle restriction than an ArcSDE issue -- A DISTINCT query on indistinct
features (object type columns) cannot succeed. I expect that SDO_GEOMETRY would have the
same issue. If you eliminate the geometry column by turning it into text, you'll corrupt the
geometry result registered with ArcSDE. You might have better luck with GROUP BY, but then
you'd need to do an expensive geometric UNION with every query. If you can fashion a DISTINCT
query without the geometry, you might be able to join the geometry later (either through inline
views or a view on a view).

I note that you're using the SDE user to own and manage spatial tables (or at least the resulting
view). The SDE user and SDE tablespace should really be reserved for ArcSDE configuration and
management, much the way the SYSTEM user and SYSAUX tablespace are reserved for the
database. It is best practice to avoid use of the SDE user for anything except ArcSDE management.

The use of 'sdetable -o create_view' on ST_GEOMETRY tables is also outside best practice.
'create_view' was designed for SDEBINARY/SDELOB storage -- the best way to construct
spatial views on ST_GEOMETRY layers is through SQL with CREATE VIEW, and to register
the results with 'sdelayer -o register' (which will also verify the uniqueness of the query
[by NUMBER(38) registered rowid column] at the time of registration).

- V

[ChrisBeaudette]

Cheers Vince.

Got it -- will use inline view most likely.

Also best practices are duly noted.  Didn't realize that 'sdetable -o create_view' on ST_GEOMETRY tables is not a best practice.

Thanks again for your help.

[SusanMcclendon]

Vince,

We have been using Feature Class Views with ST_GEOMETRY for a while now (sdetable -o create_view and modify view in SQL Developer to join attributes from non-sde registered tables).  We started using them working with an ESRI Tech under contract about two years ago.  Not once did he say this was no longer best practice with ST_GEOMETRY or mention using the alternative of sdelayer -o register with views created directly in Oracle.  I have a bunch of them in production. 

Can you please elaborate on WHY this is no longer best practice?  Is that statement specific to ArcGIS 9.x or 10?  Why the difference with SDE_GEOMETRY and ST_GEOMETRY?  Is there ANY tech document to outline the process with sdelayer -o register in an SDE environment with ST_GEOMETRY?  I've found one, http://support.esri.com/en/knowledgebase/techarticles/detail/31709, but it applies to ArcGIS 9.3, Oracle Spatial and not SDE specific (although I do understand the vast similarities between the geometry type in Oracle Spatial and SDE ST_GEOMETRY).  I would think if this is a best practice change ESRI would shout it to the roof-tops as Feature Class Views are a very popular way of joining spatial and non-spatial data.

Are there performance issues with Feature Class Views vs registering the Oracle view?  At last year's ESRI conference staff in the last session I attended alluded to this but did not elaborate...and I had to catch a plane.

Finally, optional bonus question round - I tried the sdelayer -o register method and got an error that the attribute table I was joining to did not have "GRANT" option.  My DBA does not like to give the GIS account GRANT option with SELECT on the attribute non-sde registered tables.  Why does SDE require that?  Why is not SELECT enough?

Thanks a bunch for the clarification!

Susan
WYDOT GISITS developer

This is more of an Oracle restriction than an ArcSDE issue -- A DISTINCT query on indistinct
features (object type columns) cannot succeed. I expect that SDO_GEOMETRY would have the
same issue. If you eliminate the geometry column by turning it into text, you'll corrupt the
geometry result registered with ArcSDE. You might have better luck with GROUP BY, but then
you'd need to do an expensive geometric UNION with every query. If you can fashion a DISTINCT
query without the geometry, you might be able to join the geometry later (either through inline
views or a view on a view).

I note that you're using the SDE user to own and manage spatial tables (or at least the resulting
view). The SDE user and SDE tablespace should really be reserved for ArcSDE configuration and
management, much the way the SYSTEM user and SYSAUX tablespace are reserved for the
database. It is best practice to avoid use of the SDE user for anything except ArcSDE management.

The use of 'sdetable -o create_view' on ST_GEOMETRY tables is also outside best practice.
'create_view' was designed for SDEBINARY/SDELOB storage -- the best way to construct
spatial views on ST_GEOMETRY layers is through SQL with CREATE VIEW, and to register
the results with 'sdelayer -o register' (which will also verify the uniqueness of the query
[by NUMBER(38) registered rowid column] at the time of registration).

- V

[VinceAngelo]

It's never been best practice (or even supported) to edit views outside of ArcSDE's ken.
It was, however, the only way to solve some situations before the advent of native
datatypes like ST_GEOMETRY.

The 'sdetable -o create_view' command was created for the purpose of managing the
F and S views associated with SDEBINARY and SDELOB storage layers.  Given the choice
of a supported 'sdelayer -o register' after creating the exact view you need and the
unsupported editing of a view which lacked the ability to use most of the capabilities of
SQL, I would think the former would be more popular.  In fact, SQL administration is one
of the most compelling arguments for using ST_GEOMETRY (it's not the *easiest* way
to administer an instance, which is probably why it's not emphasized).

I've never heard of performance issues associated with view implementation -- If the
optimizer is going to choose unwisely, it will do so for what seems to be valid reasons,
and it will do so for both types of creation equally.  If anything, the ability to include
hints in custom view creation is another strong vote for SQL view management.

Permission errors are exclusively in the domain of RDBMS implementation.  Whenever
user A tries to create a view with user B's table, they must have GRANT OPTION
access to the source table to grant access to other users.  This is required by the
SQL language.  I can't imagine what harm could come from granting SELECT WITH
GRANT OPTION to a data ownership account with restricted use and a closely held
password (another best practice).
 
- V