Cannot register an Oracle view with SDE

[ScottSugden]

Hi,

Environment: Windows Server 2008 R2 Standard, Oracle 11g, ArcGisServer 10.1 SP1
Issue: Cannot register an Oracle view with SDE

If I create an Oracle view using an existing/working feature class called Districts using the following syntax:
CREATE OR REPLACE VIEW DISTRICTS2 As
SELECT "TAG","OBJECTID","SHAPE"
FROM Districts
Where ObjectID = 1;

Spatial column is 'Shape'. ID column is 'ObjectId'.

If I then try to register that view with the geodatabase using the following:
sdelayer -o register -l DISTRICTS2,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -i sde:oracle11g:orcl10 -u xxx -p xxx

(-u xxx -p xxx are dummy values)

I get:
Wrong column type (-144)
SE_table_get_shape_type failed.

I have checked the shape column type on the base table DISTRICTS using:
sdelayer -o describe_long -l DISTRICTS,SHAPE -i sde:oracle11g:orcl10 -u xxx -p xxx

And I get:
Spatial Column = SHAPE
Layer Type = ST_GEOMETRY
Entities = nac+

What I am doing wrong?

I cannot use catalog to register the view as the 'Register with geodatabase' option under 'Manage' is greyed out.

I can 'manually' register the view by manually inserting records into the sde tables LAYERS, GEOMETRY_COLUMNS and TABLE_REGISTRY. Obviously this is not the correct way to go!

Thanks in advance.

[VinceAngelo]

I found that if I specified the SRID (-R nn) I was able to get the layer to register.

- V

[ScottSugden]

Perfect! Works like a charm. Many thanks for the help.

[CarlosHenrique_Dourado]

Hi!
I'm having the same problem but with a different error code

I tried so:

sdelayer -o register -l BAIRROS,SHAPE -e a -C OBJECTID -t SDO_GEOMETRY -P BASIC -u xxx -p xxx

I get:

Error: Wrong column type (-114).
Error: SE_table_get_shape_type failed


Anybody?

[VinceAngelo]

Please post all relevant information about your environment:
EXACT Oracle release (A.B.C.D.E notation)
EXACT ArcGIS release (version & SP)
EXACT ArcSDE release (version & SP)
The contents of USER_SDO_GEOM_METADATA
DESCRIBE output on the view
'sdetable -o describe' output on the view

Note that "-P BASIC" is no longer a valid  option -- As of 10.1, all native geometries
are always registered with HIGH precision.  You should always register polygons as
multi-part ("a+").  You should always specify coordinate reference parameters
when you register a table (-R or -G/-x).

- V

[CarlosHenrique_Dourado]

Enviroment:
Windows 2008 Server Enterprise 64 bits
oracle 11.2.0.3
arcgis desktop 10.1
arcsde 10.1 for oracle 11g


SQL> select table_name, column_name, srid  from user_sdo_GEOM_metadata where table_name='BAIRROS';

TABLE_NAME COLUMN_NAME                          SRID
---------- ------------------------------ ----------
BAIRROS    SHAPE                               29191


SQL> select  index_name, table_name, column_name  from user_sdo_index_info where table_name='BAIRROS';

INDEX_NAME             TABLE_NAME      COLUMN_NAME
-------------------  ------------  ------------------
IDXR__BAIBAIR1         BAIRROS          SHAPE



>sdelayer -o register -l BAIRROS,SHAPE -e a -C OBJECTID -t SDO_GEOMETRY  -u xxx -p xxx


ArcSDE 10.1  for Oracle11g Build 679 Thu Apr  5 11:43:21  2012
Layer    Administration Utility
-----------------------------------------------------
Error: Wrong column type (-114).
Error: SE_table_get_shape_type failed

Note that same without  '-P BASIC' returns the same error

[VinceAngelo]

Please post all relevant information about your environment:
DESCRIBE output on the view
'sdetable -o describe' output on the view

You should always register polygons as multi-part ("a+").  You should always specify
coordinate reference parameters when you register a table (-R or -G/-x).

- V

[CarlosHenrique_Dourado]

sdetable -o describe output

Table BAIRROS:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
ID_BAIRRO               SE_FLOAT64       NULL           38,8
DATA                    SE_DATE          NULL            0
SHAPE                   SE_SHAPE         NULL         2000
COD_BAIRRO              SE_STRING        NULL           10
OBJECTID                SE_INT32         NOT NULL       10
NOMES_BAIRROS           SE_STRING        NULL          200
ESCALA                  SE_STRING        NULL           30
PROJETO                 SE_STRING        NULL          100
FONTE                   SE_STRING        NULL          200
ORIGEM                  SE_STRING        NULL          200
FASE                    SE_STRING        NULL          100
CONTROLE_1              SE_STRING        NULL          100
CONTROLE_2              SE_STRING        NULL          100
CONTROLE_3              SE_STRING        NULL          100

[CarlosHenrique_Dourado]

Thanks vangelo

I Got it! Register layer works here.

[CarlosHenrique_Dourado]

You should always register polygons as multi-part ("a+"). 
- V

Multi-part is valid for others geometry types as point and line?