Functions are implemented in PL/SQL, which in turn calls functions written in the C programming language stored in external shared library files. The functions are called from PL/SQL using an alias name that maps the name of the library—in the case of the spatial type for Oracle, ST_SHAPELIB—to the name of the library file. (See the documentation for the Oracle CREATE LIBRARY command for details.) The first time a spatial type function is called that needs something from ST_SHAPELIB, the database requests the listener to spawn an extproc process for the SQL session. The extproc is given the location of ST_SHAPELIB, the name of the function to be called, and its parameters. The extproc loads ST_SHAPELIB and calls the function. If the external function calls functions in other external libraries, such as the Geometry library (sg) or the Projection Engine library (pe), those libraries are also loaded by the extproc. When the external function completes, the extproc returns the results and remains active, waiting for additional function calls during this session. The extproc process terminates when the SQL session disconnects.

To make this work, the following configuration is needed:

• The database needs to know the location of the file containing ST_SHAPELIB so it can pass this information to the listener process and on to the extproc.
If the file_spec for the ST_SHAPELIB in the user_libraries table does not match the physical file location of the ST_SHAPELIB on the server, ST_Geometry operators and functions will not work. Therefore, you must update the library definition in the Oracle data dictionary to include the correct library path to the file containing ST_SHAPELIB.

NOTE: Altering the definition of the library path invalidates package bodies that refer to it; therefore, the package bodies of some ArcSDE stored procedure packages should be recompiled using Oracle.

• The database must know of the service that handles requests to the extproc. This is configured in the file tnsnames.ora.
• The extproc must be allowed to load the file containing ST_SHAPELIB. This is done by defining the environment variable EXTPROC_DLLS in the file listener.ora.
• The location of the Geometry and Projection Engine libraries must be added to the list of locations from which shared libraries can be loaded. On UNIX systems, this means the location must be defined in LD_LIBRARY_PATH (or SHLIB_PATH or LIBPATH, depending on your operating system). The environment variable must be defined for the extproc in the file listener.ora.
• The extproc (usually running as the user that owns ORACLE_HOME) must have read permissions on the location of the library files and execute permissions on the files.
• After its configuration is changed, the listener must be restarted.

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 will 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.

Before making any changes, make backup copies of the files tnsnames.ora and listener.ora.

Configuring tnsnames.ora

The file tnsnames.ora 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. Here is an example as it appears in the file tnsnames.ora. (Please note that this will probably not be the only entry in the file.)

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

• EXTPROC_CONNECTION_DATA
This entry must always have the label EXTPROC_CONNECTION_DATA.
• Key and SID
The two items in this entry that could be changed are the name of the key (EXTPROC1) and the SID (PLSExtProc). These items are used to link this entry to corresponding information in the file listener.ora. 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.

Configuring listener.ora

The file listener.ora describes some (not necessarily all) of the services for which the listener listens for requests. This example uses the same listener for the extproc as for connection requests for the database. Other configurations are possible.

NOTE: This is a sample only and may be different for every database instance.

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\sdeexe93\bin\st_shapelib.dll")
    )
    (SID_DESC =
      (SID_NAME = sde93)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (GLOBAL_NAME = sde93)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr1.dmn1.com)(PORT = 1521))
    )
  )

• 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 example above defines two services, which are listed as two SID_DESC entries under the heading SID_LIST. The first in the list (PLSExtProc) handles extproc requests, and the second (sde93) handles client sessions.
• SID_NAME = PLSExtProc
This corresponds 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 (see below) and any other environment variable the extproc needs when it runs, often including LD_LIBRARY_PATH on UNIX and Linux systems. LD_LIBRARY_PATH 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 ST_SHAPELIB must be specified here. The Geometry and Projection Engine libraries are not included in this list because the extproc does not call functions in them directly.
The list is colon delimited. All paths must be absolute. 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. In many cases, neither keyword is needed.
• KEY = EXTPROC1
The name in the example, EXTPROC1, 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.