Configure the Oracle extproc to access the geodatabase with SQL

The SQL functions of ST_Geometry and ST_Raster in Oracle use shared libraries that are accessed via the Oracle external procedure agent, or extproc. Oracle must be able to access the physical library files for you to do any of the following:

• Execute ST_Geometry or ST_Raster SQL functions from SQL clients.
• Access the ArcSDE 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.
• Identify features in database views from ArcMap or ArcGIS Pro.
• Register tables containing ST_Raster columns as versioned.
• Use an ArcGIS client to delete a row that includes an ST_Raster column.

You need to configure the Oracle external procedure framework to call the libraries to perform these tasks.

In the majority of cases, you installed the ST_Raster type so you could access it with SQL. For that reason, the instructions for configuring the Oracle extproc to work with ST_Raster are included in the ST_Raster installation topic. This topic focuses on configuring the extproc for ST_Geometry.

Tip:

The ST_Geometry and ST_Raster libraries are installed with ArcGIS Server and ArcMap clients, or they can be downloaded from My Esri. If you have ArcGIS Server or ArcMap installed on the Oracle machine, you can configure your extproc.ora file to point to one of the following locations, and you can skip the two preparatory steps listed below.

• ArcGIS Server on Windows: <ArcGIS Server installation directory>\ArcGIS\Server\DatabaseSupport\Oracle\Windows64
• ArcGIS Server on Linux: <ArcGIS Server installation directory>/arcgis/server/DatabaseSupport/oracle/linux64
• ArcMap: <ArcMap installation directory>\ArcGIS\Desktop<version>\DatabaseSupport\Oracle\Windows64

If you point to the ArcMap or ArcGIS Server location, you will automatically get updated libraries when you install a new ArcMap or ArcGIS Server version. If you place the libraries in a different location, you will need to manually update these libraries to obtain the fixes and improvements included in new versions of the ArcGIS libraries.

Before you configure the Oracle extproc, complete these steps:

1. If your Oracle instance does not have access to the ArcGIS Server or ArcMap installation locations noted above, download the libraries or copy them from the ArcGIS client machine to a directory on the Oracle machine. Be sure to copy the correct library for your Oracle operating system.

   Note:

   ST_Geometry libraries require the Microsoft Visual C++ 2013 Redistributable Package (x64) when deployed on a Windows server. If the Microsoft Visual C++ 2013 Redistributable Package (x64) is not present on the Oracle server, download it from the Microsoft site and install it.

2. 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, you will need to re-create the library after configuring the extproc.

Configure the Oracle extproc.ora 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 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 extrproc.ora to point to the ST_Geometry library in that directory:

   SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll

   If you are using both the ST_Geometry and ST_Raster libraries, you could place entries for both on one line:

   SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll;C:\\mylibraries\\libst_raster_ora.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

   If you are using both the ST_Geometry and ST_Raster libraries, you could place entries for both on one line:

   SET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so:/user/esrilibs/libst_raster_ora.so

3. Save and close 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 libraries (including web services) must reconnect to the database.