topic Cannot register an Oracle view with SDE in Data Management Questions

Cannot register an Oracle view with SDE

ScottSugden — Fri, 16 Nov 2012 18:36:09 GMT

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.

Re: Cannot register an Oracle view with SDE

VinceAngelo — Fri, 16 Nov 2012 18:40:45 GMT

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

- V

Re: Cannot register an Oracle view with SDE

ScottSugden — Fri, 16 Nov 2012 20:56:50 GMT

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

Re: Cannot register an Oracle view with SDE

CarlosHenrique_Dourado — Mon, 09 Sep 2013 16:50:13 GMT

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?

Re: Cannot register an Oracle view with SDE

VinceAngelo — Mon, 09 Sep 2013 17:12:36 GMT

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

Re: Cannot register an Oracle view with SDE

CarlosHenrique_Dourado — Mon, 09 Sep 2013 19:28:43 GMT

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

Re: Cannot register an Oracle view with SDE

VinceAngelo — Mon, 09 Sep 2013 19:40:18 GMT

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

Re: Cannot register an Oracle view with SDE

CarlosHenrique_Dourado — Mon, 09 Sep 2013 19:46:56 GMT

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

Re: Cannot register an Oracle view with SDE

CarlosHenrique_Dourado — Mon, 09 Sep 2013 20:27:12 GMT

Thanks vangelo

I Got it! Register layer works here.

Re: Cannot register an Oracle view with SDE

CarlosHenrique_Dourado — Thu, 19 Sep 2013 18:01:36 GMT

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

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

Re: Cannot register an Oracle view with SDE

VinceAngelo — Thu, 19 Sep 2013 18:15:03 GMT

Yes and no. Linear features should be registered "nsl+" but generally point features
are only registered "np". You should only specify the "+" on multipoint geometries
(layers for which more than one vertex exists for a point feature -- eg., the centroids
of the Hawaiian islands).

Note that ArcGIS generally registers layers with CAD support ("npc"/"nplsc+"/"nac+").
If CAD objects are permitted on the source layer, CAD should be enabled in the view.

- V