2.2 Configuring EXTPROC for Embedded R Execution

An external procedure is a procedure invoked from a program that is written in a different language. Oracle Database uses an external procedure agent named extproc to support external procedures. OML4R uses extproc to support embedded R execution.

2.2.1 About EXTPROC

When an application invokes an external procedure, Oracle Database starts an extproc agent. The application uses the network connection established by Oracle Database to pass instructions to the agent for executing the procedure. The agent loads a DLL or shared library, runs the external procedure, and passes back to the application any values returned by the external procedure.

2.2.2 About EXTPROC Configuration for OML4R

OML4R uses the default configuration of extproc.

The extproc agent is spawned directly by Oracle Database, and no configuration changes are required to either listener.ora or tnsnames.ora. If extproc is configured on the database listener, it overrides the default settings.

By default, extproc supports any external procedure call. If you want to allow only external procedure calls for OML4R, you can edit the EXTPROC_DLLS environment variable in ORACLE_HOME/hs/admin/extproc.ora.

The following statement on a Linux or UNIX system sets EXTPROC_DLLS to execute only external procedures for OML4R:

SET EXTPROC_DLLS=ONLY:$ORACLE_HOME/lib/ore.so

To allow extproc to service any external procedure, set EXTPROC_DLLS to ANY or simply leave it blank (the default).

Enable extproc tracing by doing the following:

  1. To your /extproc.ora file, add the following statement:
    SET TRACE_LEVEL=ON
  2. Restart the database.

    Traces for all extproc operations are now recorded in the log files in the $ORACLE_HOME/hs/log directory.

Beginning in R-3.3.0, for Linux 6 it is necessary to set LD_LIBRARY_PATH in $ORACLE_HOME/hs/admin/extproc.ora to the location of the R-core-extra RPM so that these libraries are found by the Oracle process running extproc. For example, the default location of the R-core-extra RPM is /usr/lib64/R/port/Linux-X64/lib. The extproc.ora entry for that location should be:

SET LD_LIBRARY_PATH=/usr/lib64/R/port/Linux-X64/lib

In Linux 7, setting LD_LIBRARY_PATH is not necessary because the required versions of these libraries are native to Linux 7 systems.

Note:

A database reboot is needed for changes in extproc.ora to take effect.

See Also:

"Default Configuration for External Procedures" in Oracle Database Net Services Administrator's Guide for details

2.2.3 Troubleshooting EXTPROC

Calling an OML4R embedded R function may result in an error if a database configuration problem exists.

If an attempt to call an OML4R embedded R function results in the following error, then the external procedure did not succeed:

ORA-28575: unable to open RPC connection to external procedure agent.

This error is often a database configuration problem. It may be caused by any of the following:

  • The OML4R user has not been granted RQADMIN role.

  • The Oracle listener is not running,

  • The Oracle listener configuration is incorrect, which may occur if the default external procedure configuration (which is recommended) is not being used.

  • Networking layer restrictions or issues exist.

  • Restrictions on external procedure calls are in force.