Error ORA-28575 with ST_GEOMETRY

[JhonGarcia]

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.

[VinceAngelo]

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

[JhonGarcia]

The version of ArcGIS is 10.1

[VinceAngelo]

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

[ForrestJones · Accepted solution]

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

[JhonGarcia]

Hi FJ, it works thanks.

thank you very much.