Querying an SDE view in ArcMap returns ORA-01445 error

[EllenDean]

We are getting an ORA-01445 error message whenever we are doing a query in ArcMap using an SDE view using the "Select By Attributes" tool.  We get this error when selecting on an attribute  which is not either the view join field or the OBJECTID field, and the selection returns more than one record.  Our SDE view is a simple 1:1 Join between an SDE feature class and an Oracle attribute table.  We are using ST_GEOMETRY as our spatial data type.

ESRI Support does not get this same error message with our data when I sent it to them and they created the same SDE view.

I was wondering if anyone might have some thoughts on this.  I have tried the following scenarios and all combinations (Oracle database w/ Client ArcMap) result in this error:

(2) Oracle databases:  version 10.2.0.4 and version 11.2.0.2
- both with ArcSDE 9.3.1 sp2

(2) Client Workstations: 
(i) Oracle client 11.2.0.1 + ArcGIS Desktop 10 sp1;
(ii) Oracle client 10.2.0.3 + ArcGIS Desktop 9.3 sp1;


Any thoughts would be appreciated!

Ellen Dean

[VinceAngelo]

Did you use 'sdetable -o create_view' or SQL to make the view?

Generally speaking, views on ST_GEOMETRY columns should be constructed from SQL
and registered with ArcSDE via 'sdelayer -o register'.

What does your join clause look like?

- V

[EllenDean]

Hi Vince -

Yes, I created this view with the "sdetable -o create_view" command:

sdetable -o create_view -T STN_ET_SPRINGSEEP_TST -t "stn_et_geom,v_mv_springseep_stn" -c "stn_et_geom.objectid, v_mv_springseep_stn.stn_id, v_mv_springseep_stn.stn_stts_cd, stn_et_geom.shape" -w "stn_et_geom.stn_id=v_mv_springseep_stn.stn_id" -i esri_sde -s earth -u gislib -p ****

Both the feature class (gislib.stn_et_geom) and the attribute table (gislib.v_mv_springseep_stn) are unique for their "stn_id" field.

[VinceAngelo]

I'd recommend you use 'sdetable -o delete' on the view, and recreate it with SQL:

CREATE VIEW stn_et_springseep_v AS
SELECT g.objectid, s.stn_id, s.stn_stts_cd, g.shape
FROM stn_et_geom g
INNER JOIN v_mv_springseep_stn s ON stn_id

Do some diagnotic queries, to make sure the table and view row counts are as expected,
then register the view and try querying it through ArcSDE.

- V

[EllenDean]

Thanks for the suggestion - I did this, but then got an error when trying to register this through sdelayer -o register:

[sde@earth ~]$ sdelayer -o register -l STN_ET_SPRINGSEEP_V,shape -e p -t ST_GEOMETRY -C objectid -i esri_sde -s earth -u gislib -p ******

ArcSDE 9.3.1  for Oracle10g Build 3055 Mon May 10 10:12:50  2010
Layer    Administration Utility
-----------------------------------------------------
Error: Not supported on a view (-251).
Error: Unable to alter registration for table STN_ET_SPRINGSEEP_V
[sde@earth ~]$



************************************************
Output from creating the original view:


[sde@earth ~]$ sqlplus gislib@sde

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 8 12:40:04 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

SQL> create view stn_et_springseep_v as select
  2    g.objectid, s.stn_id, s.stn_stts_cd, g.shape
  3  from
  4    stn_et_geom g INNER JOIN v_mv_springseep_stn s ON g.stn_id=s.stn_id;

View created.

SQL> select count(1) from stn_et_geom;

  COUNT(1)
----------
     53652

SQL> select count(distinct stn_id) from stn_et_geom;

COUNT(DISTINCTSTN_ID)
---------------------
                53652

SQL> select count(1) from v_mv_springseep_stn;

  COUNT(1)
----------
       185


SQL> select count(distinct stn_id) from v_mv_springseep_stn;

COUNT(DISTINCTSTN_ID)
---------------------
                  185

SQL> select count(1) from stn_et_springseep_v;

  COUNT(1)
----------
       185

SQL> select count(distinct stn_id) from stn_et_springseep_v;

COUNT(DISTINCTSTN_ID)
---------------------
                  185

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[sde@earth ~]$

[VinceAngelo]

Your SQL*Plus client shows Oracle 10.2.0.1, which is not supported by any
Esri applications.  If you are using Direct Connect, the client should be upgraded
to the server release.

Try registering with a user-set rowid ("-C OBJECTID,USER")

- V

[EllenDean]

Thanks so much Vince.  This worked.

I repeated the steps, this time from a (Windows) client machine with Oracle client 10.2.0.3.  This time, the sdelayer -o register step did work.   Also, this time I used a direct connect to this layer in ArcMap and the query worked correctly.

Thanks again - I really appreciate it!

[VinceAngelo]

The difference on the rowid column parameter is due to the inability to create
a trigger on a view (even if the source column *is* SDE-set).

There's quite a disparity between the rowcounts in your two tables.  Driving from
the geometry table will help performance, but make sure there's an index on the "s"
table's STD_ID column.

Glad you get it working.

- V

[EllenDean]

Yes - our feature class "stn_et_geom" is the one Master GIS table of all stations (wells, springs, water control structure points, water quality monitoring stations, hydrologic stations (e.g. rain gauges), and others) used and managed by our organization.  Different departments manage these stations and their data through specific applications.  We have created many SDE views off of the one stn_et_geom feature class, pertaining to these different station types.  It has been very helpful to us to use SDE views rather than creating (and maintaining) output feature class subsets.

Thanks again -
E.