ArcGIS Server Banner

Configuring Oracle Net Services to use ST_Geometry SQL functions

Configuring Oracle Net Services to use ST_Geometry SQL functions

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
The SQL functions of the spatial type for Oracle (ST_Geometry) use shared libraries that are accessed via Oracle's external procedure agent, or extproc. The spatial type for Oracle requires these libraries during SQL to spatial type functions. They are not used when accessing ST_Geometry columns through ArcSDE. The Oracle listener must be configured to call functions in these libraries through Oracle's external procedure framework. While it is not strictly required unless you are planning to use the SQL functions, it is recommended that you perform this configuration.

Oracle listeners are highly configurable. For example, there may be multiple listeners associated to 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.

How ST_Geometry function calls work

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:

Configuring the listener

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

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

See Also

  • About geometry storage types