ST_SHAPELIB + Oracle 19c + ArcGIS Enterprise 10.6.1 = ORA-28578 error

[Jean-FrançoisMourgues]

I'm upgrading our database from Oracle 11G to Oracle 19C. Our ArcGIS version still the same : Enterprise 10.6.1

I'm getting a problem when using the SHAPELIB library (same that was installed in the Oracle 11C server :

ORA-28578: erreur de protocole lors du callback depuis une procédure externe
ORA-06512: à "SDE.ST_GEOMETRY_SHAPELIB_PKG", ligne 12
ORA-06512: à "SDE.ST_POINT", ligne 181
28578. 00000 -  "protocol error during callback from an external procedure"
*Cause:    An internal protocol error occurred while trying to execute a
           callback to the Oracle server from the user's 3GL routine.
*Action:   Contact Oracle customer support.

Does anyone knows how to solve this problem?

[George_Thompson]

You may need to install the patch for 10.6.1 and make sure that you grab the latest st_shape lib file from that release; https://desktop.arcgis.com/en/system-requirements/10.6/database-requirements-oracle.htm

Patch: https://support.esri.com/en/download/7734

You may also need to recompile the SDE schema after the package update.

[Jean-FrançoisMourgues]

Thanks for your answer!

We did all the actions you recommend :

- Install patch 10.6.1

- Download and install the latest libst_shepelib.so from https://my.esri.com/#/downloads/ArcGIS%20Pro/2.2 (we download the version corresponding to ArcGIS Pro 2.2 as detailed in the link you provided)

- Recompile the SDE schema with the command

EXECUTE sys.utl_recomp.recomp_serial('SDE');

After all these actions, when executing query :

select sde.ST_Buffer(sde.st_point(1006849.0, 1874381.0, 2), .1) from dual;

this error still there:

ORA-28578: erreur de protocole lors du callback depuis une procédure externe
ORA-06512: à "SDE.ST_GEOMETRY_SHAPELIB_PKG", ligne 12
ORA-06512: à "SDE.ST_POINT", ligne 181
28578. 00000 -  "protocol error during callback from an external procedure"
*Cause:    An internal protocol error occurred while trying to execute a
           callback to the Oracle server from the user's 3GL routine.
*Action:   Contact Oracle customer support.

[George_Thompson]

So it looks like there is an EXTPROC issue. Was that updated on the new Oracle19c instance?

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/configure-oracle-extproc.htm

[Jean-FrançoisMourgues]

As per below ESRI + oracle documentation

https://desktop.arcgis.com/fr/arcmap/10.5/manage-data/gdbs-in-oracle/configure-oracle-extproc.htm

https://desktop.arcgis.com/fr/arcmap/latest/manage-data/gdbs-in-oracle/configure-oracle-extproc.htm

https://docs.oracle.com/en/database/oracle/oracle-database/19/ntqrf/registering-an-external-procedur...

We copied the library files at their location

[oracle@zeaca50f arcsde]$ ll

total 21828

-rwxr-xr-x. 1 oracle oinstall  7579206 Sep 27  2016 libst_raster_ora.so

-rwxr-xr-x. 1 oracle oinstall  2359296 Nov  5 13:40 libst_shapelib.so

-rwxr-xr-x. 1 oracle oinstall 11107430 Oct  4 15:40 libst_shapelib.so.old

Then we updated all config files as below :

listener.ora : adding this bloc
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /logiciels/oracle/oradb/19.5.0/oh1)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=/oierdf/appli/SIK/arcsde/libst_shapelib.so")
)

tnsnames.ora : adding this bloc
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(Key = LISTENER))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

extproc.ora :

SET EXTPROC_DLLS=ONLY:/oierdf/appli/SIK/arcsde/libst_shapelib.so:/oierdf/appli/SIK/arcsde/libst_raster_ora.so

Once all those files were configured, we ran the following

-- As user SDE :

SQL> CREATE or REPLACE LIBRARY ST_SHAPELIB AS '/oierdf/appli/SIK/arcsde/libst_shapelib.so';

SQL> ALTER PACKAGE sde.st_geometry_shapelib_pkg COMPILE REUSE SETTINGS;

-- As user SYS :

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

--Finally we still receive same error :

-- As user SDE

SQL> select sde.ST_Buffer(sde.st_point(1006849.0, 1874381.0, 2), .1) from dual;

select sde.ST_Buffer(sde.st_point(1006849.0, 1874381.0, 2), .1) from dual

                     *

ERROR at line 1:

ORA-28578: protocol error during callback from an external procedure

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12

ORA-06512: at "SDE.ST_POINT", line 181

[George_Thompson]

I have not had to update the listener or TNSNAMES file for the EXTPROC in many years. The latest help does not say to modify those files.

If you remove the changes, does it correct the issue?

[Jean-FrançoisMourgues]

At first we tried only what is mentioned in your suggested documentation.

https://desktop.arcgis.com/fr/arcmap/latest/manage-data/gdbs-in-oracle/configure-oracle-extproc.htm

but result was exactly the same.
That is why we tried further with updates of tnsnames.ora and listener.ora

[George_Thompson]

Understand.

The last test that I would try is update the EXTPROC.ora to

• SET EXTPROC_DLLS=ANY

Then retest, I have seen where you have to restart the listener and / or Oracle SID for the change to pick up.

Otherwise, I would recommend contacting Esri technical support for potential guidance.

[Jean-FrançoisMourgues]

George, thx pour your reply.
I tried with SET EXTPROC_DLLS=ANY but same issue again.

However note that I haven't restarted the listener. Do you confirm it is mandatory?

--Sebastian

[George_Thompson]

I can say that is not mandatory, just something I have seen in the past. No promise that it will fix the issue.

Again, it should not have been modified to make this work.