> This is one of my biggest beefs with ESRI. You should not need to create a geodatabase,
Exactly. They are lazy, slow "GIS" programmers (or rather hacks) who use the database mainly as a bit-bucket, not taking advantage of it's native features. The corporate smucks keep buying their buggy code because their salesmen could sell ice to eskimos at their drink the cool-aid conferences. It's nuts.
Sorry, back to our regularly scheduled geek fest.
Phew. Glad I'm not alone ...
For more completeness, the main two packages which require DLL's are the ST_Geometry and ST_Raster libraries
which are installed with
ArcGIS Server and
or they can be downloaded from My Esri.
If you have either installed on the Oracle machine,
configure extproc.ora to point to
<ArcGIS Server dir>\ArcGIS\Server\DatabaseSupport\Oracle\Windows64
<ArcMap install dir>\ArcGIS\Desktop<version>\DatabaseSupport\Oracle\Windows64
Connect to oracle as SDE and run
SELECT file_spec FROM user_libraries WHERE library_name = 'ST_SHAPELIB';
Open/edit extproc.ora to point to st_shapelib.dll
In this example, no ArcGIS client is installed on the Oracle Windows server,
so the st_shapelib.dll has been copied
to a directory named mylibraries on the Oracle machine.
SET EXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll ...or if both:
CREATE or REPLACE LIBRARY ST_SHAPELIB AS 'C:\mylibraries\st_shapelib.dll';
ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;
For more completeness, I did find a database and schema which DID have the package body. (which showed which procedures call functions inside the external C DLL library file)
In this case there was an operator called ST_ASTEXT
which pointed to the SDE.ST_GEOMETRY_OPERATORS "st_astext_f" function,
which called the SDE.ST_GEOMETRY_SHAPELIB package "astext" procedure.
(which in turn called for the missing DLL)
SELECT owner, operator_name, function_name FROM SYS.ALL_OPBINDINGS where OPERATOR_NAME = 'ST_ASTEXT';
A quick follow up, (for even MORE completeness), I persuaded our administrators to correct our USER_LIBRARY file_spec location (to point to the actual location of the ESRI dll file), with the help of some ESRI docs
FAQ: When does the Oracle library for st_shapelib.dll need to be changed?
CREATE OR REPLACE LIBRARY st_shapelib AS 'C:\Program Files\ESRI\ArcSDE\ora11gexe\bin\st_shapelib.dll';
then recompile any related packages/functions which became invalid
SELECT object_name FROM user_objects WHERE status = 'INVALID';
So basically just re-creating the library object to point to the dll fixed it:
select owner, LIBRARY_NAME, FILE_SPEC, status from dba_libraries where owner='SDE' and LIBRARY_NAME='ST_SHAPELIB';
And now those 2 packages work just fine.
select SDE.st_astext(sde.st_point(0,0,0)) from dual; --Simple test works