ST_GEOMETRY problem

GabitAlmenbetov

Hi guys,

I get following error when try to select from gdb_items_vw:

select * from gdb_items_vw;
ERROR:
ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1339
ORA-06512: at "SDE.ST_GEOM_UTIL", line 760

select st_point(0,0,0) from dual
ERROR at line 1:
ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_POINT", line 176

We have Oracle Database 11.2.0.3 installed at RHEL and ArcSDE 10SP5 at Windows.

After some reading get confused how should I point to correct user library, the current shows this:

SELECT * FROM USER_LIBRARIES;
LIBRARY_NAME       FILE_SPEC  D STATUS
------------------------------ -------------------------------------------------- - -------
ST_SHAPELIB       C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll  Y VALID

Should i download ArcSDE for Linux and extract from it Linux versions of library files and put it at Linux Server where Oracle database is?

OR just update extproc.ora with C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll, because it didn't worked - the same result???

GabitAlmenbetov

Hi All,

[PS. Thanks Vince Angelo, I didn't mean and don't at all and all times to offend anybody.]

So my solution was from:
1) ArcGIS Desktop

2) ST_GEOMETRY  problem on Oracle RDBMS 11g R2 11.2.0.3

So, my steps:

1) Downloaded ArcSDE10_UNIX_121100.iso, unpacked and get required files.

2) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [6] - Copy the st_shape shared library used by EXTPROC to the Oracle server.

[root@host /]# mkdir /arcsdehome
[root@host /]# chown -Rf oracle.oinstall /arcsdehome/
[root@host /]# cp -pr /tmp/sdeexe100/lib/libst_* /arcsdehome/
[root@host /]# ll /arcsdehome/
total 12920
-rw-r--r--. 1 oracle oinstall 7572806 Jul  2 11:23 libst_raster_ora.so
-rw-r--r--. 1 oracle oinstall 5652930 Jul  2 11:23 libst_shapelib.so
[root@host /]# 


[oracle@host ~]$ cat .bash_profile
export SDEHOME=/arcsdehome
export LD_LIBRARY_PATH=$SDEHOME:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@host ~]$ echo $SDEHOME


[oracle@host ~]$ . ~/.bash_profile
[oracle@host ~]$ echo $SDEHOME
/arcsdehome
[oracle@host ~]$ 

3) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [7] - Create the Oracle library entries for these ArcSDE libraries with the following SQL command as the ArcSDE administrative user (the sde user).

02-JUL-15:SDE()@dbname>column file_spec format a50
02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;
LIBRARY_NAME       FILE_SPEC  D STATUS
------------------------------ -------------------------------------------------- - -------
ST_SHAPELIB       C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll  Y VALID
1 row selected.
Elapsed: 00:00:00.03
02-JUL-15:SDE()@dbname>
02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';
  2  /
CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';
*
ERROR at line 1:
ORA-01031: insufficient privileges

02-JUL-15:SYS(AS SYSDBA)@dbname>grant create library to sde;
Grant succeeded.
02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';
  2  /
Library created.
Elapsed: 00:00:00.15
02-JUL-15:SDE()@dbname>column file_spec format a50
02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;
LIBRARY_NAME       FILE_SPEC  D STATUS
------------------------------ -------------------------------------------------- - -------
ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID
1 row selected.
Elapsed: 00:00:00.03
02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY libst_raster AS '/arcsdehome/libst_raster_ora.so'
  2  /
Library created.
Elapsed: 00:00:00.07
02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;
LIBRARY_NAME       FILE_SPEC  D STATUS
------------------------------ -------------------------------------------------- - -------
LIBST_RASTER       /arcsdehome/libst_raster_ora.so  Y VALID
ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID
2 rows selected.
Elapsed: 00:00:00.01
02-JUL-15:SDE()@dbname>

4) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [8] - Configure the Oracle EXTPROC so it can find the st_shapelib and libst_raster_ora libraries.

[8.a] - Add the following entry in the tnsnames.ora on the remote database server: Actually I have added per official documentation but then removed per forum note(links are above).

[8.b] - Add the following entry to the SID_LIST in listener.ora on the remote database server:Actually I have added per official documentation but then removed per forum note(links are above)

[8.c] - Modify your extproc.ora as follows

SET EXTPROC_DLLS=ONLY:/arcsdehome/libst_shapelib.so:/arcsdehome/libst_raster_ora.so

5) Compile st_geometry_shapelib_pkg.

02-JUL-15:SYS(AS SYSDBA)@dbname>alter package sde.st_geometry_shapelib_pkg compile reuse settings;
Package altered.
Elapsed: 00:00:00.39

6) Creating the library causes dependent object to become invalid. Execute the following command as the SDE user (you may need to grant the execute privilege on sys.utl_recomp to user SDE)

02-JUL-15:SYS(AS SYSDBA)@dbname>GRANT EXECUTE ON SYS.UTL_RECOMP TO SDE;
Grant succeeded.
Elapsed: 00:00:00.03
02-JUL-15:SYS(AS SYSDBA)@dbname>conn sde/************
Connected.
02-JUL-15:SDE()@dbname>EXECUTE sys.utl_recomp.recomp_serial('SDE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.56
02-JUL-15:SDE()@dbname>select object_name, object_type from user_objects where status = 'INVALID';
no rows selected
Elapsed: 00:00:00.03
02-JUL-15:SDE()@dbname>

Hope this procedure will help you in configuring your mixed environment!!!

Izgi niyetpen,

Gabit Almenbetov