The ST_Geometry SQL functions in Oracle use a shared library that Oracle accesses through the Oracle external procedure agent, or extproc. Oracle requires access to the physical library file for you to do any of the following:
- Run ST_Geometry SQL functions from SQL clients.
- Define a query layer that runs SQL functions on ST_Geometry columns.
- Query binned feature layers in ArcGIS Pro.
- Publish a web layer that references data in the Oracle database.
- Run the Select Layer By Location geoprocessing tool with the Intersect (DBMS) option.
You must configure the Oracle external procedure framework to call the ST_Geometry library—ST_SHAPELIB—to perform the tasks listed above.
Preparatory steps
The ST_Geometry library is created for specific operating systems. You can download the library file specific to your operating system and client version from My Esri.
The Oracle instance must have access to the ST_Geometry library. Place the library on the Oracle machine in a directory that the Oracle instance can access.
Before you configure the Oracle extproc file, complete the following steps:
- Download the ST_Geometry library from My Esri and place it in a directory on the Oracle machine.
Note:
Be sure to download or copy the correct library for your Oracle operating system.
The ST_Geometry library requires the Microsoft Visual C++ Redistributable Package (x64) when deployed on a Microsoft Windows server. See ArcGIS requirements for Oracle for the package version required. If this package is not present on the Oracle server, download it from the Microsoft site and install it.
- Connect to the Oracle instance as the sde user from an SQL client, and run the following SQL to confirm that the path set by the tool matches the location of the ST_Geometry 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 extproc.
Configure extproc
Configure the Oracle extproc.ora file to point to the location of the ST_Geometry library file.
- Make a backup of the extproc.ora file on the Oracle server.
- Open the extproc.ora file in a text editor 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, the st_shapelib.dll file was 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 identify 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.
You must connect to the database as the sde user to create or re-create the ST_Geometry library.
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.
Validate the extproc configuration
You can query the ST_Geometry library to ensure that extproc can access it.
- Sign in to an Oracle SQL client.
- Run the following query:
SELECT sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) FROM dual;
If extproc is properly configured, the query will return the following:
SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0)) -------------------------------------------------------------------------------- POINT ( 10.00000000 10.00000000)
If extproc is not properly configured, one or more of the following error messages may appear:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 0
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 68
Upgrades and the ST_Geometry library
You must download or copy new versions of the library file to obtain the fixes and improvements included in new versions of the library whenever you upgrade the geodatabase in Oracle or need to upgrade the ST_Geometry type in an Oracle database.
Oracle may mark the library as invalid when it is replaced or after you upgrade the Oracle instance. Oracle sometimes recompiles invalid database objects automatically when the objects are referenced by a client, but there are cases when they are not compiled automatically.
The ST_Geometry library is owned by the sde user. To check the status of the library (and all files owned by the sde user), sign in to an SQL client as the sde user and run the following statement:
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE STATUS = 'INVALID';
If the sde.st_geometry_shapelib_pkg package body is returned in the list of invalid objects, recompile it as described in step 5 of the Configure extproc section above.
If more than one object is invalid, you can recompile all objects in the same schema. See the Oracle documentation for the privileges required and instructions to recompile multiple objects at a time.