Quest to Counting Adds/Deletes Table

AdityarajChavada — Mon, 02 Apr 2018 07:54:47 GMT

Hi!

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 11.2.0.3

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 libst_shapelib.so 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.

SET EXTPROC_DLLS=ONLY:UNIX PATH to the libst_shapelib.so FILE

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

CREATE or REPLACE LIBRARY ST_SHAPELIB AS UNIX PATH to the libst_shapelib.so FILE;

😎 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.

Regards,

Ambitious Geodata Admin

Re: Quest to Counting Adds/Deletes Table

Asrujit_SenGupta — Sun, 12 Dec 2021 00:32:33 GMT

Any clients that need access to the library (including web services) must reconnect to the database.

The last piece of advice on the WebHelp link...did you follow that?

Also, to check whether the Extrpoc was configured correctly, you can try the below SQL:

SQL> select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0))
--------------------------------------------------------------------------------

POINT  ( 10.00000000 10.00000000)‍‍‍‍‍‍

The error that you receive, mentions ORA-06522: Permission denied, so does the user have the necessary permissions to access the file in that directory?

Re: Quest to Counting Adds/Deletes Table

AdityarajChavada — Mon, 02 Apr 2018 12:51:48 GMT

Thanks for your feedback. We overlooked some details. We pointed the full path with the file name for ST_SHAPELIB file. Also, as per the other thread, we ran the following code to grant some privileges to the SDE user .

i.As sys user, grant execute on SYS.UTL_RECOMP TO SDE. SQL> GRANT EXECUTE ON SYS.UTL_RECOMP TO SDE;
ii.As SDE user, recompile. SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

We did the above changes and the sample script is now working but add/deletes SQL script posted on GITHUB - developer-support/delta-table-record-count.sql at master · Esri/developer-support · GitHub has issue parsing XML. Need to dig into that issue now.

topic Re: Quest to Counting Adds/Deletes Table in Data Management Questions

Quest to Counting Adds/Deletes Table

Re: Quest to Counting Adds/Deletes Table

Re: Quest to Counting Adds/Deletes Table