Configure the extproc to access ST_Geometry in Oracle

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.
• Define a query layer in ArcMap or ArcGIS Pro that executes 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 copy the file from an ArcGIS Server installation or download it from My Esri.

The libraries are installed with ArcGIS Server in the following locations:

• <ArcGIS Server installation directory>\ArcGIS\Server\DatabaseSupport\Oracle\Windows64\st_shapelib.dll
• <ArcGIS Server installation directory>/arcgis/server/DatabaseSupport/oracle/linux64/libst_shapelib.so
• <ArcGIS Server installation directory>/arcgis/server/DatabaseSupport/oracle/ibmaix64/libst_shapelib.so
• <ArcGIS Server installation directory>/arcgis/server/DatabaseSupport/oracle/sunos64/libst_shapelib.so

The Oracle instance must have access to the ST_Geometry library. Either place the library on the Oracle machine in a directory that the instance can access or, if ArcGIS Server and Oracle are installed on the same machine, you can configure the extproc.ora file to point to the file in the ArcGIS Server installation directory.

Note:

If you configure the extproc.ora file to point to the file in the ArcGIS Server installation directory, you can skip the preparatory steps below. However, running ArcGIS Server and Oracle on the same machine can cause performance issues due to resource contention on the machine.

Before you configure the Oracle extproc, complete these steps:

1. If your Oracle instance does not have access to the ArcGIS Server installation locations noted above, download the ST_Geometry library from My Esri or copy the file from an ArcGIS Server machine to a directory on the Oracle machine.

   Note:

   Be sure you 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 Oracle database requirements for the package version required. If this package is not present on the Oracle server, download it from the Microsoft site and install it.

When you run the Create Spatial Type geoprocessing tool to enable ST_Geometry in an Oracle database, or you run the Create Enterprise Geodatabase or Enable Enterprise Geodatabase geoprocessing tools to create a geodatabase, the tools set a default path for the ST_SHAPELIB library.
2. Connect to the Oracle instance as the sde user from an SQL client, and execute 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 the extproc.

Configure the extproc

Configure the Oracle extproc.ora file to point to the location of the ST_Geometry library file.

1. Make a backup copy of the extproc.ora file on the Oracle server.
2. 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, no ArcGIS client is installed on the Oracle Windows server, so the st_shapelib.dll 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

3. 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.

4. 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';

5. If you re-created the library, recompile the sde.st_geometry_shapelib_pkg package.

   ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE
    REUSE SETTINGS;

6. 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 the extproc can access it.

1. Sign in to an Oracle SQL client.
2. Run the following query:

   SELECT sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) FROM dual;

   If the 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 the extproc is not properly configured, you could see one or more of the following error messages:

   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