ArcGIS for Server (Windows) - Configure the Oracle extproc to access the geodatabase with SQL

The SQL functions of ST_Geometry and ST_Raster in Oracle use shared libraries that are accessed via the Oracle external procedure agent, or extproc. To use SQL with ST_Geometry or ST_Raster, or to access the ArcSDE XML columns in the GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW views, Oracle must be able to access the libraries. Therefore, the libraries must be present on the Oracle server, and these libraries must be called through the Oracle external procedure framework.

It is possible to use ST_Geometry and ST_Raster in ArcGIS without setting up the Oracle extproc. However, you will not have full functionality from ArcGIS clients and no functionality from SQL clients, such as SQL*Plus. For example, you cannot use SQL functions on ST_Geometry columns in query layers in ArcMap, identify features in database views, version tables containing ST_Raster columns, delete a row that includes an ST_Raster column, publish feature services containing ST_Geometry data, or execute SQL functions from SQL clients if the extproc is not configured.

Note:

If your Oracle database is installed on a Windows server, you may need to install the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64). If the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) is not present on the Oracle server, download it from the Microsoft site and install it.

If you are using Oracle 11g or 12c, edit the extproc.ora file to call functions through Oracle's external procedure framework. If you are using Oracle 10g, configure the Oracle listener.

Oracle 11g and 12c

Oracle recommends that you configure the extproc by altering the extproc.ora file rather than configure the listener for Oracle 11g and 12c. The extproc.ora file is located in the ORACLE_HOME\hs\admin directory.

Steps:

Make a backup copy of the extproc.ora file on the Oracle server.
Open the extproc.ora file and alter it to point to the location of the ST_Geometry (st_shapelib) and/or the ST_Raster libraries.
In this example on a Windows server, the following line is added to point to the ST_Geometry library in a folder named mylibraries:
```
SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll
```
If you are using both the ST_Geometry and ST_Raster libraries, you could place entries for both on one line:
```
SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll;C:\\mylibraries\\libst_raster_ora.dll
```
In this example on a Linux or UNIX server, the following line is added to point to the ST_Geometry library in the user's esrilibs directory, which was created on the Oracle server to store the library:
```
SET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so
```
If you are using both the ST_Geometry and ST_Raster libraries, you could place entries for both on one line:
```
SET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so:/user/esrilibs/libst_raster_ora.so
```
Save and close the extproc.ora file.
Any clients that need access to the libraries (including web services) must reconnect to the database.

Oracle 10g

Telling the extproc where to find the shared libraries is the most important aspect of configuring the listener. You need to modify the listener configuration to specify the location of the shared libraries and restart the Oracle listener process so the configuration changes take effect.

Two standard Oracle Listener configuration files are involved: tnsnames.ora and listener.ora. These files usually reside in ORACLE_HOME/net/admin. This document presents the configuration settings that are needed.

There are several ways to manage the settings. You can edit the text files with a text editor, use the Oracle Net Manager, or use the Oracle Net Configuration Assistant. Oracle provides documentation about how to configure the listener. Refer to the Oracle Database Net Services Administrator's Guide for details.

The tnsnames.ora file contains a directory of known database services. This file can define services on the local database or on remote servers. One entry is specifically for use by the local database server to use interprocess communications (IPC) to send function calls to the extproc. This entry is labeled EXTPROC_CONNECTION_DATA. You can alter the Key and SID values under this entry.

These items are used to link this entry to corresponding information in the listener.ora file. The key can be any short name but must be the same in both the listener.ora and tnsnames.ora files. These values are case sensitive. They are used only by the listener process and not by users or applications.

The listener.ora file describes some (not necessarily all) of the services for which the listener listens for requests.

The values that can be altered in this file include the following:

SID_LIST_LISTENER
This label begins a list of SIDs to be handled by the listener named LISTENER (the default listener name).
SID_LIST and SID_DESC
The SID_DESC entries under the heading SID_LIST define services. The first in the list handles extproc requests, and the second handles client sessions.
SID_NAME
The value for this must correspond to the SID specified for the extproc in the file tnsnames.ora.
ORACLE_HOME
This defines the location of the Oracle home for this service. The extproc program files load from a folder beneath this location.
PROGRAM
This specifies the file name of the extproc executable file. This case-sensitive name might be extproc or extproc.exe depending on the operating system type. The file is located in ORACLE_HOME/bin.
ENVS
This is a list of environment variables that the extproc uses when it runs. The list is colon delimited. This list must include a definition of the environment variable EXTPROC_DLLS and any other environment variable the extproc needs when it runs, often including LD_LIBRARY_PATH, SHLIB_PATH, or LIBPATH on UNIX and Linux systems or PATH on Windows servers. The path variable often includes the location of the geometry and projection engine libraries.
EXTPROC_DLLS
This environment variable defines a list of libraries that the extproc can load and call functions from directly. The path to the file containing the shape library, which is necessary to access ST_Geometry types or the GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW views using SQL, and st_raster library, which is necessary to access the ST_Raster type using SQL, must be specified here.
The list is colon delimited for Oracle servers installed on UNIX and semicolon delimited for Oracle servers installed on Windows. All paths must be absolute and point to local directories or a UNC path that the Oracle system user can access. This also means if you have installed an ArcSDE application server on a different server than Oracle, you must copy the shape and raster libraries from the bin directory in SDEHOME to the Oracle server.
There is no environment variable substitution.
The optional keywords ANY and ONLY can be used to loosen or restrict the way the extproc uses library files. If you preface the path with ANY, Oracle can load any libraries from the specified path, and you don't have to provide a library name. If you preface the path with ONLY, Oracle will only use the specific library you provide in the path.
KEY
The value set for this entry links this listener with the corresponding service entry in the file tnsnames.ora. It distinguishes this listener from other IPC listeners that might be present on the same database server. The key can be any short name but must be the same in the files listener.ora and tnsnames.ora. The key is case sensitive.

Steps:

Before making any changes, make backup copies of the files tnsnames.ora and listener.ora.
Open the tnsnames.ora file on the Oracle server.
Alter 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 the label EXTPROC_CONNECTION_DATA. This entry is used by the database server for interprocess communications to send function calls to the extproc.
Save changes and close the tnsnames.ora file.
Open the listener.ora file on the Oracle server.

Alter the necessary entries to set up the services from which the listener will accept requests.

This first example is for a Windows operating system, and the ST_Geometry and ST_Raster libraries are in a folder named gislibs:

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 = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
         (ENVS="EXTPROC_DLLS=C:\gislibs\st_shapelib.dll;C:\gislibs\libst_raster_ora.dll")
 )  
)

This example shows an entry for a Linux or UNIX server on which the ST_Geometry and ST_Raster libraries are stored in /arcgis/libs:

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=/arcgis/libs/libst_shapelib.so:/arcgis/libs/libst_raster_ora.so")
    )
   )

After you alter and save the listener.ora file, restart the Oracle listener.