Configure ST_Geometry functions in Oracle

[MarcHoogerwerf]

Hi,

I'm trying to get the ST_Geometry functions to work but keep getting Oracle .ORA-28595: Extproc-agent: invalid DLL-path errors.

I'm running Oracle 11gR2 on a 64 bit Windows 7 machine

I copied the st_shapelib.dll from C:\Program Files (x86)\ArcGIS\Desktop10.1\DatabaseSupport\Oracle\Windows64 to a c:\users\904292\Apps folder

Changed the extproc.ora file to point at the st_shapelib.dll using escaped backslashes:
EXTPROC_DLLS=ONLY:C:\\Users\\904292\\Apps\\st_shapelib.dll

If I connect as the sde user I can find the entry for st_shapelib in user_libraries:

LIBRARY_NAME                   FILE_SPEC                                               D STATUS
------------------------------ ---------------------------------------- - -------
ST_SHAPELIB                    c:\users\904292\apps\st_shapelib.dll             Y VALID


Any idea what's missing?

Marc

[MandarPurohit]

Marc,

Could you test setting EXTPROC_DLLS=ANY in extproc.ora, if that makes a difference?
Also, are there any other INVALID objects?
select object_name, owner from dba_objects where status='INVALID';

- Mandar

[MarcHoogerwerf]

Hi Mandar,

Setting EXTPROC_DLLS=ANY didn't help

But you're correct, there are invalid objects:

OBJECT_NAME                              OWNER
---------------------------------------- ------------------------------
ST_GEOM_UTIL                             SDE
ST_GEOMETRY_SHAPELIB_PKG                 SDE
GETLIBRARYVERSION                        SDE

What's ther next step?

Marc

[MandarPurohit]

You can recompile objects by executing utlrp.sql:

SQL> @<ORACLE_HOME>/rdbms/admin/utlrp.sql

(Please replace <ORACLE_HOME> with the appropriate path)

- Mandar

[MarcHoogerwerf]

I ran @C:/app/904292/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql successfully

SQL> select object_name, owner,object_type from dba_objects where status='INVALID';

Er zijn geen rijen geselecteerd.

So no more invalid objects

SQL> select * from user_libraries;

LIBRARY_NAME         FILE_SPEC                                D STATUS
-------------------- ---------------------------------------- - -------
ST_SHAPELIB          c:\users\904292\apps\st_shapelib.dll     Y VALID

I verified that st_shapelib.dll is actually residing in c:\users\904292\apps

Extproc.ora contains a line
EXTPROC_DLLS=ANY


But still get this error:
SQL> select globalid,sde.st_astext(geometrie) geom from landbouwperceel where rownum < 2;
ERROR:
ORA-28595: Extproc-agent: ongeldig DLL-pad
ORA-06512: in "SDE.ST_GEOMETRY_SHAPELIB_PKG", regel 67
ORA-06512: in "SDE.ST_GEOMETRY_OPERATORS", regel 67

Any ideas how to proceed from here?

[MandarPurohit]

Marc,

1) Could you restart the listener and test again with this query?

select sde.st_point(0,0,0) from dual;

2) Do you have ArcSDE application server installed?

Referring to the note:
"If your Oracle database is installed on a Windows server, but you do not have the ArcSDE application server installed, you may need to install the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64). If the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) is not present on the Oracle server, download it from the Microsoft site and install it."

http://resourcesbeta.arcgis.com/en/help/main/10.1/index.html#//002n00000091000000

- Mandar

[MarcHoogerwerf]

Hi Mandar,

I restarted the listener

SQL> select sde.st_point(0,0,0) from dual;
select sde.st_point(0,0,0) from dual
       *
ERROR in line 1:
.ORA-28595: Extproc-agent: invalid DLL-path
ORA-06512: in "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: in "SDE.ST_POINT", line 176

I'm not running the ArcSDE application server.

I checked I had the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) installed, but nevertheless re-installed it. The installer offered to repair my existing installation, so it was definitely installed previously.

I've included a screenshot to show you the version of the C++ Runtimes installed on my machine.

[MarcHoogerwerf]

Hi Mandar,

Problem solved. Only thing I don't understand exactly is what made the difference. In the end I decided to stop and remove the listener, then recreated the listener en proceeded with the instructions as mentioned in the online help. Now everything is working fine. The thing that is different now is the IP=V6_ONLY entry in listener.ora. I found out the ICT people pushed an update from IPv4 to IPv6. That broke my listener. That's when I decided to reinstall the listener service and added the IP=V6_ONLY entry. I'm not sure if that made the difference because I can't go back to the old situation. But anyway, I'm up and running now.

Thanks for thinking along.

- Marc

[MatjazHabic]

Hi
I have the same error »ORA-28595: Extproc-agent: invalid DLL-path« on my win 7 Oracle 11.2.
The problem was missing entry ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) in tnsnames.ora where KEY must be the same in listener.ora and tnsnames.ora.

When I restart Listener i have to kill extproc process otherwise restarting doesn't help.

Matjaz

[JonDeRose]

Hi Mandar,

Setting EXTPROC_DLLS=ANY didn't help

But you're correct, there are invalid objects:

OBJECT_NAME                              OWNER
---------------------------------------- ------------------------------
ST_GEOM_UTIL                             SDE
ST_GEOMETRY_SHAPELIB_PKG                 SDE
GETLIBRARYVERSION                        SDE

What's ther next step?

Marc

Hi Marc,

I am glad that you were able to configure extproc to work for you following further steps but I wanted to add a note for others that while setting EXTROC_DLL=ANY within extproc.ora should work in most situations as Mandar referenced, this will not function properly if the "EXTPROC_CONNECTION_DATA" entry is still found within your tnsnames.ora.  These will conflict.