English

How To: 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

In this scenario, local Machine A has ArcSDE and an Oracle instance, and a view is created using data located on a remote offsite Oracle instance (Machine B).

Configurations:

• Local Server (Machine A)= 'charlotte' running ArcSDE 9.3.1, schema = GISADMIN and Oracle10g

• Remote Server (Machine B)= 'winston' with Oracle 10g, schema = REMOTE

  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.