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.

NoteNote:

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:

Steps:
  1. Before making any changes, make backup copies of the files tnsnames.ora and listener.ora.
  2. Open the tnsnames.ora file on the Oracle server.
  3. 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.

  4. Open the listener.ora file on the Oracle server.
  5. 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")
        )
       )

  6. After you alter the listener.ora file, restart the Oracle listener.
6/19/2015