ArcGIS Help 10.2 - Configuring 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 Oracle's 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 Oracle's external procedure framework.

It is possible to use ST_Geometry and ST_Raster in ArcGIS without setting up the Oracle listener. 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, or execute SQL functions from SQL clients if the Oracle listener is not configured. Additionally, you cannot publish data from a database that uses the ST_Geometry type as a feature service if you do not configure the Oracle listener.

The ST_Raster installation is optional. See Installing the ST_Raster type in Oracle for instructions.

Note:

If your Oracle database is installed on a Windows server, but you do not have the ArcSDE application server installed, 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. The extproc.ora file is located in the ORACLE_HOME\hs\admin directory.

After you move the ST_Geometry (st_shapelib) and/or the ST_Raster library to your Oracle server, alter the extproc.ora file to point to the location of the libraries.

On a Windows server, you would add lines similar to the following:

SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll

SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\libst_raster_ora.dll

If you are using both libraries, you could place them on one line:

SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll;C:\\mylibraries\\libst_raster_ora.dll

In these examples, the libraries are placed in a folder called mylibraries, which was created on the Oracle server to store the libraries.

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 are using both libraries, you could place them on one line:

SET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so:/user/esrilibs/libst_raster_ora.so

In these examples, the libraries are placed in the user's esrilibs directory, which was created on the Oracle server to store the libraries.

Oracle 10g

Oracle listeners are highly configurable. For example, there may be multiple listeners associated with your database, and each listener can manage multiple types of service requests. This is a complex topic, the many variations of which are not covered in this document. It is important that you refer to the Oracle Database Net Services Administrator's Guide for details about configuring your listeners.

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. Please 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.
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 on which the ArcSDE application server and Oracle are both installed:

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:\ArcSDE\sdeexe\bin\st_shapelib.dll;c:\ArcSDE\sdeexe\bin\libst_raster_ora.dll")
 )  
)

This example shows an entry for a Linux or UNIX server on which both the ArcSDE application server and Oracle are installed:

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")
    )
   )

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

6/12/2015