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.

Note:

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:

• 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 ST_SHAPELIB library, which is necessary to access ST_Geometry types or the GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW views using SQL, and LIBST_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 ArcSDE on a different server than Oracle, you must copy the shape and raster libraries to the Oracle server. See Installing Oracle and the ArcSDE on separate servers for more information.

  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:

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.