ARCGIS SDE Spatial Processing

In Oracle, SQL functions for St_geometry and St_raster use shared libraries that are accessed through Oracle's external process agent (that is, Extproc). To use SQL and St_geometry or st_raster with or access ArcSDE XML columns in GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW views, Oracle must have access to these libraries. Therefore, these libraries must exist on the Oracle server and must be called through the external process framework of Oracle.

In ArcGIS, you can also use St_geometry and st_raster without setting up an Oracle listener. However, you will not have the full functionality of the ArcGIS client and will not have any features of the SQL client (such as Sql*plus). For example, if you do not configure an Oracle listener, you cannot use SQL functions on St_geometry columns in the query layer in ARCMAP or versioned tables that contain st_raster columns, you cannot delete rows that include st_raster columns, and you cannot execute SQL from SQL clients Function. Also, if you do not configure an Oracle listener, you cannot publish data that uses the St_geometry type as a feature service to a spatial data server.

St_raster installation as optional operation. For instructions, see Installing the St_raster type in Oracle.

Note:

If your Oracle database is installed on a Windows server, but you do not have an ArcSDE application server installed, you may need to install the Microsoft Visual C + + SP1 Redistributable Package (x64). If you do not have a Microsoft Visual C + + SP1 Redistributable Package (x64) on your Oracle server, you can download it from the Microsoft Web site and then install it.

If you are using Oracle 11g, edit the Extproc.ora file to invoke the function through Oracle's external procedure framework. If you are using Oracle 10g, configure the Oracle listener.

First create the St_shapelib library

Execute SQL query User_libraries
SELECT * from User_libraries;

CreateSt_shapelib LibraryCreate or replace library st_shapelib as ' C:\arcgisdll\st_shapelib.dll '; settings take effectAlter package sde.st_geometry_shapelib_pkg compile reuse settings;

Oracle 11g

For Oracle 11g,oracle It is recommended that you configure Extproc by changing the Extproc.ora file instead of configuring the listener. The Extproc.ora file is located in the Oracle_home\hs\admin directory.

After you move the St_geometry (st_shapelib) and/or St_raster libraries to your Oracle server, change the Extproc.ora file to point to the location of the library.

On a Windows server, you can add a line similar to the following:

Extproc_dlls=only:c:\\mylibraries\\st_shapelib.dll

Extproc_dlls=only:c:\\mylibraries\\libst_raster_ora.dll

If you use two libraries, you can put them on one line:

Extproc_dlls=only:c:\\mylibraries\\st_shapelib.dll; C:\\mylibraries\\libst_raster_ora.dll

In these examples, the library is placed in a folder named Mylibraries, which is created on the Oracle server to store the library.

On a Linux or UNIX server:

SET extproc_dlls=only:/user/esrilibs/libst_shapelib.so

SET extproc_dlls=only:/user/esrilibs/libst_raster_ora.so

If you use two libraries, you can put them on one line:

SET extproc_dlls=only:/user/esrilibs/libst_shapelib.so:/user/esrilibs/libst_raster_ora.so

In these examples, the library is placed in the user's Esrilibs directory, which is created on the Oracle server to store the repository.

Oracle 10g

Oracle listeners can be configured in a variety of configurations. For example, there may be multiple listeners associated with a database, and each listener can manage multiple types of service requests. This is a very complex topic, and this document does not cover many of these changes. It is important to see the Oracle Database Network Service Administrator's Guide for more information about configuring the listener.

One of the most important aspects of configuring the listener is to tell Extproc where to find the shared library. You need to modify the listener configuration to specify the location of the shared library and restart the Oracle listener process so that the configuration changes take effect.

This document covers two standard Oracle listener profiles: Tnsnames.ora and Listener.ora. These two files are usually located in Oracle_home/net/admin. This document describes the configuration settings that are required.

There are several ways to manage settings. Use a text editor to edit text files, using Oracle Net Manager, or using the Oracle Net Configuration Assistant. Oracle provides documentation on how to configure the listener. For more information, see the Oracle Database Network Service Administrator's Guide.

The Tnsnames.ora file contains a directory of known database services. This file defines the service on the local database or on the remote server. There is an entry for the local database server to send function calls to Extproc using interprocess communication (IPC). This entry is labeled Extproc_connection_data. You can change the Key and SID values under this entry.

These items are used to link the entry to the corresponding information in the Listener.ora file. The name of the key can be any abbreviated name, but it must be the same name in the Listener.ora file and the Tnsnames.ora file. The values are case-sensitive. Only the listener process can use these values, and the user or application is not available.

The Listener.ora file describes some (not necessarily all) services that the listener listens to for requests.

The values that can be changed in this file include:

• Sid_list_listener

  This callout is the beginning of the list of SIDS to be processed by the listener named LISTENER (the default listener name).

• Sid_list and Sid_desc

  The Sid_desc entry under heading sid_list is used to define the service. The first entry in the list handles the EXTPROC request, and the second entry handles the client session.

• Sid_name

  Its value must correspond to the SID specified in the file Tnsnames.ora for Extproc.

• Oracle_home

  Defines the location of the Oracle home page for this service. Loads the Extproc program file from a folder under this location.

• Program

  Specifies the file name of the Extproc executable file. The case-sensitive name may be Extproc or extproc.exe, depending on the type of operating system. The file is located in Oracle_home/bin.

• Envs

  This is the list of environment variables that are used by Extproc at run time. The list is separated by a colon. This list must include the definition of the environment variable extproc_dlls and any other environment variables that EXTPROC requires at run time, and other environment variables typically include Ld_library_path, Shlib_path, or LIBPATH in UNIX and Linux systems. Or PATH on a Windows server. Path variables typically include the location of the geometry and projection engine libraries.

• Extproc_dlls

  This environment variable is used to define a list of libraries that EXTPROC can directly load and invoke functions from. You must specify here the path to the file that contains the following libraries: Shape libraries, which are used to access st_geometry types or GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW views through SQL, and St_raster libraries for accessing st_ through SQL The Raster type.

  For Oracle servers installed on UNIX, the list is separated by colons, and the list is semicolon-delimited for Oracle servers installed on Windows. All paths must be absolute and point to a local directory, or a UNC path that is accessible to Oracle system users. This also means that if you have an ArcSDE application server installed on a non-Oracle server, you must copy the shapes and raster libraries from the bin directory in the SDEHOME to the Oracle server.

  No environment variable substitution exists.

  Optional keywords any and only can be used to relax or limit the way Extproc uses library files. If you put any in front of the path, Oracle can load any library from the specified path and do not have to provide the library name. If only is placed in front of the path, Oracle will use only the specific libraries provided in the path.

• KEY

  The value set for this entry links the listener to the corresponding service entry in the file Tnsnames.ora. This value separates this listener from other IPC listeners that may exist on the same database server. Key can be abbreviated for any name, but must be the same in file Listener.ora and Tnsnames.ora. Key is case-sensitive.

Steps:

2. Before you make any changes, back up the Tnsnames.ora and Listener.ora files.
4. Open the Tnsnames.ora file on the Oracle server.
6. Change the Key and SID values under Extproc_connection_data in the Tnsnames.ora file.

   Extproc_connection_data = (DESCRIPTION = (    address_list = (      ADDRESS = (PROTOCOL = IPC) (Key = EXTPROC1))    (Connect_data =      (SID = plsextproc)      (PRESENTATION = RO))    )

   This entry must always have a callout extproc_connection_data. This entry is used by the database server to send function calls to the EXTPROC through interprocess communication.

8. Open the Listener.ora file on the Oracle server.
9. Change the required entry to set the server on which the listener will accept the request.

   The first example is for Windows operating systems that have both an ArcSDE application server and Oracle installed.

   LISTENER = (Description_list = (    DESCRIPTION = (      address = (PROTOCOL = IPC) (KEY = EXTPROC1)) (address = (PROTOCOL = TC P) (HOST = svr1.dmn1.com) (PORT = 1521)))    Sid_list_listener =  (sid_list = (Sid_desc = (      sid_name = Plsextproc) (      oracle_home = C:\oracle\product\10.2.0\db_1) (Program      = extproc)         (envs= "Extproc_dlls=c:\ Arcsde\sdeexe\bin\st_shapelib.dll;c:\arcsde\sdeexe\bin\libst_raster_ora.dll ")))  

   This example shows an entry to install a Linux or UNIX server with an ArcSDE application server and Oracle:

   LISTENER = (Description_list = (    DESCRIPTION = (      address = (PROTOCOL = IPC) (KEY = EXTPROC1)) (address = (PROTOCOL = TCP) (HOST = svr1.dmn1.com) (PORT = 1521))))    Sid_list_listener =  (sid_list = (Sid_desc = (      sid_name = Plsextproc) (Oracle_home =/servit/oracle/product/10.2.0/db_1) (Program      = extproc)         (envs= "extproc_dlls=/ Servit/arcsde/sdeexe/lib/libst_shapelib.so:/servit/arcsde/sdeexe/lib/libst_raster_ora.so ")))   

11. After you change the Listener.ora file, restart the Oracle listener.
    

ARCGIS SDE Spatial Processing