ORA-28595: Extproc agent : Invalid DLL Path

Question

I'm trying to create a table with spatial column in oracle and insert data into it. I'm using ArcSDE and Oracle. The version of Oracle is 11.2.0, the version of ArcSDE is 10.0.

My create statement is:

CREATE TABLE sensitive_areas (area_id integer, name varchar(128), 
area_size float, type varchar(10), zone sde.st_geometry);

And insert statement is:

INSERT INTO SENSITIVE_AREAS (area_id, name, area_size, type, zone)
VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', sde.ST_PolyFromText('polygon ((52 28,58 28,58 23,52 23,52 28))', 0));

I've already added st_shaplib.dll and libst_raster_ora.dll to the environment variable EXTPROC_DLLS.

My tnsname.ora:

    LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

My listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\rainman\product\11.2.0\dbhome_3)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\rainman\product\11.2.0\dbhome_3\bin\oraclr11.dll;D:\Program Files\ArcGIS\ArcSDE\ora11gexe\bin\st_shapelib.dll;D:\Program Files\ArcGIS\ArcSDE\ora11gexe\bin\libst_raster_ora.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\rainman

However, I got this error: ORA-28595: Extproc agent : Invalid DLL Path. What should I do?

Thanks

USER_LIBRARIES entry for ST_SHAPELIB is also correct: ST_SHAPELIB D:\Program Files\ArcGIS\ArcSDE\ora11gexe\bin\st_shapelib.dll Y VALID

Answer 1

If you've already compiled the PUBLIC and SDE schema after setting the user library, that error message looks like you do not have the actual EXTPROC_DLLS environment variable on your Oracle server. From your settings it looks like your Oracle is on Windows.

Your listener path and user library point to your st_shapelib.dll residing in the ArcSDE home. Oracle won't access that location if the EXTPROC_DLLS environment variable is not set:

Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load. **Without the EXTPROC_DLLS environment variable, extproc loads DLLs from ORACLE_HOME/lib on UNIX operating systems and ORACLE_HOME\bin on Microsoft Windows.**

Source: Enabling Advanced Features of Oracle Net Services - Table 13-5 External Procedures Settings in listener.ora http://docs.oracle.com/cd/E11882_01/network.112/e41945/advcfg.htm#NETAG0132

To rectify this you can move the st_shapelib.dll file into your Oracle_Home\bin and repoint your listener.ora file and Oracle SDE user library, then compile.

Alternatively, you can create the Windows system environmental variable EXTPROC_DLLS using the source provided. Restart your listener just in case after setting the variable.

Run a quick test after doing so such as:

select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

Here is the Esri information on EXTPROC_DLLS:

Configuring the Oracle listener to access the geodatabase with SQL(10.0) http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002n00000091000000

If that doesn't succeed, post the rest of the error message. It should be more than just one line/one Ora error.