Example: Creating spatial views in Oracle using the sdetable command

Complexity: Beginner Data Requirement: Use your own data

You can create spatial views using the sdetable command by including the spatial column in the list of columns specified with the –c option. When you create a spatial view using the sdetable command, it is registered as a table in ArcSDE. To register the view as a layer, use the sdelayer command.

In this example, a spatial view that incorporates columns from a feature class and nonspatial table is created using the sdetable command and privileges are granted on it. The view is then registered as a layer with ArcSDE.

Create a spatial view

In the following example, the table, employees, is joined with the feature class, regions. The tables are joined on emp_id, which exists in both tables.

sdetable -o create_view -T emp_region_view -t "employees,world.regions" 
-c "employees.name,employees.emp_id,world.regions.reg_id,world.regions.rname,world.regions.region"
-a "employee,eid,rid,region,area" -w "employees.emp_id = world.regions.emp_id"
-s myodbserver -i sde:oracle11g:ora1 -u gdb -p gdb.bdg

If the tables are not owned by the connected user, the name must be in owner.table format. When using multiple tables, you must qualify the names of each column listed after –c with the name of the table using the format table.column or owner.table.column.

To use tables that are in a geodatabase in a user's schema, specify the user-owned geodatabase with the –D option.

Since columns from two different tables are included in the view in this example, making the table names quite long, the –a option is used to assign aliases to the columns. However, aliasing is only required if the column names included in the view are ambiguous. For example, if you create a view between two tables and include the name column from both, you should alias the columns, giving them distinct names. If you use the –a option to alias the column names, the columns and the order they are listed must be the same in the –c and –a lists.

The reg_id is the not null, integer column from the regions feature class that is used as the row ID in ArcSDE. Region is the spatial column from the regions feature class.

Tip:

The gdb user must have at least select privileges on the regions feature class to include it in the emp_region_view definition.

The view is now registered with ArcSDE as a table (there is an entry for it in the TABLE_REGISTRY system table). The view will appear as a table when viewed through ArcGIS Desktop.

Grant privileges on the view

You can use SQL or the sdetable command to grant privileges on a view.

In Example: Creating a spatial view in Oracle using SQL and registering it, SQL is used to grant privileges on the view. In this example, the sdetable command with the grant operation is used.

sdetable -o grant -t emp_region_view -U dispatch1 -A SELECT
-s myodbserver -i sde:oracle11g:ora1 -u gdb -p gdb.bdg -I

The –I option is included to allow the user, dispatch1, to grant privileges on the view to other users.

Register the view as a layer

When the view was created with the sdetable command, it was registered with ArcSDE as a table. To see the view as a layer, you must register it using the sdelayer command.

sdelayer -o register -l emp_region_view,region -e a 
-C reg_id,USER -t ST_GEOMETRY -E 2.20,2.20,3.01,3.01 -R 1 
-s myodbserver -i sde:oracle11g:ora1 -u gdb -p gdb.bdg

The values you provide for the options, except –o, will vary depending on your data.

For more information on the sdelayer and sdetable commands, see the Administration Command Reference.