Sunday Dec 29, 2013

FAQ: Oracle R Enterprise and External Procedures

Oracle R Enterprise uses external procedures in Oracle Database to support embedded R execution. An external procedure, or extproc, is a procedure stored in a shared library that is called to perform special-purpose processing.  When ORE invokes an external procedure, Oracle Database starts an extproc agent and passes instructions to the agent for executing the procedure. The agent loads an ORE shared library or DLL, runs the external procedure in the database, and passes back the values returned by the external procedure to ORE.

With Oracle 11g, this all happens behind the scenes because the default configuration for extproc works out of the box. Occasionally, users experience problems with external procedures on their system or wish to modify configuration parameters. In this post, we've compiled the most commonly asked questions regarding external procedures with ORE.

1. How do I configure extproc for use with Oracle R Enterprise?

When you use the default configuration for external procedures, the extproc agent is spawned directly by Oracle Database and no listener is involved as it was for previous RDBMS versions. The parameters for external procedures may be configured by modifying the extproc.ora file located in the $ORACLE_HOME/hs/admin directory. For example, you may want to restrict extproc to certain libraries or specify environment variables for the extproc agent. Refer to the configuration parameters detailed in the Oracle Database Net Services Administrator's Guide for details.

2. When I try to use embedded R execution in Oracle R Enterprise, I receive the error: ORA-28575: unable to open RPC connection to external procedure agent.

In general, this error indicates that extproc did not succeed.  To start, this simple program will verify if extproc is working in Oracle Database independently of Oracle R Enterprise.

a. Create a C file test.c with the following:

  #include <stdio.h>
  #include <sys/types.h>
  #include <unistd.h>
  #include <stdlib.h>
  int negative(char* db, int n)
  {
        return -1*n;
  }


b. Create a shared library test.so by running:

  $ gcc -shared -fPIC -o test.so test.c

Copy the resulting shared library, test.so to $ORACLE_HOME/bin.

c. Grant dba privileges to scott

  $sqplus / as sysdba
  SQL> grant dba to scott;


d. Create an external procedure library test:

  $sqlplus scott/tiger
  SQL> CREATE OR REPLACE LIBRARY test AS '$ORACLE_HOME/bin/test.so';
       /

e.  Create function negative_it to run in the external procedure:

  SQL> CREATE OR REPLACE FUNCTION negative_it(x VARCHAR2, y BINARY_INTEGER)
       RETURN BINARY_INTEGER AS LANGUAGE C
       LIBRARY test
       NAME "negative"
       PARAMETERS(x STRING, y INT);
      /


f.  If extproc is working properly, the following value will be returned from the function:

  SQL> select negative_it('dummy', 1234) from dual;
       NEGATIVE_IT('DUMMY',1234)
       -------------------------------
                          -1234

If extproc is working correctly outside of Oracle R Enterprise, the above error may be caused by any of the following:

  • networking layer issues
  • incorrect listener configuration (if the default configuration is not being used)
  • the Oracle R Enterprise user has not been granted RQADMIN role (required for running embedded R)

Users are advised to refer to My Oracle Support for assistance with networking issues.  For listener configuration issues, consult "Configuring Oracle Net Services for External Procedures" in the Oracle Database Net Services Administrator's Guide for the required parameters. Refer to the Oracle R Installation and Administration Guide for information on roles and grants for ORE users.

3. How can I restrict external procedure calls to use Oracle R Enterprise only?

By default, extproc supports any external procedure call.  To maximize security, you may want to allow only external procedure calls for Oracle R Enterprise. To do this, 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 Oracle R Enterprise:

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


On Windows, the equivalent statement is:

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

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

5. I've configured Oracle Wallet for use with Oracle R Enterprise, but when I attempt to connect to Oracle Database my session hangs.

This may be caused by specifying SQLNET.WALLET_OVERRIDE=TRUE in the sqlnet.ora configuration file.  This file is located in: $ORACLE_HOME/network/admin/sqlnet.ora:

   WALLET_LOCATION =
       (SOURCE = (METHOD = FILE)
       (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/product/wallet)))
        SQLNET.WALLET_OVERRIDE = TRUE



For Oracle Wallet clients wanting to override Operating System credentials for database authentication, SQLNET.WALLET_OVERRIDE can be set to TRUE. The default value for SQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials.

Because setting SQLNET.WALLET_OVERRIDE=TRUE overrides Operating System authentication, the database does not recognize the user attempting to execute the external procedure and extproc fails, causing the hanging behavior. This can be solved by creating a userid and password when creating a password store for the Oracle Wallet client.

Note: The advice in this post applies only if the default Oracle server configuration for extproc is in use.  If the Oracle listener is configured for extproc, the listener settings will override the default configuration. See the Oracle Database Net Services Reference Guide for details.



About

The place for best practices, tips, and tricks for applying Oracle R Enterprise, Oracle R Distribution, ROracle, and Oracle R Advanced Analytics for Hadoop in both traditional and Big Data environments.

Search

Archives
« December 2013 »
SunMonTueWedThuFriSat
1
2
3
5
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
30
31
    
       
Today