Configuring the Oracle listener 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 the Oracle listener must be configured to call functions in these libraries through Oracle's external procedure framework.

NoteNote:

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 ArcSDE 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 or 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.

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

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 ArcSDE 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 (except IBM AIX) server on which both ArcSDE 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")
        )
       )

    This example shows an entry for ArcSDE for Oracle on IBM AIX.

    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_64.so:/servit/ArcSDE/sdeexe/lib/libst_raster_ora_64.so")
         )
       )

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

11/18/2013