topic Error ORA-28575 with ST_GEOMETRY in Data Management Questions

Error ORA-28575 with ST_GEOMETRY

JhonGarcia — Tue, 03 Dec 2013 19:29:39 GMT

Hi i am configuring the library but when i try to execute the next:

SELECT st_pointfromtext ('point (10.02 20.01)', 0) FROM dual;

i recived this error: ora-28575 unable to open rpc connection to external procedure agent, when i execute:

SELECT st_point(0,0,0) FROM dual;

it works ok with users differents to SDE for example with SYS, but with SDE i have the same error.

i have Oracle 11.2.0.3 64 bits installed on Oracle Enterprise Linux 6 x64.

configuration of extproc:

SET EXTPROC_DLLS=ONLY:/u01/app/oracle/product/11.3.0/db_1/lib/libst_shapelib.so
SET LD_LIBRARY_PATH=/u01/app/oracle/product/11.3.0/db_1/lib

########################configuration of listener:
Test environment

LISTENER_PRT =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1528))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
    )
)

SID_LIST_LISTENER_PRT =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.3.0/db_1)
      (PROGRAM = extproc)
    )
)

###########################
Production environment

LISTENER_PROD =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1533))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1533))
    )
)

SID_LIST_LISTENER_PROD =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.3.0/db_1)
      (PROGRAM = extproc)
    )
)

########################configuration of tnsnames
Test environment
SGCPRT =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1528))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sgcprt)
    )
)

EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1528)
)
)
(CONNECT_DATA =
(SID = PLSExtProc)(PRESENTATION = RO)))

###########################
Production environment

SGCPROD =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sgcprod)
    )
)

EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1533)
)
)
(CONNECT_DATA =
(SID = PLSExtProc)(PRESENTATION = RO)))

LISTENER_PRT =
(ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1528))

LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = vir11g)(PORT = 1533))

######################################Creating library with SDE user:

CREATE OR REPLACE LIBRARY st_shapelib AS '/u01/app/oracle/product/11.3.0/db_1/lib/libst_shapelib.so';

Thanks for your help.

Re: Error ORA-28575 with ST_GEOMETRY

VinceAngelo — Tue, 03 Dec 2013 20:37:21 GMT

You didn't say which version of ArcGIS you're using.

The ST_POINT constructor does not require DLL support, but the WKT parser does, meaning your
listener is not configured correctly.

Use of Oracle 11.3 might be an issue.

- V

Re: Error ORA-28575 with ST_GEOMETRY

JhonGarcia — Wed, 04 Dec 2013 14:00:13 GMT

The version of ArcGIS is 10.1

Re: Error ORA-28575 with ST_GEOMETRY

VinceAngelo — Wed, 04 Dec 2013 14:33:11 GMT

ArcGIS 10.1 does not support Oracle 11.3. If you've successfully used the same configuration
on a 11gR2 listener and it still doesn't work, the issue might be 11gR3.

- V

Re: Error ORA-28575 with ST_GEOMETRY

ForrestJones — Wed, 04 Dec 2013 15:21:14 GMT

Hi Jhon,

You may also want to consider setting it up with the extproc.ora file in the $ORACLE_HOME/hs/admin directory instead of using the listener.

From the oracle doc:

"The default configuration for external procedures does not require a network listener to work with Oracle Database and the extproc agent. The extproc agent is spawned directly by Oracle Database and eliminates the risks that extproc might be spawned by Oracle Listener unexpectedly. This default configuration is recommended for maximum security. "

From:
http://docs.oracle.com/cd/E18283_01/network.112/e10836/advcfg.htm

Re: Error ORA-28575 with ST_GEOMETRY

JhonGarcia — Wed, 04 Dec 2013 18:33:55 GMT

Hi FJ, it works thanks.

thank you very much.