XML functions and procedures don't work - error ORA-06522

[GeoffLau]

We have an ARCSDE 10.0 geodatabase in Oracle 11.2, using both sdo_geometry and st_geometry (in different tables).

The extproc listener is configured and the st_geometry functions work correctly - for example:
SDEPROD>   select sde.st_astext(shape) from cgdweb.crashes where rownum < 2;
SDE.ST_ASTEXT(SHAPE)
----------------------------------------------------------------------------

POINT  ( 694076.36722689 6093035.09159664)

However the xml modules do not work. Querying the gdb_items_vw view where xml blobs are mapped to clobs with the sde.sdexmltotext operator fails as shown below:

SDEPROD> select definition from gdb_items_vw
  2  where definition is not null;
ERROR:
ORA-06521: PL/SQL: Error mapping function
ORA-06522: ld.so.1: extproc: fatal: SDEXML_To_Text: can't find symbol
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1339
ORA-06512: at "SDE.ST_GEOM_UTIL", line 760

Not all modules in st_geom_util fail, for example:
declare
  mysrid   number;
begin
  sde.st_geom_util.validate_geom_srid
   (owner   => 'cgdweb',
    table_name                  => 'crashes',
    column_name   => 'shape',
    srid   => mysrid);
  dbms_output.put_line(mysrid);
end;
/

30

but the modules which seem to make external callouts do fail, including st_geom_util.sdexml_to_text_f as above, and st_geom_util.checklibraryversion and st_geom_util.getlibraryversion:
SDEPROD> select st_geom_util.getlibraryversion from dual;
select st_geom_util.getlibraryversion from dual
       *
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06522: ld.so.1: extproc: fatal: getVersion: can't find symbol
ORA-06512: at "SDE.ST_GEOM_UTIL", line 646
ORA-06512: at "SDE.ST_GEOM_UTIL", line 682

Our library has cksum:
$ cksum /app/oracle/db/11.2.0/lib/libst_shapelib.so
3989627067      167888  /app/oracle/db/11.2.0/lib/libst_shapelib.so

Might this be a faulty library? It seems to have definitions of some functions and procedures but not others.

[VinceAngelo]

There's a bunch of possible issues here.  You should certainly review the ST_GEOMETRY
library configuration procedures, to make sure you've correctly registered the DLLs
through the database (recopying wouldn't hurt, since maybe you have an older DLL
sitting in the Oracle instance).

- V

[GeoffLau]

Hi Vince,

I was hoping for a response from you.

Could you list some of the possible issues please, as a guide to what I should try next?

Unless we were using st_raster, I understood that there is just one library file required - the shapelib library libst_shapelib.so
- correct?

Are you able to list the cksum for the appropriate library for Oracle 11.2 64-bit on Solaris 10?

Is the correct library file available online for download?

Geoff

[VinceAngelo]

You can't shake an ArcGIS installer without an ST_GEOMETRY library falling out
of it -- I think you can choose from Desktop, Server, and ArcSDE (I generally
use the ArcSDE install).

Your issue may be in the Oracle install, and the XML libraries it has available.

- V

[GeoffLau]

Hi Vince

I'll investigate the Oracle XML database issue, and I'll look for other shapefile libraries.

But would you expect the xml stuff to have anything to do with the failures of:
select st_geom_util.checklibraryversion from dual;
and
select st_geom_util.getlibraryversion from dual;
?

Geoff

[VinceAngelo]

Not shapefile, st_geometry.

'ldd' can be used to look for missing DLLs, but Oracle sometimes stacks their deck
with stub libraries (which have the necessary entrypoints for the DLL to resolve,
but no working functions).  It looks like your LD_LIBRARY_PATH is incomplete or
your install is.

It may be the ST_GEOMETRY function which is failing, but it's not the ST library's fault.

- V

[GeoffLau]

Hi Vince,

Just to close this one off, it was a faulty library.

We've upgraded to ARC SDE 10.1 and Oracle 11.2.0.3.0. I sourced a new shapelib library from http://support.esri.com/en/downloads/patches-servicepacks/view/productid/66/metaid/1914#choose-produ... and now the xml functions work nicely.

Regards,

Geoff