Quest to Counting Adds/Deletes Table

Discussion created by Adityaraj on Apr 2, 2018
Latest reply on Apr 2, 2018 by Adityaraj


I, Geodata Admin, desperately trying to execute the script posted here - developer-support/delta-table-record-count.sql at master · Esri/developer-support · GitHub using the geodatabase administrative account outside of ArcGIS clients from the Oracle SQL Developer. 

Our Oracle Enterprise 11.2g database is installed on AIX OS server. And, the ArcSDE 10.4 is installed on the Microsoft Windows 2012 server.  

Now, when I execute the SQL from the SQL Developer to populate the required count in adds and deletes tables, I am getting the following error message:

ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1325
ORA-06512: at "SDE.ST_GEOM_UTIL", line 822
ORA-06512: at line 18
28595. 00000 - "Extproc agent : Invalid DLL Path"
*Cause: The path of DLL supplied for the extproc execution is invalid.
*Action: Check if the DLL path is set properly using the EXTPROC_DLLS
environment variable

After carrying out some more research, I found following two threads and help doc explaining the cause of the issue and proposing some solution for the same - 


1)Error: ORA-28595: Extproc agent : Invalid DLL Path 

2)ST_GEOMETRY  problem on Oracle RDBMS 11g R2 

3) Configure the extproc to access ST_Geometry in Oracle—Help | ArcGIS Desktop 


After reviewing both the threads and the help document, DBA (walking god in the workplace) and I took on this challenge and performed the following steps to make an attempt to resolve the issue.


1) Run the following SQL script to check the user_libraries for SDE user:

SQL> SELECT library_name, file_spec FROM user_libraries;

Result - we found no records.


2) So, we copied the file (as shown below) from our dearly Windows Server where the ArcSDE is installed,



3) We pasted the above file to our precise Linux database server.  


4) As there was no library under the SDE User, my DBA created one and pointed to the path where the above file saved. 


5) We took a backup the extproc.ora file.


6) Opened the magical extproc.ora file and set the path to file.




7) Since no location was set to ST_SHAPELIB file, we created ST_SHAPELIB library by running the following SQL code




8) Finally, we recompiled the sde.st_geometry_shapelib_pkg package using the following code:


ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;


9) We checked the library and its path for the SDE user for confirmation. We got the following results:





10) My DBA told me that let's try to run the original SQL Script again. I kept my fingers crossed,  told him Ok


11) We ran the SQL Script and got the following error message:


Error report -
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Permission denied
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1325
ORA-06512: at "SDE.ST_GEOM_UTIL", line 822
ORA-06512: at line 18
06520. 00000 - "PL/SQL: Error loading external library"
*Cause: An error was detected by PL/SQL trying to load the external
library dynamically.
*Action: Check the stacked error (if any) for more details.


12) My DBA and I are pulling each other's hair out to resolve the issue.


Any observations, feedback from the wise GISers in the community will be appreciated.



Ambitious Geodata Admin