Can you use the default configuration for external procedures : extproc.ora file

[MarkChilcott]

To use the SQl functions of ST_Geometry, you need to configure the Oracle listener.ora and the tnsnames.ora files.  The following link to Oracle documentation would indicate the default configuration no longer requires this configuration change, just a simple addition to the extproc.ora file.  There are some times when this won't work though - see below.

Question then is, will this default configuration work with ArcSDE ST_Geometry?
Anyone know?

http://download.oracle.com/docs/cd/B28359_01/network.111/b28316/advcfg.htm#NETAG0132

13.5.1 indicates:

Note:
The default configuration for external procedures no longer requires a network listener to work with Oracle Database and extproc agent. The extproc agent is spawned directly by Oracle Database and eliminates the risks that extproc might be spawned by Oracle Listener, unexpectedly. This default configuration is recommended for maximum security.
You can change the default configuration for external procedures and have your extproc agent spawned by Oracle Listener. To do this, however, you must perform additional network configuration steps.
Having your extproc agent spawned by Oracle Listener is necessary if you use:

* Multi-threaded Agent
* Oracle Database in MTS mode on Windows
* AGENT clause of the LIBRARY specification or AGENT IN clause of the PROCEDURE specification such that you can redirect external procedures to a different extproc agent



When you use the default configuration for external procedures, the extproc agent is spawned directly by Oracle Database. There are no configuration changes required for either listener.ora or tnsnames.ora.
When the default configuration for external procedures is used, define the environment variables to be used by external procedures in the extproc.ora file located in the $ORACLE_HOME/hs/admin directory on UNIX operating systems or the %ORACLE_HOME%\hs\admin directory on Windows.

[TravisVal]

Mark, thanks for pointing this out.

Right now only windows has been tested, but at first glance this does appear to work.  The only quirk on the system (Windows Server 2008 R2 64bit / Oracle 11.2.0.2 64bit) that I have been testing this on is, it appears that the EXTPROC_DLL values need to be escaped ('\' needs to be '\\').  As in, adding the following entries to the extproc.ora file appear to work:

EXTPROC=ANY
EXTPROC=C:\\ArcGIS\\ArcSDE\\.....st_shapelib.dll
EXTPROC=ONLY:C:\\ArcGIS\\ArcSDE\\.....st_shapelib.dll

We will continue testing this, and if successful update our documentation.

Thanks again for bringing this to my attention.
Travis

[MarkChilcott]

Hi Travis,

No problem.

Cheers,

Mark

[MichaelHöck]

Hi Travis, hi Mark,

I also tried to use extproc.ora instead of configuring listener.ora and the st functions worked for me too. My system is a Windows Server 2008 R2 64bit with Oracle 11.2.0.2 64bit and I used SQL*Plus directly from this machine or from a Windows 7 client with an Oracle 11.2.0.1 32bit client installation.

Did you continue your tests ? Do you think that this could be a supported alternative for using the spatial type functions ?

Thank you
Martin
Esri Germany

[TravisVal]

Hey Martin,

We did continue our testing and this is now a supported configuration.  The documentation is in the process of being updated.

Thanks
Travis

[MichaelHöck]

Travis, that's good news!

Thanks,
Martin
Esri Germany

[MarkChilcott]

Good result.  Thanks for the follow up.