Technical Articles >  ArcSDE  >  Administration  >  Instructions

Knowledge Base - Technical Articles


Technical Article   HowTo:  Create an Oracle View of an Oracle Spatial layer, containing multiple geometry columns, and register it with ArcSDE

Article ID: 31709
Software:  ArcSDE 9.0, 9.1, 9.2, 9.3, 9.3.1
Platforms:  Windows 2000, 2003Server

Summary

Instructions on how to create an Oracle View of an Oracle Spatial layer, containing multiple geometry columns, and register it with ArcSDE.

Procedure

Create an Oracle View of the layer so that only one MDSYS.SDO_GEOMETRY (spatial) column is specified and then register the view with ArcSDE.

  1. Use SQL to create a view on the Oracle Spatial layer. Include only one MDSYS.SDO_GEOMETRY (spatial) column. 

    CREATE VIEW <view_name> AS SELECT <column1>, <column2>, <spatial_column> FROM <table_with_multiple_geom_cols>;

  2. Insert metadata for the newly created view. 

    INSERT INTO USER_SDO_GEOM_METADATA

    VALUES ('<view_name>','<spatial_column>',

        MDSYS.SDO_DIM_ARRAY( -- upper and lower bounds for the view, tolerance

        MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),

        MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)),

        <SRID> -- SRID from MDSYS.CS_SRS

    );

  3. Register the view with ArcSDE specifying the user-maintained Row ID : 

    sdelayer -o register -l <view_name>,<spatial_column> -C <id_column>,USER -e <allowed_types> -i <service> -u <user> -p <password>


Related Information


Created: 8/31/2006
Last Modified: 5/4/2011

Article Rating: (1)
If you would like to post a comment, please login

Comments

By Anonymous - 10/15/2006 11:10 PM

The article is incorrect or the solution didn’t work.

Hi there, I have followed the instructions in the article but only had partial success. The solution works on very small tables and does not work on large tables. The reason appears to be the fact that the spatial index is not utilised. Regards, Igor

By Anonymous - 09/18/2006 8:11 AM

I followed the article’s instructions, but experienced another problem. I’ve provided details below.

I have a big problem with create_view, when the Oracle table contains a Timestamp column. I want to create a view based on a table. The following statement sdetable -o create_view -T TESTP -t "RM.TEST" -c "TEST_ID,NAME,SHAPE" -a "TEST_ID,NAME,SHAPE" -u SDE -p *** works fine, if the table RM.TEST has columns with "normal" data types. If I add a column with datatype TIMESTAMP to the table and fire the same statement, I get an "Attribute conversion error (-55)": ArcSDE 9.1 Oracle10g Bu

By Anonymous - 09/18/2006 5:27 AM

The article has missing, misleading, or conflicting information.

It seems that it's necessary to create and register a view via "sdetable -o create_view". I have not managed to register an already existing view (created with Oracle tools) with "sdelayer -o register", although this article looks like it is possible. There should be a link to "sdetable -o create_view" to make this article more helpful. Holger

Rating:

By Anonymous - 09/18/2006 1:22 AM

I followed the article’s instructions, but experienced another problem. I’ve provided details below.

In addition to my first feedback to this article, posted 20 minutes ago: The first error Error: Underlying DBMS error (-51). Error: Cannot Create Layer. ORA-01720: grant option does not exist for 'RM.TEST' was solved by granting SELECT rights for table RM.TEST to user SDE, but "WITH GRANT OPTION". According to trace logs for the sdelayer -o register command, it tried to grant Select privileges for the new view to user SDE, which is unnecessary, because the view already belongs to user

By Anonymous - 09/18/2006 12:42 AM

I followed the article’s instructions, but experienced another problem. I’ve provided details below.

using ArcSDE 9.1 SP2 and Oracle 10gR2 (geometry as SDO_GEOMETRY type): I want to register a layer based on a view (CENTERP in SDE schema), which refers to a table (TEST) in another schema (RM). privileges are granted. step 1 and 2 are ok, step 3 gives me errors: step 1: CREATE VIEW CENTERP AS SELECT OBJID, NAME_T, SHAPE FROM RM.TEST; and step 2: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CENTERP','SHAPE', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.0000005), MDSYS.SDO