ST_Geometry function calls

ST_Geometry functions in Oracle and PostgreSQL are implemented in structured query language (SQL). This is a database-level language. In Oracle, it is Procedural Language Structured Query Language (PL/SQL). In PostgreSQL, it is PL/pgSQL.

When you access ST_Geometry columns using SQL functions, you are accessing the database directly. Therefore, the database must be able to access the ST_Geometry functions. In Oracle, the database also spawns an extproc process.

PostgreSQL

The st_geometry library must be stored in the PostgreSQL installation directory to allow PostgreSQL to access the ST_Geometry functions. If you install ArcSDE on the same Windows server as PostgreSQL using the ArcSDE for PostgreSQL installation wizard, the st_geometry.dll is copied to the PostgreSQL lib folder for you. If your database is on a different server than ArcSDE or you are installing on a Linux server, you must manually copy the library to the PostgreSQL installation directory. On Linux servers, copy the st_geometry.so library to the /usr/lib/pgsql directory.

Oracle

Functions are implemented in PL/SQL, which calls functions in external shared library files written in the C programming language. The functions are called from PL/SQL using an alias name that maps the name of the library—in the case of ST_Geometry in 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 requires 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. 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 user libraries to include the correct library path to the file containing ST_SHAPELIB using the CREATE LIBRARY command.
  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 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 changing its configuration, the listener must be restarted for the changes to take affect.