Everyone knows about the fact that they need some extra configuration in Oracle, to work with ST_GEOMETRY outside ArcCatalog/ArcMap. The confusion comes about as to what is considered "outside ArcCatalog/ArcMap" and what if ST_GEOMETRY is not their feature shape storage type. This blog post will hopefully help answer some of these questions and also help you with setting up your Oracle geodatabase to work with ST_GEOMETRY.

I would also like to mention that all the information stated here is published by ESRI in various KB articles, documentation, help resources, etc. I am not stating anything new, just consolidating the information to present it in one location for anyone interested.

If you never interact with your geodatabase outside ArcCatalog/ArcMap, then you have nothing to worry about (but come on, when is that really true).

If your feature class storage data type is ST_GEOMETRY and you want to do any sort of spatial queries and/or modify data outside of ArcMap, you will need to set up your geoadatabase for ST_GEOMETRY.

If your feature class storage data type is NOT ST_GEOMETRY, but you would like to query metadata views such as SDE.GDB_ITEMS_VW using SQLPlus or SQLDeveloper, then you will need to set up your geodatabase for ST_GEOMETRY.

If your feature class storage data type is NOT ST_GEOMETRY, and you are not interested in the above mentioned view, then you may not need to set up your geoadatabase for ST_GEOMETRY.

My recommendation would be to set up your geodatabase to work with ST_GEOMETRY anyways. In this way you do not have to worry about it down the road when you plan to work with one of the above features.

Geodatabase Setup

All the steps in the setup are done on the database server. You will need your DBA to perform these steps. The setup consists of 4 steps.

1. ESRI provides a library which is used for ST_GEOMETRY functions and operators with Oracle's external process. This library needs to be on the database server. The library can be found in any ArcGIS Desktop installation under Desktop 10.xDatabaseSupport Folder. Choose the appropriate RDBMSDatabaseServerOS directory and you should see a file (for example st_shaplib.dll for Windows OS or libst_shapelib.so for Linux OS). Move the library file to a directory of your choosing on the database server (in this example, I am going to use C:OracleArcGIS directory as the location of the library file).

     In addition, if your database OS is Windows, ensure that the 64-bit Visual C++ 2008 SP1 Redistributable Package is installed, if not, it can be downloaded from Microsoft.

2. Create a library object in the Oracle geodatabase as user SDE. This library object will point to the directory and file (full path) location of the library file located in step 1.

Extending our example from step 1, log into the geodatabase as SDE user and run the following command.

Create or replace library st_shapelib as 'C:OracleArcGISst_shapelib.dll' ;

     The name of the library object created in step 2 has to be st_shapelib.

3. Modify the appropriate Oracle files.

If your Oracle version is Oracle 11g and above, then you only need to modify the extproc.ora file located in the ORACLE_HOMEhsadmin directory. The extproc.ora file has a bunch of comments at the top. The line of interest is the last line which is uncommented. In a default state the last line reads

SET EXTPROC_DLLS=

Change this line to below for our example.

SET EXTPROC_DLLS=C:\Oracle\ArcGIS\st_shapelib.dll

if you need to add more than one dll they need to separated by ; in windows and : in Unix/Linux. In windows the path works with \ and in Unix/Linux the path works with / (I know that is not what the comments above in extproc.ora say, but trust me that is the only way I have gotten it to work)

If your Oracle version is Oracle 10g, then you do not have access to extproc.ora file (was not available in 10g). In this case you have to modify the tnsnames.ora and listener.ora files on the database server, so that the external calls can be made. Both these files are located in the ORACLE_HOMEnetworkadmin directory.

Caution: Before making changes to the tnsnames.ora and listener.ora, please make back up copies of these files.

The tnsnames.ora file contains a directory of known database services (local and remote), one entry is specifically used by the local database server to use external function calls (to the extproc). This entry is labeled EXTPROC_CONNECTION_DATA (must always be labeled by this name). Add/modify the entry in tnsnames.ora

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1)))

    (CONNECT_DATA = (SID = PLSExtProc)(PRESENTATION = RO))

  )

The next step is to modify listener.ora. Add an entry in the SID_LIST_LISTENER (this is an example of a default listener named LISTENER. If your listener uses a different name then add the entry in its SID_LIST). My entire listener.ora looks like below

LISTENER =

  (ADDRESS_LIST =

       (ADDRESS= (PROTOCOL= IPC)(KEY = EXTPROC1))

       (ADDRESS= (PROTOCOL= TCP)(Host= hostname.domain.com>)(Port= 1521))

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:oracleproduct10.2db_1)

      (PROGRAM = extproc)

      (ENVS="EXTPROC_DLLS=C:OracleArcGISst_shapelib.dll")

    )

  )

The KEY value in listener.ora should match the corresponding service entry in the tnsnames.ora.

Oracle listener files are highly configurable and have lots of additional options. All the possible options that can be configured in the listener.ora and tnsnames.ora are beyond the scope of this blog post. The listener,ora and tnsnames,ora on your database server may look very different from the simple configuration example mentioned above. Please refer to Oracle Database Net Services Administrator's Guide for details about configuring your listeners.

4. In case of Oracle 10g you will need to restart your listener so that the new configuration can be implemented. Creating library objects may cause some of the dependent objects to become invalid. To ensure all the objects in SDE schema are valid, it is a good idea to run recompile on SDE schema as the final step.

Log into the geodatabase as sys user and run the following command.

EXECUTE sys.utl_recomp.recomp_serial('SDE');

This completes the Oracle geodatabase setup to work with ST_GEOMETRY.