Enabling direct SQL manipulation of Esri ST_Geometry types in Oracle requires precise configuration of the external procedure (extproc) listener. This guide outlines the necessary steps to establish a stable connection between the Oracle database and the spatial processing libraries.

1. Storage Format and Version Compatibility

Before configuring the listener, verify that your spatial data is stored using the native ST_Geometry type. Legacy ArcSDE implementations often used BLOB or LONG RAW columns. Upgrading the software does not automatically convert the underlying storage. Attempting to run spatial SQL on unconverted columns will fail regardless of listener settings. Use the official storage migration utilities to upgrade legacy geometries to ST_Geometry. Additionally, avoid using this workflow with ArcSDE 9.2 due to documented stability issues in the external library interface.

2. Listener Registration State and Protocols

The Oracle listener must handle both TCP database connections and IPC calls for external procedures. Run lsnrctl status to verify the configuration. The extproc service must show a status of UNKNOWN. This indicates static registration, wich is mandatory for extproc. A READY state implies dynamic registration, which will prevent the spatial libraries from loading correctly.

$ lsnrctl status
...
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=GIS_EXT_KEY)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.20)(PORT=1521)))
Services Summary...
Service "GISExtProc" has 1 instance(s).
  Instance "GISExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "gisdb" has 1 instance(s).
  Instance "gisdb", status READY, has 1 handler(s) for this service...

3. Architecture Alignment and Library Dependencies

The external procedure agent runs within the Oracle database server's environment. Therefore, the spatial libraries must match the database server's operating system and architecture (32-bit vs. 64-bit), regardless of the client or ArcSDE application server configuration. If your database runs on 64-bit Linux, you must deploy the 64-bit Linux versions of the spatial libraries to the database host.

Three core libraries are required for full functionality:

• st_shapelib (primary geometry processor)
• pe (projection engine)
• sg (spatial geometry utilities)

Ensure the Oracle OS user has read and execute permissions on these files. On Windows, they are .dll files; on UNIX/Linux, they are .so or .sl files.

4. Registering the Spatial Library in Oracle

Connect as the SDE schema owner and verify that the external library is registered correctly:

SQL> SELECT library_name, file_spec, status FROM user_libraries WHERE library_name = 'ST_SHAPELIB';

LIBRARY_NAME    FILE_SPEC                                          STATUS
--------------- -------------------------------------------------- ------
ST_SHAPELIB     /opt/oracle/product/19c/dbhome_1/lib/st_shapelib.so VALID

If the library is missing or points to an incorrect path, recreate it and recompile the dependent package:

SQL> CREATE OR REPLACE LIBRARY st_shapelib AS '/u01/app/oracle/gis_libs/libst_shapelib.so';
SQL> /
Library created.

SQL> ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;
Package altered.

5. Configuring Network Files

The extproc mechanism relies on synchronized entries in listener.ora and tnsnames.ora. The KEY parameter must match exactly across both files.

listener.ora Configuration

Define a static SID for the external procedure agent. Use the ENVS parameter to specify library paths or allow dynamic loading with EXTPROC_DLLS=ANY.

# listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = GISExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=/u01/app/oracle/gis_libs")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = gisdb)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME = gisdb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = GIS_EXT_KEY))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.20)(PORT = 1521))
    )
  )

tnsnames.ora Configuration

Add a dedicated alias for the extproc connection. Modern Oracle installations may omit this by default, so it must be added manually.

# tnsnames.ora
GISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisdb)
    )
  )

EXTPROC_GIS_LINK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = GIS_EXT_KEY))
    )
    (CONNECT_DATA =
      (SID = GISExtProc)
      (PRESENTATION = RO)
    )
  )

6. Parameter Reference

• SID_LIST / SID_DESC: Defines static service handlers. The first entry routes extproc calls; the second handles standard client sesssions.
• SID_NAME: Must exactly match the SID defined in the tnsnames.ora extproc alias.
• PROGRAM: Specifies the external procedure executable (extproc or extproc.exe), located in $ORACLE_HOME/bin.
• ENVS: Passes environment variables to the extproc agent. EXTPROC_DLLS is mandatory. Use ANY to permit loading any library from the specified paths, or ONLY to restrict loading to explicitly named files. Path separators are colons (:) on UNIX and semicolons (;) on Windows.
• KEY: A case-sensitive identifier linking the IPC endpoint in listener.ora to the corresponding entry in tnsnames.ora.

7. Validation Query

After restarting the listener (lsnrctl stop followed by lsnrctl start), execute a spatial function to confirm the external library is loading correctly:

SQL> SELECT sde.st_astext(geom_column) AS wkt_output FROM gis_features WHERE fid <= 2;

WKT_OUTPUT
--------------------------------------------------
POINT (-13625889.6162 4541020.4788)
POINT (-13627070.4123 4548787.0414)