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
