The ST_Geometry SQL functions in Oracle use a shared library that Oracle accesses via the Oracle external procedure agent, or extproc. Oracle requires access to the physical library file for you to do any of the following:
- Execute ST_Geometry SQL functions from SQL clients.
- Query the XML columns in the GDB_ITEMS_VW and GDB_ITEMRELATIONSHIPS_VW geodatabase system views.
- Define a query layer in ArcMap or ArcGIS Pro that executes SQL functions on ST_Geometry columns.
- Publish a web service that references data in the Oracle database.
Configure the Oracle external procedure framework to call the ST_Geometry library to perform these tasks.
Before you configure the Oracle extproc, complete these steps:
- If your Oracle instance does not have access to the ArcGIS Server or ArcMap installation locations noted above, download the ST_Geometry library from My Esri or copy the file from the ArcGIS client machine to a directory on the Oracle machine. Be sure to copy the correct library for your Oracle operating system.
- The Create Spatial Type, Create Enterprise Geodatabase, and Enable Enterprise Geodatabase geoprocessing tools set a default path for the ST_SHAPELIB library. Connect to the Oracle instance as the sde user from an SQL client, and execute the following SQL to see what path is currently set for the ST_SHAPELIB library:
SELECT file_spec FROM user_libraries WHERE library_name = 'ST_SHAPELIB';
If the path returned from this does not match the location of the ST_Geometry library file, re-create the library after configuring the extproc.
Configure the Oracle extproc.ora file to point to the location of the ST_Geometry library file.
- Make a backup copy of the extproc.ora file on the Oracle server.
- Open the extproc.ora file and alter it to point to the location of the st_shapelib.dll (Windows) or libst_shapelib.so (Linux or UNIX) library.
In this example, no ArcGIS client is installed on the Oracle Windows server, so the st_shapelib.dll has been copied to a directory named mylibraries on the Oracle machine. The following line is added to extproc.ora to point to the ST_Geometry library in that directory:
SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll
In this example on a Linux or UNIX server, the following line is added to point to the ST_Geometry library in the user's esrilibs directory, which was created on the Oracle server to store the library:
SET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so
- Save and close the extproc.ora file.
You may need to restart the Oracle instance for it to recognize changes in the extproc.ora file.
- If you changed the location of the ST_Geometry library file from the default location or no location was set, create or re-create the ST_SHAPELIB library in the Oracle database.
In this example, the ST_SHAPELIB library is set to the mylibraries directory on an Oracle Windows server where the st_shapelib.dll file was placed.
CREATE or REPLACE LIBRARY ST_SHAPELIB AS 'C:\mylibraries\st_shapelib.dll';
- If you re-created the library, recompile the sde.st_geometry_shapelib_pkg package.
ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;
- Any clients that need access to the library (including web services) must reconnect to the database.