5

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

2
  • Have you created the ST_Geometry type in Oracle (there is a GP tool in ArcGIS for this)? It sounds like you may have, but you didn't mention it. – Evil Genius Jul 24 '14 at 10:48
  • 1
    First off, if you haven't installed Service Pack 5, do so (for both ArcGIS and ArcSDE). Second, make sure your Oracle install is 64-bit. Third, inspect the USER_LIBRARIES view of the SDE user to see if it matches the value you set in the listener; if not, follow the documentation for changing the library and recompiling the packages. – Vince Jul 24 '14 at 11:08
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.