How To: Create a view through the sde command line locally using data from a remote Oracle instance
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.
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).
• Local Server (Machine A)= 'charlotte' running ArcSDE 9.3.1, schema = GISADMIN and Oracle10g
• Remote Server (Machine B)= 'winston' with Oracle 10g, schema = REMOTE
- Create a view on Machine B ('winston'). For this example, name it "CITY_VIEW" and add one field to the view: "NAME".
create view CITY_VIEW as select NAME from CITIES;
The remainder of the steps are performed on the local machine, Machine A ('charlotte').
- 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.
This may already be configured.
- Log in to SQL*Plus on Machine A as sysdba and enable the data creator 'GISADMIN' to create a database link.
grant create database link to GISADMIN;
- 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.
create database link dblinkdemo connect to REMOTE identified by remote using 'winston_rem';
Sample syntax: [CREATE DATABASE LINK other_db CONNECT TO <schema> IDENTIFIED BY <password>
- Following creation of the database link as shown above, run the following query:
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.
- Exit SQL*Plus.
- 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':
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.