Create a view through the sde command line locally using data from a remote Oracle instance

Summary

Sometimes it is necessary to access data from remote database instances. A view may be created locally using data from a remote table or view.

Instructions provided describe a sample scenario that explains how to create a local view using the sde command line interface with a view created on a remote database instance.

Procedure

1. Create a view on Machine B ('winston'). For this example, name it "CITY_VIEW" and add one field to the view: "NAME".
   
   

   Code:
   create view CITY_VIEW as select NAME from CITIES;

   
   
   

   Note:
   The remainder of the steps are performed on the local machine, Machine A ('charlotte').

   
   
2. Create a new entry in the 'tnsnames.ora' file on Machine A referencing the Oracle instance on Machine B, aliased as 'winston_rem'. This is done to enable the connection that is used by the database link.
   
   

   Note:
   This may already be configured.

   
   
3. Log in to SQL*Plus on Machine A as sysdba and enable the data creator 'GISADMIN' to create a database link.
   
   

   Code:
   grant create database link to GISADMIN; 

   
   
4. Change the connection in SQL*Plus to connect as 'GISADMIN' and create the database link (db_link) from local Oracle instance on Machine A to Machine B.
   
   

   Code:
   create database link dblinkdemo connect to REMOTE identified by remote using 'winston_rem';

   
   
   

   Code:
   Sample syntax: [CREATE DATABASE LINK other_db CONNECT TO <schema> IDENTIFIED BY <password>
                       USING 'tns_alias';]

   
   
5. Following creation of the database link as shown above, run the following query:
   
   

   Code:
   describe REMOTE.CITY_VIEW@dblinkdemo

   
   
   

   Code:
   Sample syntax: schema.view_name@linkname

   
   The view information should be populated on the screen. If this was not set up correctly then the following error message is displayed: "ORA-02019:connection description for remote database not found." If this is the case, check the syntax and the tnsnames.ora file for any errors.
6. Exit SQL*Plus.
7. Within sde command line create a new view, see below syntax used on Machine A 'charlotte' and SID = 'Prod' using the service 'esri_prod' and the data owner and schema 'GISADMIN':
   
   

   Code:
   sdetable -o create_view -T LOCAL_VIEW -t REMOTE.CITY_VIEW@dblinkdemo -c REMOTE.CITY_VIEW.CITY_NAME  -i esri_prod -s charlotte -u GISADMIN -p GISADMIN

   
   This should return the following message: "Successfully created view LOCAL_VIEW." This newly created view should now be visible within ArcCatalog when connecting as the GISADMIN.