Error Querying an SDE view in ArcMap returns error ORA-01445

AgnesPoirier — Mon, 18 Jun 2012 15:27:54 GMT

We are getting an ORA-01445 error message whenever we are doing a query on a view in ArcMap using either
-an SDE view using the "Select By Attributes" tool.(sdetable -o create_view)
-an Oracle view register this through sdelayer -o register ( create view in Oracle and then sdelayer -o register)

Our current configuration is the following:
Oracle 10g Enterprise Edition Release 10.2.0.4.0version 10.2.0.4
ArcGIS Desktop 9.3 sp1
ArcSDE 9.3.1 sp2

The purpose of the view(GEO_BORDER_LINE_V) is to decode coded value of a line layer(GEO_BORDER_LINE) with values available in an oracle table (DL_BORD_GEN_TYP) created with Arctool DomainTo Table

1st Option sdetable -o create_view

sdetable -o create_view -T GEO_BORDER_LINE_V \
-t "IRSDE.GEO_BORDER_LINE,IRSDE.DL_BORD_GEN_TYP" \
-c "IRSDE.GEO_BORDER_LINE.ID,IRSDE.DL_BORD_GEN_TYP.DESCR,IRSDE.GEO_BORDER_LINE.OBJECTID,IRSDE.GEO_BORDER_LINE.GID" \
-a "ID,GEN_TYP,OBJECTID,GID" \
-w "IRSDE.GEO_BORDER_LINE.GEN_TYP_ID=IRSDE.DL_BORD_GEN_TYP.CODE" \
-u -p -i

SQL> desc DL_BORD_GEN_TYP
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OID                                       NOT NULL NUMBER(38)
CODE                                               NUMBER(5)
DESCR                                              NVARCHAR2(255)

SQL> select * from DL_BORD_GEN_TYP;
OID       CODE   DESCR
-- ---------- -----------------------
1          1   ons boundary
2          2   off boundary
3          3   off limit

2nd Option Create view in ORACLE and register

create view GEO_BORDER_LINE_ORA_V
(ID,GEN_TYP,OBJECTID,GID)
AS SELECT IRSDE.GEO_BORDER_LINE.ID,IRSDE.DL_BORD_GEN_TYP.DESCR,IRSDE.GEO_BORDER_LINE.OBJECTID,IRSDE.GEO_BORDER_LINE.GID
FROM IRSDE.GEO_BORDER_LINE,IRSDE.DL_BORD_GEN_TYP
WHERE IRSDE.GEO_BORDER_LINE.GEN_TYP_ID=IRSDE.DL_BORD_GEN_TYP.CODE

sdelayer -o register -l GEO_BORDER_LINE_ORA_V,GID -e nslc+ -t ST_GEOMETRY -C OBJECTID,USER -u -p -i

With both options we're having the same error message while doing select by attributes on GEO_BORDER_LINE_ORA_V oR GEO_BORDER_LINE_V Sepcifying GEN_TYP='ons boundary'

This is the error message in Arcmap

---------------------------
Attributes Selection
---------------------------
There was an error executing the query.
Underlying DBMS error[ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Any thoughts would be appreciated

topic Error Querying an SDE view in ArcMap returns error ORA-01445 in Data Management Questions

Error Querying an SDE view in ArcMap returns error ORA-01445