10 Embedded R Execution

Embedded R execution in OML4R enables you to run user-defined R functions, also referred to as scripts in R sessions that run in the Database environment.

These topics discuss Embedded R execution:

• About Embedded R Execution
  In OML4R, embedded R execution is the ability to run R scripts in R engines that are dynamically spawned and managed by the database environment.
• Datastore and Script Repository Views supporting Embedded R Execution
  OML4R includes a number of database views that contain information about datastores and about the scripts and user-defined functions in the datastores. You can use these views with the embedded R execution APIs to work with the datastores and their contents.
• R Interface for Embedded R Execution
  Oracle Machine Learning for R provides functions that call R scripts that run in one or more R engines that are embedded in the Oracle Database.
• SQL Interface for Embedded R Execution
  SQL Interface for Oracle Machine Learning for R Embedded R execution allows you to run R functions in production database applications.
• SQL API for Embedded R Execution with On-premises Database
  The OML4R SQL APIs comprise SQL table functions for executing R functions in one or more embedded R sessions on the OML4R Server database, and PL/SQL procedures for managing OML4R datastores and for managing scripts in the OML4R script repository.
• SQL API for Embedded R Execution with Autonomous Database
  The SQL API for Embedded R Execution with Autonomous Database provides SQL interfaces for setting authorization tokens, managing access control list (ACL) privileges, executing R scripts, and synchronously and asynchronously running jobs.

10.1 About Embedded R Execution

In OML4R, embedded R execution is the ability to run R scripts in R engines that are dynamically spawned and managed by the database environment.

You can store R scripts in the OML4R script repository and to call such scripts with embedded R functions. When called, a script runs in one or more R engines that run on the database server. OML4R provides both an R interface and a SQL interface for embedded R execution on Oracle Database, R, SQL, and REST interfaces on Autonomous Database. From the same R script you can get structured data, an XML representation of R objects and images, and even PNG images through a BLOB column in a database table.

The following topics describe embedded R execution:

• Benefits of Embedded R Execution
  Embedded R Execution has the following benefits:
• APIs for Embedded R Execution
  Oracle Machine Learning for R provides R, SQL and REST interfaces for Embedded R Execution.
• Support for Parallel Execution
  Some of the Oracle Machine Learning for R Embedded R Execution functions support the use of parallel execution in the database.

10.1.1 Benefits of Embedded R Execution

Embedded R Execution has the following benefits:

• Eliminates moving data from the Oracle Database server to your local R session.

  As well as being more secure, the transfer of database data between Oracle Database and an internal R engine is much faster than to a separate client R engine.

• Uses the database server to start, manage, and control the running of R scripts in R engines running on the server.

• Leverages the memory and processing power of the database server machine for R engine running, which provides better scalability and performance.

• Enables data-parallel and task-parallel execution of user-defined R functions.

• Provides parallel simulations capability.

• Allows the use of open source CRAN packages in R scripts running on the database server in the database environment.

• Provides the ability to develop and operationalize comprehensive scripts for analytical applications in a single step, without leaving the R environment.

  You can directly integrate R scripts used in exploratory analysis into application tasks. You can also immediately run R scripts in production to drastically reduce time to market by eliminating porting and enabling instantaneous updates of changes to application code.

• Running R scripts from SQL enables integration of R script results with Oracle Analytics Server, Oracle Analytics Cloud, Oracle APEX, and other SQL-enabled tools for structured data, R objects, and images.

10.1.2 APIs for Embedded R Execution

Oracle Machine Learning for R provides R, SQL and REST interfaces for Embedded R Execution.

The following table lists the R functions and the equivalent SQL functions and procedures for Embedded R Execution and OML4R script repository management. The function f refers to a named R function or an R function defined in a script in the OML4R script repository.

Table 10-1 R and SQL APIs for Embedded R Execution

R API	SQL API	Description
ore.doEval	rqEval2	Runs f with no data input.
ore.tableApply	rqTableEval2	Runs f by passing all rows of the provided input ore.frame as the first argument of f. Provides the first argument of f as a data.frame.
ore.groupApply	rqGroupEval2 This function must be explicitly defined by the user.	Runs f by partitioning data according to the values of a grouping column. Provides each data partition as a data.frame in the first argument of f. Supports running of each f call in parallel in the pool of database server-side R engines.
ore.rowApply	rqRowEval2	Runs f by passing a specified number of rows (a chunk) of the provided input ore.frame. Provides each chunk as a data.frame in the first argument of f. Supports running of each f call in parallel in the pool of database server-side R engines.
ore.indexApply	No equivalent.	Runs f with no automatic transfer of data but provides the index of the invocation, 1 through n, where n is the number of times to run the function. Supports running of each f call in parallel in the pool of database server-side R engines.
ore.grant	rqGrant	Grants read privilege access to a datastore or script.
ore.revoke	rqRevoke	Revokes read privilege access to a datastore or script.
ore.scriptCreate	sys.rqScriptCreate	Adds the provided R function into the OML4R script repository with the provided name.
ore.scriptDrop	sys.rqScriptDrop	Removes the named R function from the OML4R script repository.
ore.scriptList	ALL_RQ_SCRIPTS USER_RQ_SCRIPTS	Lists information about scripts.
ore.scriptLoad	No equivalent.	Loads the R function of a script into the R environment.

10.1.3 Support for Parallel Execution

Some of the Oracle Machine Learning for R Embedded R Execution functions support the use of parallel execution in the database.

The ore.groupApply, ore.rowApply, rqGroupEval2, and rqRowEval2 functions support data-parallel execution and the ore.indexApply function supports task-parallel execution. This parallel execution capability enables a script to take advantage of high-performance computing hardware such as an Oracle Exadata Database Machine.

The parallel argument of the ore.groupApply, ore.rowApply, and ore.indexApply functions specifies the degree of parallelism to use in the Embedded R Execution. The value of the argument can be one of the following:

• A positive integer greater than or equal to 2 for a specific degree of parallelism

• FALSE or 1 for no parallelism

• TRUE for the default parallelism of the data argument

• NULL for the database default for the operation

The default value of the argument is the value of the global option ore.parallel or FALSE if ore.parallel is not set.

A user-defined R function invoked using ore.doEval or ore.tableApply is not executed in parallel. The function executes in a single R engine.

For the rqGroupEval2, and rqRowEval2 functions, the degree of parallelism is specified by a PARALLEL hint in the input cursor argument.

In data-parallel execution for the ore.groupApply and rqGroupEval2 functions, one or more R engines perform the same R function, or task, on different partitions of data. This functionality enables the building of large numbers of models, for example building tens or hundreds of thousands of predictive models, one model per customer.

In data-parallel execution for the ore.rowApply and rqRowEval2 functions, one or more R engines perform the same R function on disjoint chunks of data. This functionality enables scalable model scoring and predictions on large data sets.

In task-parallel execution for the ore.indexApply function, one or more R engines perform the same or different calculations, or task. A number, associated with the index of the execution, is provided to the function. This functionality is valuable in a variety of operations, such as in performing simulations.

Oracle Database handles the management and control of potentially multiple R engines at the database server, automatically partitioning and passing data to R engines executing in parallel. It ensures that all of the R function executions for all of the partitions complete; if not, the OML4R function returns an error. The result from the execution of each user-defined embedded R function is gathered in an ore.list. This list remains in the database until the user requires the result.

Embedded R execution also allows for data-parallel execution of user-defined R functions that may use functions from an open source R package from The Comprehensive R Archive Network (CRAN) or other third-party R package. However, third-party packages do not leverage in-database parallelism and are subject to the parallelism constraints of R. Third-party packages can benefit from the data-parallel and task-parallel execution supported in Embedded R Execution.

10.2 Datastore and Script Repository Views supporting Embedded R Execution

OML4R includes a number of database views that contain information about datastores and about the scripts and user-defined functions in the datastores. You can use these views with the embedded R execution APIs to work with the datastores and their contents.

View	Description
ALL_RQ_DATASTORES	Describes the datastores available to the current user.
ALL_RQ_SCRIPTS	Describes the scripts in the OML4R script repository that are available to the current user.
RQUSER_DATASTORECONTENTS	Contains information about the contents of Oracle Machine Learning for R datastores.
RQUSER_DATASTORELIST	Contains information about Oracle Machine Learning for R datastores.
USER_RQ_DATASTORE_PRIVS	Describes the datastores and the users to whom the current user has granted read privilege access.
USER_RQ_DATASTORES	Describes datastores created by the current user.
USER_RQ_SCRIPT_PRIVS	Describes the scripts in the OML4R script repository to which the current user has granted read access and the users to whom access has been granted.
USER_RQ_SCRIPTS	Describes the scripts in the OML4R script repository that are owned by the current user.

• ALL_RQ_DATASTORES
  ALL_RQ_DATASTORES describes the datastores available to the current user.
• ALL_RQ_SCRIPTS
  ALL_RQ_SCRIPTS describes the scripts in the OML4R script repository that are available to the current user.
• RQUSER_DATASTORECONTENTS
  RQUSER_DATASTORECONTENTS contains information about the contents of Oracle Machine Learning for R datastores.
• RQUSER_DATASTORELIST
  RQUSER_DATASTORELIST contains information about Oracle Machine Learning for R datastores.
• USER_RQ_DATASTORE_PRIVS
  USER_RQ_DATASTORE_PRIVS describes the datastores and the users to whom the current user has granted read privilege access.
• USER_RQ_DATASTORES
  USER_RQ_DATASTORES describes datastores created by the current user.
• USER_RQ_SCRIPT_PRIVS
  USER_RQ_SCRIPT_PRIVS describes the scripts in the OML4R script repository to which the current user has granted read access and the users to whom access has been granted.
• USER_RQ_SCRIPTS
  USER_RQ_SCRIPTS describes the scripts in the OML4R script repository that are owned by the current user.

10.2.1 ALL_RQ_DATASTORES

ALL_RQ_DATASTORES describes the datastores available to the current user.

Column	Datatype	Null	Description
DSOWNER	VARCHAR2(256)	NOT NULL	The owner of the datastore.
DSNAME	VARCHAR2(128)	NOT NULL	The name of the datastore.
NOBJ	NUMBER	NOT NULL	The number of objects in the datastore.
DSSIZE	NUMBER	NOT NULL	The size of the datastore.
CDATE	DATE	NOT NULL	The creation date of the datastore.
DESCRIPTION	VARCHAR2(2000)	NULL allowed	A description of the datastore.
GRANTABLE	VARCHAR2(1)	NOT NULL	Whether read privilege access to the datastore can be granted by the owner to another user.

10.2.2 ALL_RQ_SCRIPTS

ALL_RQ_SCRIPTS describes the scripts in the OML4R script repository that are available to the current user.

Column	Datatype	Null	Description
OWNER	VARCHAR2(256)	NOT NULL	The owner of the script.
NAME	VARCHAR2(128)	NOT NULL	The name of the script.
SCRIPT	CLOB	NOT NULL	The R function of the script.

10.2.3 RQUSER_DATASTORECONTENTS

RQUSER_DATASTORECONTENTS contains information about the contents of Oracle Machine Learning for R datastores.

Column	Datatype	Null	Description
DSNAME	VARCHAR2(128)	NOT NULL	The name of the datastore.
OBJNAME	VARCHAR2(128)	NOT NULL	The names of the objects in the datastore.
CLASS	VARCHAR2(128)	NOT NULL	The R class of an object.
DSSIZE	NUMBER	NOT NULL	The size of an object.
LENGTH	NUMBER	NOT NULL	The size of an object.
NROW	NUMBER	NULL allowed	The number of rows in an object.
NCOL	NUMBER	NULL allowed	The number of columns in an object.

10.2.4 RQUSER_DATASTORELIST

RQUSER_DATASTORELIST contains information about Oracle Machine Learning for R datastores.

Column	Datatype	Null	Description
DSNAME	VARCHAR2(128)	NOT NULL	The name of the datastore.
NOBJ	NUMBER	NOT NULL	The number of objects in a datastore.
DSSIZE	NUMBER	NOT NULL	The size of the datastore.
CDATE	DATE	NOT NULL	The date the datastore was created.
DESCRIPTION	VARCHAR2(2000)	NULL allowed	The description of the datastore.

10.2.5 USER_RQ_DATASTORE_PRIVS

USER_RQ_DATASTORE_PRIVS describes the datastores and the users to whom the current user has granted read privilege access.

Column	Datatype	Null	Description
DSNAME	VARCHAR2(128)	NOT NULL	The name of a datastore.
GRANTEE	VARCHAR2(30)	NOT NULL	The user to whom read privilege access has been granted.

10.2.6 USER_RQ_DATASTORES

USER_RQ_DATASTORES describes datastores created by the current user.

Column	Datatype	Null	Description
DSNAME	VARCHAR2(128)	NOT NULL	The name of a datastore.
NOBJ	NUMBER	NOT NULL	The number of objects in the datastore.
DSSIZE	NUMBER	NOT NULL	The size of the datastore.
CDATE	DATE	NOT NULL	The creation date of the datastore.
DESCRIPTION	VARCHAR2(2000)	NULL allowed	A description of the datastore.
GRANTABLE	VARCHAR2(1)	NOT NULL	Whether read privilege access to the datastore can be granted by the owner to another user.

10.2.7 USER_RQ_SCRIPT_PRIVS

USER_RQ_SCRIPT_PRIVS describes the scripts in the OML4R script repository to which the current user has granted read access and the users to whom access has been granted.

Column	Datatype	Null	Description
NAME	VARCHAR2(128)	NOT NULL	The name of the script to which read access has been granted.
GRANTEE	VARCHAR2(128)	NOT NULL	The user to whom read access has been granted.

10.2.8 USER_RQ_SCRIPTS

USER_RQ_SCRIPTS describes the scripts in the OML4R script repository that are owned by the current user.

Column	Datatype	Null	Description
NAME	VARCHAR2(128)	NOT NULL	The name of the script.
SCRIPT	CLOB	NOT NULL	The R function of the script.

10.3 R Interface for Embedded R Execution

Oracle Machine Learning for R provides functions that call R scripts that run in one or more R engines that are embedded in the Oracle Database.

Other functions create and store an R function as a script in the OML4R script repository, grant or revoke read access to a script, list the available scripts, load a script function into the R environment, or drop a script from the repository. This section describes these functions in the following topics:

• Arguments for Functions that Run Scripts
  The Oracle Machine Learning for R Embedded R Execution functions ore.doEval, ore.tableApply, ore.groupApply, ore.rowApply, and ore.indexApply have arguments that are common to some or all of the functions.
• Manage Scripts in R
  Embedded R Execution functions can call R functions that are stored as scripts in the OML4R script repository. You can use the R functions described in this topic to create and manage scripts.
• Use the ore.doEval Function
  The ore.doEval function runs the specified input function using data that is generated by the input function.
• Use the ore.tableApply Function
  The ore.tableApply function calls an R script with an ore.frame as the input data.
• Use the ore.groupApply Function
  The ore.groupApply function calls an R script with an ore.frame as the input data.
• Use the ore.rowApply Function
  The ore.rowApply function calls an R script with an ore.frame as the input data.
• Use the ore.indexApply Function
  The ore.indexApply function executes the specified user-defined input function using data that is generated by the input function.

10.3.1 Arguments for Functions that Run Scripts

The Oracle Machine Learning for R Embedded R Execution functions ore.doEval, ore.tableApply, ore.groupApply, ore.rowApply, and ore.indexApply have arguments that are common to some or all of the functions.

Some of the functions also have an argument that is unique to the function. The following topics describe these arguments:

• Input Function to Run
  The Embedded R Execution functions all require an R function to apply during the running of the script.
• Optional and Control Arguments
  All of the Embedded R Execution functions take optional arguments, which can be named or not.
• Structure of Return Value
  Another argument that applies to all of the Embedded R Execution functions is FUN.VALUE.
• Input Data
  The ore.doEval and ore.indexApply functions do not automatically receive any data from the database.
• Parallel Execution
  The parallel argument specifies the level of parallelism to use in the Embedded R Execution of the input function.
• Unique Arguments
  The ore.groupApply, ore.indexApply, and ore.rowApply functions each take an argument that is unique to the function.

10.3.1.1 Input Function to Run

The Embedded R Execution functions all require an R function to apply during the running of the script.

You specify the input function with one of the following mutually exclusive arguments:

• FUN

• FUN.NAME (and optional FUN.OWNER)

The FUN argument takes a function object as a directly specified function or as one assigned to an R variable. Only a user with the RQADMIN role can use the FUN argument when invoking an embedded R function.

The FUN.NAME argument specifies a script that is stored in the OML4R R script repository. A stored script contains the function to apply when the script runs. Any OML4R user can use the FUN.NAME argument when invoking an embedded R function.

The optional argument FUN.OWNER specifies the owner of a script in the R script repository. The owner is the user who created the script. Use this argument only with the FUN.NAME argument. When FUN.NAME is a private script to which you have been granted read privilege access, use FUN.OWNNER to specify the owner of the private script.

The RQSYS schema is the owner of public scripts and the predefined OML4R scripts. For a list of the predefined scripts, run help(“ore.doEval”) and see the description of the FUN.NAME argument. If FUN.OWNNER is not specified or is NULL, then OML4R looks for the owner in the following order: user of the current session, RQSYS. If the owner of the script is not current user or RQSYS, then an error occurs.

Note:

The OML4R functions in the OREmodels package, ore.glm, ore.lm, ore.neural, and ore.randomForest, use the Embedded R Execution framework internally and cannot be used in Embedded R Execution functions.

10.3.1.2 Optional and Control Arguments

All of the Embedded R Execution functions take optional arguments, which can be named or not.

Oracle Machine Learning for R passes user-defined optional arguments to the input function. You can pass any number of optional arguments to the input function, including complex R objects such as models.

Arguments that start with ore. are special control arguments. OML4R does not pass them to the input function, but instead uses them to control what happens before or after the running of that function. The following control arguments are supported:

• ore.connect controls whether to automatically connect to OML4R inside the Embedded R Execution function. This is equivalent to doing an ore.connect call with the same credentials as the client session. The default value is FALSE.

  If an automatic connection is enabled, the following functionality occurs:

  • The embedded R script is connected to the database.

  • The connection has the same credentials as the session that calls the Embedded R SQL function.

  • The script runs in an autonomous transaction.

  • ROracle queries can work with the automatic connection.

  • OML4R transparency layer functionality is enabled in the Embedded script.

• ore.drop controls the input data. If the option value is TRUE, a one column data.frame is converted to a vector. The default value is TRUE.

• ore.envAsEmptyenv controls whether an environment referenced in an object is replaced with an empty environment during serialization. Some types of input parameters and returned objects, such as list and formula, are serialized before being saved to the database. If the control argument value is TRUE, then the referenced environment in the object is replaced with an empty environment whose parent is .GlobalEnv and the objects in the original referenced environment are not serialized. In some cases, this can significantly reduce the size of serialized objects. If the control argument value is FALSE, then all of the objects in the referenced environment are serialized and can be unserialized and recovered later. The default value is regulated by the global option ore.envAsEmptyenv.

• ore.na.omit controls the handling of missing values in the input data. If you specify ore.na.omit = TRUE, then rows or vector elements, depending on the ore.drop setting, that contain missing values are removed from the input data. If all of the rows in a chunk contain missing values, then the input data for that chunk will be an empty data.frame or vector. The default value is FALSE.

• ore.graphics controls whether to start a graphical driver and look for images. The default value is TRUE.

• ore.png.* specifies additional arguments for the png graphics driver if ore.graphics is TRUE. The naming convention for these arguments is to add an ore.png. prefix to the arguments of the png function. For example, if ore.png.height is supplied, argument height is passed to the png function. If not set, the standard default values for the png function are used.

See Also:

For more details about control arguments, see the online help displayed by invoking help(ore.doEval)

10.3.1.3 Structure of Return Value

Another argument that applies to all of the Embedded R Execution functions is FUN.VALUE.

If the FUN.VALUE argument is NULL, then the ore.doEval and ore.tableApply function can return a serialized R object as an ore.object class object, and the ore.groupApply, ore.indexApply, and ore.rowApply functions return an ore.list object. However, if you specify a data.frame or an ore.frame with the FUN.VALUE argument, then the function returns an ore.frame that has the structure of the specified data.frame or ore.frame object.

To specify that the corresponding output column of an ore.frame have a CLOB or BLOB database data type, you can apply the attribute ora.type to a column of a FUN.VALUE data.frame.

10.3.1.4 Input Data

The ore.doEval and ore.indexApply functions do not automatically receive any data from the database.

They simply run the function specified by the FUN or FUN.NAME argument. Any data needed by the input function is either generated within that function or explicitly retrieved from a data source such as Oracle Database, other databases, or flat files. The input function can load data from a file or a table using the ore.pull function or other transparency layer function.

The ore.tableApply, ore.groupApply, and ore.rowApply functions require a database table as input data. The table is represented by an ore.frame. You supply that data with an ore.frame object that you specify with the X argument, which is the first argument to the Embedded R Execution function. The Embedded R Execution function passes the ore.frame object to the user-defined input function as the first argument to that function.

Note:

The data represented by the ore.frame object passed to the user-defined R function is copied from Oracle Database to the database server R engine. The R memory limitations apply. If your database server machine has 32 GB RAM and your data table is 64 GB, then Oracle R Enterprise cannot load the data into the R engine memory.

10.3.1.5 Parallel Execution

The parallel argument specifies the level of parallelism to use in the Embedded R Execution of the input function.

The parallel argument is accepted by the ore.groupApply, ore.indexApply, and ore.rowApply functions.

10.3.1.6 Unique Arguments

The ore.groupApply, ore.indexApply, and ore.rowApply functions each take an argument that is unique to the function.

The ore.groupApply function takes the INDEX argument, which specifies the name of a column by which the rows of the input data are partitioned for processing by the input function.

The ore.indexApply function takes the times argument, which specifies the number of times to run the input function.

The ore.rowApply function tales the rows argument, which specifies the number of rows to pass to each invocation of the input function.

10.3.2 Manage Scripts in R

Embedded R Execution functions can call R functions that are stored as scripts in the OML4R script repository. You can use the R functions described in this topic to create and manage scripts.

The Embedded R Execution functions can take a FUN.NAME argument, which specifies the name of a script in the OML4R script repository. Scripts in the R script repository are also available through the SQL API for Embedded R Execution.

The R functions for managing scripts are the following:

• ore.grant

• ore.revoke

• ore.scriptCreate

• ore.scriptList

• ore.scriptLoad

• ore.scriptDrop

These functions are described in the following sections:

• Adding a Script

• Granting or Revoking Read Access to a Script

• Listing the Available Scripts

• Loading a Script into an R Environment

• Dropping a Script

For an example that uses these functions, see Example 10-1.

Adding a Script

To add an R function as a script in the OML4R script repository, use the ore.createScript function. To evoke this function, you must have the RQADMIN role. The ore.createScript function has the following syntax:

ore.scriptCreate(name, FUN, global, overwrite)

The arguments are the following:

Argument	Description
name	A name for the script in the OML4R script repository.
fun	An R function.
global	A logical value that indicates whether the script is public (global) or private. FALSE (the default) specifies that the script is not public and is visible only to the owner or to users to whom the owner has granted read privilege access; TRUE specifies that the script is public and therefore visible to all users.
overwrite	A logical value that indicates whether to replace the R function of the script with the function specified in by the fun argument. TRUE specifies replacing the function, if it exists; FALSE (the default) specifies that the existing contents cannot be replaced.

If overwrite = FALSE, an error condition occurs if a script by the same name already exists in the OML4R script repository; otherwise, ore.scriptCreate returns NULL.

Granting or Revoking Read Access to a Script

The creator of a script can use the ore.grant function to grant read access privilege to the script and the ore.revoke function to revoke that access. Those functions have the following syntax:

ore.grant(name, type = "rqscript", user)
ore.revoke(name, type = "rqscript", user)

The arguments are the following:

Argument	Description
name	The name of a script in the OML4R script repository.
type	For a script, the type is rqscript.
user	The user to whom to grant or revoke read privilege access.

The name and type arguments are required. If argument user is not specified, then read privilege access is granted to or revoked from all users.

An error occurs when one of the following is true:

• The named script is not in the OML4R script repository.

• The type argument is not specified.

• The user is not found.

• The read privilege has already been granted to or revoked from the user.

• The named script is public.

Listing the Available Scripts

To list the scripts available to you, use ore.scriptList. You can list scripts by name, by a pattern, or by type. The function has the following syntax:

ore.scriptList(name, pattern, type)

The arguments are the following:

Argument	Description
name	The name of a script in the OML4R script repository. Cannot be used when argument pattern is specified.
pattern	A regular expression pattern. Scripts that match the pattern are listed. Cannot be used when argument name is specified.
type	The type of the script, which can be one of the following: user, which lists scripts owned by the current user global, which lists public scripts, which are visible to all users grant, which lists the scripts to which the current user has granted read access to others granted, which lists the scripts to which the current user has been granted read access by another user all, which lists all of the user, public, and granted scripts

The ore.scriptList function returns a data.frame that contains the names of the scripts in the OML4R script repository and the function in the script.

Loading a Script into an R Environment

To load the R function of a script into an R environment, use ore.scriptLoad, which has the following syntax:

ore.scriptLoad(name, owner, newname, envir)

The arguments are the following:

Argument	Description
name	The name of a script in the OML4R script repository.
owner	The owner of the script.
newname	A new function name in which to load the script.
envir	The R environment in which to load the script.

Specifying the owner of a script is useful when access to the script has been granted to the user who is invoking ore.scriptLoad.

Specifying a new function name is useful when the name of the script in the OML4R script repository is not a valid R function name.

An error occurs when one of the following is true:

• The script is not in the OML4R script repository.

• The current user does not have read access to the script.

• The function specified by the name argument is not a valid R function name.

• The newname argument is not a valid R function name.

Dropping a Script

To remove a script from the OML4R script repository, use the ore.scriptDrop function. To call this function, you must have the RQADMIN role. The ore.scriptDrop function has the following syntax:

ore.scriptDrop(name, global, silent)

The arguments are the following:

Argument	Description
name	A name for the script in the OML4R script repository.
global	A logical value that indicates whether the script is global (public) or private. TRUE specifies dropping a global script; FALSE (the default) specifies dropping a script owned by the current user.
silent	A logical value that indicates whether to display an error message if ore.scriptDrop encounters an error condition. TRUE specifies the display of error messages; FALSE (the default) specifies no display.

An error condition occurs when one of the following is true:

• The script is not in the OML4R script repository.

• If global = TRUE, the script is a private script.

• If global = FALSE, the script is a public script.

If successful, ore.scriptDrop returns NULL.

Example 10-1 Using the R Script Management Functions

# Create a temporary R data.frame proxy object for the iris data.frame. Overwrite the script another script with the same name already exists.
IRIS <- ore.push(iris)

# Create a private R script for the current user.
ore.scriptCreate("myRandomRedDots", function(divisor = 100){
                 id <- 1:10
                 plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2 )
                 data.frame(id = id, val = id / divisor)
                 }, overwrite=TRUE)

# Create another private R script.
ore.scriptCreate("MYLM",
                 function(data, formula, ...) lm(formula, data, ...), overwrite=TRUE)

# Create a public script, available to any user.
ore.scriptCreate("GLBGLM",
                 function(data, formula, ...) 
                 glm(formula = formula, data = data, ...),
                 global = TRUE,
                 overwrite=TRUE)

# List only my private scripts.
ore.scriptList()                

# List my private scripts and the public scripts. 
ore.scriptList(type = "all")    

# List my private scripts that have the specified pattern.
ore.scriptList(pattern = "MY")  

# Grant read access to a private script to all users.
ore.grant("MYLM", type = "rqscript")

# Grant read access to a private script to a specific user.
ore.grant("myRandomRedDots", user = "OMLUSER",  type = "rqscript")

# List the granted scripts.
ore.scriptList(type = "grant")

# Use the MYLM script in an Embedded R Execution function.
ore.tableApply(IRIS[1:4], FUN.NAME = "MYLM",
               formula = Sepal.Length ~ .)
# Use the GLBGLM script in an Embedded R Execution function.
ore.tableApply(IRIS[1:4], FUN.NAME = "GLBGLM",
               formula = Sepal.Length ~ .)

# Load an R script to an R function object
ore.scriptLoad(name = "MYLM")

# Invoke the function.
MYLM(iris, formula = Sepal.Length ~ .)

# Load another R script to an R function object
ore.scriptLoad(name = "GLBGLM", newname = "MYGLM")

# Invoke the function.
MYGLM(iris, formula = Sepal.Length ~ .)

# Drop some scripts.
ore.scriptDrop("MYLM")
ore.scriptDrop("GLBGLM", global = TRUE)

# List all scripts.
ore.scriptList(type = "all")

The output is similar to the following:

Table 10-2 A data.frame: 7 x 2

NAME	SCRIPT
<chr>	<chr>
MYLM	function (data, formula, ...) lm(formula, data, ...)
build.lm	function (dat) { mod <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(mod) }
build.lm.1	function (dat) { regr <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(regr) }
buildLM.group	function (dat) { mod <- lm(Petal.Length ~ Petal.Width, dat) return(mod) }
buildLM.group.1	function (dat) { mod <- lm(mpg ~ hp + vs, dat) return(mod) }
myRandomRedDots	function (divisor = 100) { id <- 1:10 plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2) data.frame(id = id, val = id/divisor) }
scoreLM.1	function (dat, dsname) { ore.load(dsname) dat$Petal.Length_prediction <- predict(mod, newdata = dat) dat[, c("Petal.Length_prediction", "Petal.Length", "Species")] }

Table 10-3 A data.frame: 20 x 3

OWNER	NAME	SCRIPT
<chr>	<chr>	<chr>
RQSYS	GLBGLM	function (data, formula, ...) glm(formula = formula, data = data, ...)
RQSYS	RQ$R.Version	function() { v <- as.character(R.Version()) v[v == ""] <- NA_character_ data.frame(name=names(R.Version()), value=unname(v), stringsAsFactors=FALSE) }
RQSYS	RQ$getRversion	function() { data.frame(Version=as.character(getRversion()), stringsAsFactors=FALSE) }
RQSYS	RQ$installed.packages	function() { data.frame(installed.packages()[,c(1L,3L,2L),drop=FALSE], stringsAsFactors=FALSE) }
RQSYS	RQ$packageVersion	function(pkg) { data.frame(Version=as.character(packageVersion(pkg=pkg)), stringsAsFactors=FALSE) }
RQSYS	RQG$boxplot	function(x, ...) { boxplot(x, ...) invisible(NULL) }
RQSYS	RQG$cdplot	function(x, ...) { if (NCOL(x) < 2L) stop("script RQG$cdplot requires 2 columns to produce graphic") x[[2L]] <- as.factor(x[[2L]]) cdplot(x[[1L]], x[[2L]], ...) invisible(NULL) }
RQSYS	RQG$hist	function(x, ...) { if (is.data.frame(x)) x <- x[[1L]] hist(x, ...) invisible(NULL) }
RQSYS	RQG$matplot	function(x, ...) { matplot(x, ...) invisible(NULL) }
RQSYS	RQG$pairs	function(x, ...) { if (NCOL(x) < 2L) stop("script RQG$pairs requires at least 2 columns to produce graphic") pairs(x, ...) invisible(NULL) }
RQSYS	RQG$plot1d	function(x, ...) { if (is.data.frame(x)) x <- x[[1L]] if (is.character(x)) x <- as.factor(x) plot(x, ...) invisible(NULL) }
RQSYS	RQG$plot2d	function(x, ...) { if (NCOL(x) < 2L) stop("script RQG$plot2d requires 2 columns to produce graphic") x <- x[1:2] if (is.character(x[[1L]])) x[[1L]] <- as.factor(x[[1L]]) if (is.character(x[[2L]])) x[[2L]] <- as.factor(x[[2L]]) plot(x[1:2], ...) invisible(NULL) }
RQSYS	RQG$smoothScatter	function(x, ...) { if (NCOL(x) < 2L) stop("script RQG$smoothScatter requires 2 columns to produce graphic") x <- x[1:2] if (is.character(x[[1L]])) x[[1L]] <- as.factor(x[[1L]]) if (is.character(x[[2L]])) x[[2L]] <- as.factor(x[[2L]]) smoothScatter(x[1:2], ...) invisible(NULL) }
SHERRY	MYLM	function (data, formula, ...) lm(formula, data, ...)
SHERRY	build.lm	function (dat) { mod <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(mod) }
SHERRY	build.lm.1	function (dat) { regr <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(regr) }
SHERRY	buildLM.group	function (dat) { mod <- lm(Petal.Length ~ Petal.Width, dat) return(mod) }
SHERRY	buildLM.group.1	function (dat) { mod <- lm(mpg ~ hp + vs, dat) return(mod) }
SHERRY	myRandomRedDots	function (divisor = 100) { id <- 1:10 plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2) data.frame(id = id, val = id/divisor) }
SHERRY	scoreLM.1	function (dat, dsname) { ore.load(dsname) dat$Petal.Length_prediction <- predict(mod, newdata = dat) dat[, c("Petal.Length_prediction", "Petal.Length", "Species")]

Table 10-4 A data.frame: 1 x 2

	SCRIPT
<chr>	<chr>
MYLM	function (data, formula, ...) lm(formula, data, ...)

Listing for This Example

R> # Create an ore.frame object from the data.frame for the iris data set.
R> IRIS <- ore.push(iris)
R> 
R> # Create a private R script for the current user.
R> ore.scriptCreate("myRandomRedDots", function(divisor = 100){
+                   id <- 1:10
+                   plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2 )
+                   data.frame(id = id, val = id / divisor)
+                   })
R> 
R> # Create another private R script.
R> ore.scriptCreate("MYLM",
+                   function(data, formula, ...) lm(formula, data, ...))
R> 
R> # Create a public script, available to any user.
R> ore.scriptCreate("GLBGLM",
+                   function(data, formula, ...) 
+                   glm(formula = formula, data = data, ...),
+                   global = TRUE)
R> 
R> # List only my private scripts.
R> ore.scriptList()
             NAME      SCRIPT
1            MYLM      function (data, formula, ...) \nlm(formula, data, ...)
2 myRandomRedDots      function (divisor = 100) \n{\n    id & lt\n    -1:10\n
                       plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2)\n
                       data.frame(id = id, val = id/divisor)\n}
R> 
R> # List my private scripts and the public scripts. 
R> ore.scriptList(type = "all")    
   OWNER              NAME    SCRIPT
1  RQSYS            GLBGLM    function (data, formula, ...) \nglm(formula = formula, data = data, ...)
2 OML_USER            MYLM    function (data, formula, ...) \nlm(formula, data, ...)
3 OML_USER myRandomRedDots    function (divisor = 100) \n{\n    id & lt\n    -1:10\n
                              plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2)\n
                              data.frame(id = id, val = id/divisor)\n}
R> 
R> # List my private scripts that have the specified pattern.
R> ore.scriptList(pattern = "MY")
  NAME  SCRIPT
1 MYLM  function (data, formula, ...) \nlm(formula, data, ...)
R> 
R> # Grant read access to a private script to all users.
R> ore.grant("MYLM", type = "rqscript")
R> 
R> # Grant read access to a private script to a specific user.
R> ore.grant("myRandomRedDots", user = "SCOTT",  type = "rqscript")
R> 
R> # List the granted scripts.
R> ore.scriptList(type = "grant")
             NAME GRANTEE
1            MYLM  PUBLIC
2 myRandomRedDots   SCOTT
R> 
R> # Use the MYLM script in an Embedded R Execution function.
R> ore.tableApply(IRIS[1:4], FUN.NAME = "MYLM",
+                 formula = Sepal.Length ~ .)

Call:
lm(formula = formula, data = data)

Coefficients:
 (Intercept)   Sepal.Width  Petal.Length   Petal.Width  
      1.8560        0.6508        0.7091       -0.5565 
R> 
R> # Use the GLBGLM script in an Embedded R Execution function.
R> ore.tableApply(IRIS[1:4], FUN.NAME = "GLBGLM",
+                formula = Sepal.Length ~ .) 

Call:  glm(formula = formula, data = data)

Coefficients:
 (Intercept)   Sepal.Width  Petal.Length   Petal.Width  
      1.8560        0.6508        0.7091       -0.5565  

Degrees of Freedom: 149 Total (i.e. Null);  146 Residual
Null Deviance:      102.2 
Residual Deviance: 14.45        AIC: 84.64
R> 
R> # Load an R script to an R function object
R> ore.scriptLoad(name="MYLM")
R> 
R> # Invoke the function.
R> MYLM(iris, formula = Sepal.Length ~ .)
R> 
R> # Load another R script to an R function object
R> ore.scriptLoad(name = "GLBGLM", newname = "MYGLM")
R> 
R> # Invoke the function.
R> MYGLM(iris, formula = Sepal.Length ~ .)
R> 
R> # Drop some scripts.
R> ore.scriptDrop("MYLM")
R> ore.scriptDrop("GLBGLM", global = TRUE)
R> 
R> # List all scripts.
R> ore.scriptList(type = "all")
   OWNER               NAME    SCRIPT
  OML_USER  myRandomRedDots    function (divisor = 100) \n{\n    id & lt\n    -1:10\n
                               plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 
                               2)\n data.frame(id = id, val = id/divisor)\n}

See Also:

• Input Function to Run
• Example 10-10 for another example of using ore.scriptCreate and ore.scriptDrop
• Manage Scripts in SQL

10.3.3 Use the ore.doEval Function

The ore.doEval function runs the specified input function using data that is generated by the input function.

It returns an ore.frame object or a serialized R object as an ore.object object.

The syntax of the ore.doEval function is the following:

ore.doEval(FUN, ..., FUN.VALUE = NULL, FUN.NAME = NULL, FUN.OWNER = NULL)

Example 10-2 Using the ore.doEval Function

In this example, RandomRedDots gets a function that has an argument and that returns a data.frame object that has two columns and that plots 50 random normal values. The example then calls ore.doEval function and passes it the RandomRedDots function object. The image is displayed at the client, but it is generated by the database server R engine that runs the RandomRedDots function.

%r

res <- ore.doEval(FUN.NAME="myRandomRedDots", divisor = 50,
                     FUN.VALUE= data.frame(id = 1, val = 1), ore.graphics=FALSE)
class(res)

The result is:

'ore.frame'

Listing for This Example

R> RandomRedDots <- function(divisor = 100){
+   id<- 1:10
+   plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2 )
+   data.frame(id=id, val=id / divisor)
+ }
R> ore.doEval(RandomRedDots)
   id  val
1   1 0.01
2   2 0.02
3   3 0.03
4   4 0.04
5   5 0.05
6   6 0.06
7   7 0.07
8   8 0.08
9   9 0.09
10 10 0.10

Figure 10-1 Display of Random Red Dots

Description of "Figure 10-1 Display of Random Red Dots"

Example 10-3 Using the ore.doEval Function with an Optional Argument

You can provide arguments to the input function as optional arguments to the doEval function. This example calls the doEval function with an optional argument that overrides the divisor argument of the RandomRedDots function.

ore.doEval(RandomRedDots, divisor = 50)

Listing for This Example

R> ore.doEval(RandomRedDots, divisor = 50)
   id  val
1   1 0.02
2   2 0.04
3   3 0.06
4   4 0.08
5   5 0.10
6   6 0.12
7   7 0.14
8   8 0.16
9   9 0.18
10 10 0.20
# The graph displayed by the plot function is not shown.

Example 10-4 Using the ore.doEval Function with the FUN.NAME Argument

If the input function is stored in the OML4R script repository, then you can invoke the ore.doEval function with the FUN.NAME argument. This example first calls ore.scriptDrop to ensure that the script repository does not contain a script with the name myRandomRedDots. The example adds the RandomRedDots function from Example 10-2 to the repository under the name myRandomRedDots. This example calls the ore.doEval function and specifies myRandomRedDots. The result is assigned to the variable res.

The return value of the RandomRedDots function is a data.frame but in this example the ore.doEval function returns an ore.object object. To get back the data.frame object, the example calls ore.pull to pull the result to the client R session.

ore.scriptDrop("myRandomRedDots")
ore.scriptCreate("myRandomRedDots", RandomRedDots)
res <- ore.doEval(FUN.NAME = "myRandomRedDots", divisor = 50)
class(res)
res.local <- ore.pull(res)
class(res.local)

Listing for This Example

R> ore.scriptDrop("myRandomRedDots")
R> ore.scriptCreate("myRandomRedDots", RandomRedDots)
R> res <- ore.doEval(FUN.NAME = "myRandomRedDots", divisor = 50)
R> class(res)
[1] "ore.object"
attr(,"package")
[1] "OREembed"
R> res.local <- ore.pull(res)
R> class(res.local)
[1] "data.frame"

Example 10-5 Using the ore.doEval Function with the FUN.VALUE Argument

To have the doEval function return an ore.frame object instead of an ore.object, use the argument FUN.VALUE to specify the structure of the result, as shown in this example.

%r

res <- ore.doEval(FUN.NAME="myRandomRedDots", divisor = 50,
                     FUN.VALUE= data.frame(id = 1, val = 1), ore.graphics=FALSE)
class(res)

The ouput is similar to the following:

'ore.frame'

Listing for Example 10-5

R> res.of <- ore.doEval(FUN.NAME="myRandomRedDots", divisor = 50,
+                        FUN.VALUE= data.frame(id = 1, val = 1), ore.graphics=FALSE)
R> class(res.of)
[1] "ore.frame"
attr(,"package")
[1] "OREbase"

Example 10-6 Using the doEval Function with the ore.connect Argument

This example demonstrates using the special optional argument ore.connect to connect to the database in the embedded R function, which enables the use of objects stored in a datastore. The example creates the RandomRedDots2 function object, which is the same as the RandomRedDots function from Example 10-2 except the RandomRedDots2 function has an argument that takes the name of a datastore. The example creates the myVar variable and saves it in the datastore named datastore_1. The example then calls the doEval function and passes it the name of the datastore and passes the ore.connect control argument set to TRUE.

%r

RandomRedDots2 <- function(divisor = 100, dsname = "ds-1"){
  id <- 1:10
  plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2 )
  ore.load(dsname) # Contains the numeric variable myVar.
  data.frame(id = id, val = id / divisor, num = myVar)
}

myVar <- 5
ore.save(myVar, name = "ds-1", overwrite=TRUE)
ore.doEval(RandomRedDots2, dsname="ds-1", ore.connect=TRUE)

The output is similar to the following:

   id  val num
1   1 0.01   5
2   2 0.02   5
3   3 0.03   5
4   4 0.04   5
5   5 0.05   5
6   6 0.06   5
7   7 0.07   5
8   8 0.08   5
9   9 0.09   5
10 10 0.10   5

Listing for This Example

R> RandomRedDots2 <- function(divisor = 100, datastore.name = "myDatastore"){
+   id <- 1:10
+   plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2 )
+   ore.load(datastore.name) # Contains the numeric variable myVar.
+   data.frame(id = id, val = id / divisor, num = myVar)
+ }

R>myVar <- 5
R>ore.save(myVar, name = "ds-1", overwrite=TRUE)
R> ore.doEval(RandomRedDots2, datastore.name = "datastore_1", ore.connect = TRUE)
   id  val num
1   1 0.01   5
2   2 0.02   5
3   3 0.03   5
4   4 0.04   5
5   5 0.05   5
6   6 0.06   5
7   7 0.07   5
8   8 0.08   5
9   9 0.09   5
10 10 0.10   5
# The graph displayed by the plot function is not shown.

Example 10-7 Using the ora.type Attribute

This example demonstrates using the ora.type attribute to specify database data types of CLOB and BLOB for columns in the data.frame object specified by the FUN.VALUE argument.

%r

# NOTE FROM SL: I added spaces between each example

eval1 <- ore.doEval

eval2 <-
  ore.doEval(function()
	           data.frame(x = "Hello, world", stringsAsFactors = FALSE))
	           
eval3 <-
  ore.doEval(function()
	           data.frame(x = "Hello, world", stringsAsFactors = FALSE),
	           FUN.VALUE = data.frame(x = character(), stringsAsFactors = FALSE))
	           
out.df <- data.frame(x = character(), y = raw(), stringsAsFactors = FALSE)
attr(out.df$x, "ora.type") <- "clob"
attr(out.df$y, "ora.type") <- "blob"

eval4 <- 
  ore.doEval(function() {
	           res <- data.frame(x = "Hello, world",stringsAsFactors = FALSE)
	           res$y[[1L]] <- charToRaw("Hello, world")
	           res},
	           FUN.VALUE = out.df)
	           
eval1
class(eval1)  # ore.object
eval2
class(eval2)  # ore.object
eval3
class(eval3)  # ore.frame
eval4$x
rawToChar(ore.pull(eval4$y))

The output is similar to the following:

'function'

Table 10-5 A data.frame: 1 x 1

x
<chr>
Hello, world

'data.frame'

Table 10-6 A data.frame: 1 x 1

x
<chr>
Hello, world

'ore.frame'[1] "Hello, world"'Hello, world'

Listing for This Example

R> eval1 <- ore.doEval(function() "Hello, world")
R> eval2 <- 
+    ore.doEval(function()
+               data.frame(x = "Hello, world", stringsAsFactors = FALSE))
R> eval3 <-
+    ore.doEval(function()
+               data.frame(x = "Hello, world", stringsAsFactors = FALSE),
+               FUN.VALUE =
+               data.frame(x = character(), stringsAsFactors = FALSE))
R> out.df <- data.frame(x = character(), y = raw(), stringsAsFactors = FALSE)
R> attr(out.df$x, "ora.type") <- "clob"
R> attr(out.df$y, "ora.type") <- "blob"
R> eval4 <- 
+    ore.doEval(function() {
+               res <- data.frame(x = "Hello, world",stringsAsFactors = FALSE)
+               res$y[[1L]] <- charToRaw("Hello, world")
+               res},
+               FUN.VALUE = out.df)
R> eval1
[1] "Hello, world"
R> class(eval1)
[1] "ore.object"
attr(,"package")
[1] "OREembed"
R> eval2
             x
1 Hello, world
R> class(eval2)
[1] "ore.object"
attr(,"package")
[1] "OREembed"
R> eval3
             x
1 Hello, world
Warning message:
ORE object has no unique key - using random order 
R> class(eval3)
[1] "ore.frame"
attr(,"package")
[1] "OREbase"
R> eval4$x                              
[1] "Hello, world"
Warning message:
ORE object has no unique key - using random order 
R> rawToChar(ore.pull(eval4$y))
[1] "Hello, world"
Warning message:
ORE object has no unique key - using random order 

10.3.4 Use the ore.tableApply Function

The ore.tableApply function calls an R script with an ore.frame as the input data.

The ore.tableApply function passes the ore.frame to the user-defined input function as the first argument to that function. The ore.tableApply function returns an ore.frame object or a serialized R object as an ore.object object.

The syntax of the ore.tableApply function is the following:

ore.tableApply(X, FUN, ..., FUN.VALUE = NULL, FUN.NAME = NULL, FUN.OWNER = NULL)

Example 10-8 Using the ore.tableApply Function

This example uses the ore.tableApply function to build a linear regression model on the iris data set. The linear regression function is in the e1071 package, which must be installed on both the client and database server machine R engines. As the first argument to the ore.tableApply function, the ore.push(iris) invocation creates a temporary database table and an ore.frame that is a proxy for the table. The second argument is the input function, which has as an argument dat. The ore.tableApply function passes the ore.frame table proxy to the input function as the dat argument. The input function creates a model, which the ore.tableApply function returns as an ore.object object.

%r

# Create a user-defined function that builds and returns a model using R's lm() function
build.lm <- function(dat){
  mod <- lm(Petal.Length~Petal.Width+Sepal.Width+Sepal.Length, dat)

  x <- dat[['Petal.Width']]
  y <- dat[['Petal.Length']]
    
  return(mod)
}

# Run the user-defined function on the local iris data.frame

res1 <- build.lm(iris)
res1

# Create a temporary R data.frame proxy object IRIS and run the user-defined function using ore.tableApply. The function name is passed to the FUN argument.

IRIS <- ore.push(iris)

res2 <- ore.tableApply(IRIS, FUN=build.lm)
res2     

# Save the user-defined function to the R script repository with the same name. Run the function stored in the script repository using ore.tableApply.
# The script name is passed to the FUN.NAME argument. Overwrite any script with the same name if it exits.

ore.scriptCreate("build.lm", build.lm, overwrite=TRUE)
ore.scriptList()

res3 <- ore.tableApply(IRIS, FUN.NAME="build.lm")
res3  

The output is similar to the following:

Call:
lm(formula = Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, 
    data = dat)

Coefficients:
 (Intercept)   Petal.Width   Sepal.Width  Sepal.Length  
     -0.2627        1.4468       -0.6460        0.7291  

Call:
lm(formula = Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, 
    data = dat)

Coefficients:
 (Intercept)   Petal.Width   Sepal.Width  Sepal.Length  
     -0.2627        1.4468       -0.6460        0.7291  

Table 10-7 A data.frame: 6 x 2

NAME	SCRIPT
<chr>	<chr>
build.lm	function (dat) { mod <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(mod) }
build.lm.1	function (dat) { regr <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, dat) x <- dat[["Petal.Width"]] y <- dat[["Petal.Length"]] return(regr) }
buildLM.group	function (dat) { mod <- lm(Petal.Length ~ Petal.Width, dat) return(mod) }
buildLM.group.1	function (dat) { mod <- lm(mpg ~ hp + vs, dat) return(mod) }
myRandomRedDots	function (divisor = 100) { id <- 1:10 plot(1:100, rnorm(100), pch = 21, bg = "red", cex = 2) data.frame(id = id, val = id/divisor) }
scoreLM.1	function (dat, dsname) { ore.load(dsname) dat$Petal.Length_prediction <- predict(mod, newdata = dat) dat[, c("Petal.Length_prediction", "Petal.Length", "Species")] }

Call: lm(formula = Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, data = dat) Coefficients: (Intercept) Petal.Width Sepal.Width Sepal.Length -0.2627 1.4468 -0.6460 0.7291

Listing for This Example

R> nbmod <- ore.tableApply(
+   ore.push(iris),
+   function(dat) {
+     library(e1071)
+     dat$Species <- as.factor(dat$Species)
+     naiveBayes(Species ~ ., dat)
+ })
R> class(nbmod)
[1] "ore.object"
attr(,"package")
[1] "OREembed"
R> nbmod
 
Naive Bayes Classifier for Discrete Predictors
 
Call:
naiveBayes.default(x = X, y = Y, laplace = laplace)
 
A-priori probabilities:
Y
    setosa versicolor  virginica 
 0.3333333  0.3333333  0.3333333 
 
Conditional probabilities:
            Sepal.Length
Y             [,1]      [,2]
  setosa     5.006 0.3524897
  versicolor 5.936 0.5161711
  virginica  6.588 0.6358796
 
            Sepal.Width
Y             [,1]      [,2]
  setosa     3.428 0.3790644
  versicolor 2.770 0.3137983
  virginica  2.974 0.3224966
 
            Petal.Length
Y             [,1]      [,2]
  setosa     1.462 0.1736640
  versicolor 4.260 0.4699110
  virginica  5.552 0.5518947
 
            Petal.Width
Y             [,1]      [,2]
  setosa     0.246 0.1053856
  versicolor 1.326 0.1977527
  virginica  2.026 0.2746501

10.3.5 Use the ore.groupApply Function

The ore.groupApply function calls an R script with an ore.frame as the input data.

The ore.groupApply function passes the ore.frame to the user-defined input function as the first argument to that function. The INDEX argument to the ore.groupApply function specifies the name of a column of the ore.frame by which Oracle Database partitions the rows for processing by the user-defined R function. The ore.groupApply function can use data-parallel runs, in which one or more R engines perform the same R function, or task, on different partitions of data.

The syntax of the ore.groupApply function is the following:

ore.groupApply(X, INDEX, FUN, ..., FUN.VALUE = NULL, FUN.NAME = NULL, FUN.OWNER = NULL,
               parallel = getOption("ore.parallel", NULL))

The ore.groupApply function returns an ore.list object or an ore.frame object.

Examples of the use of the ore.groupApply function are in the following topics:

• Partition on a Single Column
  This example uses the ore.groupApply function and partitions the data on a single column.
• Partition on Multiple Columns
  This example uses the ore.groupApply function and partitions the data on multiple columns.

10.3.5.1 Partition on a Single Column

This example uses the ore.groupApply function and partitions the data on a single column.

Example 10-9 Using the ore.groupApply Function

Create a user-defined function that builds and returns a model using R’s lm() function.

%r

buildLM.group <- function(dat){
  mod <- lm(Petal.Length~Petal.Width, dat)
  return(mod)
}

# Run the user-defined function on the local iris data.frame

res1 <- buildLM.group(iris)
res1

# Create a temporary R data.frame proxy object IRIS and run the user-defined function using ore.tableApply. The function name is passed to the FUN argument.

IRIS <- ore.push(iris)

# Use ore.groupApply to build one model for each of the three categories in the Species variable as well as specifying the desired number of parallel R engines using the parallel argument.
# We build three models and return them.

res2 <- ore.groupApply(IRIS[,c("Petal.Length","Petal.Width","Species")], 
                      INDEX = IRIS$Species, 
                      buildLM.group,
                      parallel = 3)
res2                      
                                           
# Save the user-defined function to the R script repository with the same name. Run the function stored in the script repository using ore.tableApply.
# The script name is passed to the FUN.NAME argument. Overwrite any script with the same name if it exits.

ore.scriptCreate(name = 'buildLM.group', 
                 FUN  =  buildLM.group,     
                 overwrite = TRUE)


res3 <- ore.groupApply(IRIS[,c("Petal.Length","Petal.Width","Species")], 
                      INDEX = IRIS$Species, 
                      buildLM.group,
                      parallel = 3)
res3

The output is similar to the following:

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
      1.084        2.230  
Warning message:
“Parallelism exceeds the DOP limit 2 (reverting to parallel=2)”
$setosa

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
     1.3276       0.5465  


$versicolor

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
      1.781        1.869  


$virginica

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
     4.2407       0.6473  

Warning message:
“Parallelism exceeds the DOP limit 2 (reverting to parallel=2)”
$setosa

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
     1.3276       0.5465  


$versicolor

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
      1.781        1.869  


$virginica

Call:
lm(formula = Petal.Length ~ Petal.Width, data = dat)

Coefficients:
(Intercept)  Petal.Width  
     4.2407       0.6473  

10.3.5.2 Partition on Multiple Columns

This example uses the ore.groupApply function and partitions the data on multiple columns.

The ore.groupApply function takes a single column or multiple columns as the INDEX argument. The following example uses data from the CHURN_TRAIN data set to build an rpart model that produces rules on the partitions of data specified, which are the voice_mail_plan and international_plan columns. The example uses the R table function to show the number of rows to expect in each partition.

The example calls the ore.scriptDrop function to ensure that no script by the specified name exists in the OML4R script repository. It then uses the ore.scriptCreate function to define a script named my_rpartFunction and to store it in the repository. The stored script defines a function that takes a data source and a prefix to use for naming OML4R datastore objects. Each invocation of the function my_rpartFunction receives data from one of the partitions identified by the values in the voice_mail_plan and international_plan columns. Because the source partition columns are constants, the function sets them to NULL. It converts the character vectors to factors, builds a model to predict churn, and saves it in an appropriately named datastore. The function creates a list to return the specific partition column values, the distribution of churn values, and the model itself.

The example then loads the rpart library, sets the datastore prefix, and calls ore.groupApply using the values from the voice_mail_plan and international_plan columns as the INDEX argument and my_rpartFunction as the value of the FUN.NAME argument to invoke the user-defined function stored in the script repository. The ore.groupApply function uses an optional argument to pass the datastorePrefix variable to the user-defined function. It uses the optional argument ore.connect to connect to the database when executing the user-defined function. The ore.groupApply function returns an ore.list object as the variable res.

The example displays the first entry in the list returned. It then calls the ore.load function to load the model for the case where the customer has both the voice mail plan and the international plan.

Example 10-10 Using ore.groupApply for Partitioning Data on Multiple Columns

%r

MTCARS <- ore.push(mtcars)

# Create a user-defined function that builds and returns a model using R's lm() function.

buildLM.group.1 <- function(dat){
  mod <- lm(mpg ~ hp + vs, dat)
  return(mod)
}

# Run the user-defined function on the local mtcars data.frame

res1 <- buildLM.group.1(mtcars)
res1

# Create a temporary R data.frame proxy object MTCARS and run the user-defined function using ore.groupApply. The function name is passed to the FUN argument.

MTCARS <- ore.push(mtcars)

# Use ore.groupApply to build one model for each of the categories in the cyl and am variables as well as specifying the desired number of parallel R engines using the parallel argument.

res2 <- ore.groupApply(MTCARS, 
                      INDEX = MTCARS[ , c("cyl", "am")],
                      buildLM.group.1,
                      parallel = 2)
res2                      
                                           
# Save the user-defined function to the R script repository with the same name. Run the function stored in the script repository using ore.tableApply.
# The script name is passed to the FUN.NAME argument. Overwrite any script with the same name if it exits.

ore.scriptCreate(name = 'buildLM.group.1', 
                 FUN  =  buildLM.group.1,     
                 overwrite = TRUE)


res3 <- ore.groupApply(MTCARS, 
                      INDEX = MTCARS[, c("cyl", "am")],
                      FUN.NAME="buildLM.group.1",
                      parallel = 2)
res3

The ouput is similar to the following:

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   26.96300     -0.05453      2.57622  
$`80`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   23.23434     -0.04215           NA  


$`41`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
    36.1150      -0.1112       1.2122  


$`61`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
      23.20        -0.02           NA  


$`81`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   18.77465     -0.01127           NA  


$`60`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   24.19782     -0.04402           NA  


$`40`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   28.63089     -0.06769           NA  

$`80`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   23.23434     -0.04215           NA  


$`41`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
    36.1150      -0.1112       1.2122  


$`61`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
      23.20        -0.02           NA  


$`81`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   18.77465     -0.01127           NA  


$`60`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   24.19782     -0.04402           NA  


$`40`

Call:
lm(formula = mpg ~ hp + vs, data = dat)

Coefficients:
(Intercept)           hp           vs  
   28.63089     -0.06769           NA  

Listing for This Example

R> library(C50)
R> data(churn)
R> ore.drop("CHURN_TRAIN") 
R> ore.create(churnTrain, "CHURN_TRAIN")
R>  
R> table(CHURN_TRAIN$international_plan, CHURN_TRAIN$voice_mail_plan)
     
        no  yes
  no  2180  830
  yes  231   92
R>
R> options(width = 80)
R> head(CHURN_TRAIN, 3)
  state account_length     area_code international_plan voice_mail_plan
1    KS            128 area_code_415                 no             yes
2    OH            107 area_code_415                 no             yes
3    NJ            137 area_code_415                 no              no
  number_vmail_messages total_day_minutes total_day_calls total_day_charge
1                    25             265.1             110            45.07
2                    26             161.6             123            27.47
3                     0             243.4             114            41.38
  total_eve_minutes total_eve_calls total_eve_charge total_night_minutes
1             197.4              99            16.78               244.7
2             195.5             103            16.62               254.4
3             121.2             110            10.30               162.6
  total_night_calls total_night_charge total_intl_minutes total_intl_calls
1                91              11.01               10.0                3
2               103              11.45               13.7                3
3               104               7.32               12.2                5
  total_intl_charge number_customer_service_calls churn
1              2.70                             1    no
2              3.70                             1    no
3              3.29                             0    no
Warning messages:
1: ORE object has no unique key - using random order
2: ORE object has no unique key - using random order
R> 
R> ore.scriptDrop("my_rpartFunction")
R> ore.scriptCreate("my_rpartFunction",
+   function(dat, datastorePrefix) {
+     library(rpart)
+     vmp <- dat[1, "voice_mail_plan"]
+     ip <- dat[1, "international_plan"]
+     datastoreName <- paste(datastorePrefix, vmp, ip, sep = "_")
+     dat$voice_mail_plan <- NULL
+     dat$international_plan <- NULL
+     dat$state <- as.factor(dat$state)
+     dat$churn <- as.factor(dat$churn)
+     dat$area_code <- as.factor(dat$area_code)
+     mod <- rpart(churn ~ ., data = dat)
+     ore.save(mod, name = datastoreName, overwrite = TRUE)
+     list(voice_mail_plan = vmp,
+         international_plan = ip,
+         churn.table = table(dat$churn),
+         rpart.model = mod)
+   })
R> 
R> library(rpart)
R> datastorePrefix = "my.rpartModel"
R> 
R> res <- ore.groupApply(CHURN_TRAIN,
+       INDEX = CHURN_TRAIN[, c("voice_mail_plan", "international_plan")],
+       FUN.NAME = "my_rpartFunction",
+       datastorePrefix = datastorePrefix,
+       ore.connect = TRUE)
R> res[[1]]
$voice_mail_plan
[1] "no"
 
$international_plan
[1] "no"
 
$churn.table
 
  no  yes 
1878  302 
 
$rpart.model
n= 2180 
 
node), split, n, loss, yval, (yprob)
      * denotes terminal node
 
 1) root 2180 302 no (0.86146789 0.13853211)  
   2) total_day_minutes< 263.55 2040 192 no (0.90588235 0.09411765)  
     4) number_customer_service_calls< 3.5 1876 108 no (0.94243070 0.05756930)  
       8) total_day_minutes< 223.25 1599  44 no (0.97248280 0.02751720) *
       9) total_day_minutes>=223.25 277  64 no (0.76895307 0.23104693)  
        18) total_eve_minutes< 242.35 210  18 no (0.91428571 0.08571429) *
        19) total_eve_minutes>=242.35 67  21 yes (0.31343284 0.68656716)  
          38) total_night_minutes< 174.2 17   4 no (0.76470588 0.23529412) *
          39) total_night_minutes>=174.2 50   8 yes (0.16000000 0.84000000) *
     5) number_customer_service_calls>=3.5 164  80 yes (0.48780488 0.51219512)  
      10) total_day_minutes>=160.2 95  22 no (0.76842105 0.23157895)  
        20) state=AL,AZ,CA,CO,DC,DE,FL,HI,KS,KY,MA,MD,ME,MI,NC,ND,NE,NH,NM,OK,OR,SC,TN,VA,VT,WY 56   2 no (0.96428571 0.03571429) *
        21) state=AK,AR,CT,GA,IA,ID,MN,MO,NJ,NV,NY,OH,RI,TX,UT,WA,WV 39  19 yes (0.48717949 0.51282051)  
          42) total_day_minutes>=182.3 21   5 no (0.76190476 0.23809524) *
          43) total_day_minutes< 182.3 18   3 yes (0.16666667 0.83333333) *
      11) total_day_minutes< 160.2 69   7 yes (0.10144928 0.89855072) *
   3) total_day_minutes>=263.55 140  30 yes (0.21428571 0.78571429)  
     6) total_eve_minutes< 167.3 29   7 no (0.75862069 0.24137931)  
      12) state=AK,AR,AZ,CO,CT,FL,HI,IN,KS,LA,MD,ND,NM,NY,OH,UT,WA,WV 21   0 no (1.00000000 0.00000000) *
      13) state=IA,MA,MN,PA,SD,TX,WI 8   1 yes (0.12500000 0.87500000) *
     7) total_eve_minutes>=167.3 111   8 yes (0.07207207 0.92792793) *
 
R> ore.load(name = paste(datastorePrefix, "yes", "yes", sep = "_"))
[1] "mod"
R> mod
n= 92 
 
node), split, n, loss, yval, (yprob)
      * denotes terminal node
 
1) root 92 36 no (0.60869565 0.39130435)  
  2) total_intl_minutes< 13.1 71 15 no (0.78873239 0.21126761)  
    4) total_intl_calls>=2.5 60  4 no (0.93333333 0.06666667)  
      8) state=AK,AR,AZ,CO,CT,DC,DE,FL,GA,HI,ID,IL,IN,KS,MD,MI,MO,MS,MT,NC,ND,NE,NH,NJ,OH,SC,SD,UT,VA,WA,WV,WY 53  0 no (1.00000000 0.00000000) *
      9) state=ME,NM,VT,WI 7  3 yes (0.42857143 0.57142857) *
    5) total_intl_calls< 2.5 11  0 yes (0.00000000 1.00000000) *
  3) total_intl_minutes>=13.1 21  0 yes (0.00000000 1.00000000) *

10.3.6 Use the ore.rowApply Function

The ore.rowApply function calls an R script with an ore.frame as the input data.

The ore.rowApply function passes the ore.frame to the user-defined input function as the first argument to that function. The rows argument to the ore.rowApply function specifies the number of rows to pass to each invocation of the user-defined R function. The last chunk or rows may have fewer rows than the number specified. The ore.rowApply function can use data-parallel execution, in which one or more R engines perform the same R function, or task, on different partitions of data.

The syntax of the ore.rowApply function is the following:

ore.rowApply(X, FUN, ..., FUN.VALUE = NULL, FUN.NAME = NULL, rows = 1, 
             FUN.OWNER = NULL, parallel = getOption("ore.parallel", NULL))

The ore.rowApply function returns an ore.list object or an ore.frame object.

Example 10-11 Using the ore.rowApply Function

This example uses the e1071 package, previously downloaded from CRAN. The example does the following:

• Loads the package e1071.

• Pushes the iris data set to the database as the IRIS temporary table and ore.frame object.

• Creates the Naive Bayes model nbmod.

• Creates a copy of IRIS as IRIS_PRED and adds the PRED column to IRIS_PRED to contain the predictions.

• calls the ore.rowApply function, passing the IRIS ore.frame as the data source for user-defined R function and the user-defined R function itself. The user-defined function does the following:

  • Loads the package e1071 so that it is available to the R engine or engines that run in the database.

  • Converts the Species column to a factor because, although the ore.frame defined factors, when they are loaded to the user-defined function, factors appear as character vectors.

  • calls the predict method and returns the res object, which contains the predictions in the column added to the data set.

• Pulls the model to the client R session.

• Passes IRIS_PRED as the argument FUN.VALUE, which specifies the structure of the object that the ore.rowApply function returns.

• Specifies the number of rows to pass to each invocation of the user-defined function.

• Displays the class of res, and calls the table function to display the Species column and the PRED column of the res object.

%r

# Create a temporary R data.frame proxy object for the iris data.frame. 
IRIS <- ore.push(iris)

# Build a model using a data.frame
mod <- lm(Petal.Length ~ Petal.Width + Sepal.Width + Sepal.Length, data=iris)

# Save the model to the datastore
ore.save(mod, "mod", name="ds-1", overwrite=TRUE)

# Create a user-defined function that loads a model residing in the datastore and scores the model on new data.
scoreLM.1 <- function(dat, dsname){
  ore.load(dsname)
  dat$Petal.Length_prediction <- predict(mod, newdata = dat)
  dat[,c("Petal.Length_prediction","Petal.Length","Species")]
}

# Save the user-defined scoring function in the R script repository.

ore.scriptCreate(name = 'scoreLM.1', 
                 FUN  = scoreLM.1,     
                 overwrite = TRUE)
                 
# Run the scoring function in the script repository as well as specifying the desired number of parallel R engines using the parallel argument.
# View the first 6 records of the result.
                 
res1 <- ore.rowApply(IRIS, 
                    scoreLM.1,
                    dsname = "ds-1", 
                    rows = 10, 
                    parallel = 2)

head(res1)

# Run the function again, this time


res2 <- ore.rowApply(IRIS, 
                    scoreLM.1,
                    dsname = "ds-1", 
                    rows = 10, 
                    parallel = 2,
                    FUN.VALUE = data.frame(Petal.Length_prediction=numeric(),             
                                           Petal.Length=numeric(),
                                           Species=character()))

class(res2)

The output is similar to the following:

Table 10-8 A data.frame: 6 x 3

Petal.Length_prediction	Petal.Length	Species
<dbl>	<dbl>	<chr>
1	1.484210	1.4	setosa
2	1.661389	1.4	setosa
3	1.386358	1.3	setosa
4	1.378046	1.5	setosa
5	1.346695	1.4	setosa
6	1.733905	1.7	setosa

'ore.frame'

10.3.7 Use the ore.indexApply Function

The ore.indexApply function executes the specified user-defined input function using data that is generated by the input function.

The function supports task-parallel execution, in which one or more R engines perform the same or different calculations, or task. The times argument to the ore.indexApply function specifies the number of times that the input function executes in the database. Any required data must be explicitly generated or loaded within the input function.

The syntax of the ore.indexApply function is the following:

ore.indexApply(times, FUN, ..., FUN.VALUE = NULL, FUN.NAME = NULL, FUN.OWNER = NULL,
               parallel = getOption("ore.parallel", NULL))

The ore.indexApply function returns an ore.list object or an ore.frame object.

• Simple Example of Using the ore.indexApply Function
  The example calls ore.indexApply and specifies that it runs the input function five times in parallel.
• Column-Parallel Use Case
  The example uses the R summary function to compute in parallel summary statistics on the first four numeric columns of the iris data set.
• Simulations Use Case
  You can use the ore.indexApply function in simulations, which can take advantage of high-performance computing hardware like an Oracle Exadata Database Machine.

10.3.7.1 Simple Example of Using the ore.indexApply Function

The example calls ore.indexApply and specifies that it runs the input function five times in parallel.

Example 10-12 Using the ore.indexApply Function

This example displays the class of the result, which is ore.list, and then displays the result.

%r

computeMean <- function(index){
    set.seed(index)
    x <- round(runif(100,2,10),4)
    return(mean(x))
}

ore.indexApply(12, computeMean)

The output is similar to the following:

$`1`
6.142776
$`2`
5.932833
$`3`
5.872673
$`4`
6.383635
$`5`
6.147493
$`6`
6.251832
$`7`
6.07391
$`8`
5.981312
$`9`
5.927451
$`10`
5.562602
$`11`
5.320832
$`12`
5.837725

Listing for This Example

R> res <- ore.indexApply(5,
+       function(index) {
+         paste("IndexApply:", index)
+       },
+       parallel = TRUE)
R> class(res)
[1] "ore.list"
attr(,"package")
[1] "OREembed"
R> res
$`1`
[1] "IndexApply: 1"
 
$`2`
[1] "IndexApply: 2"
 
$`3`
[1] "IndexApply: 3"
 
$`4`
[1] "IndexApply: 4"
 
$`5`
[1] "IndexApply: 5"

10.3.7.2 Column-Parallel Use Case

The example uses the R summary function to compute in parallel summary statistics on the first four numeric columns of the iris data set.

Example 10-13 Using the ore.indexApply Function and Combining Results

The example combines the computations into a final result. The first argument to the ore.indexApply function is 4, which specifies the number of columns to summarize in parallel. The user-defined input function takes one argument, index, which will be a value between 1 and 4 and which specifies the column to summarize.

The example calls the summary function on the specified column. The summary invocation returns a single row, which contains the summary statistics for the column. The example converts the result of the summary invocation into a data.frame and adds the column name to it.

The example next uses the FUN.VALUE argument to the ore.indexApply function to define the structure of the result of the function. The result is then returned as an ore.frame object with that structure.

%r

res <- ore.indexApply(4,
      function(index) {
        ss <- summary(iris[, index])
        attr.names <- attr(ss, "names")
        stats <- data.frame(matrix(ss, 1, length(ss)))
        names(stats) <- attr.names
        stats$col <- names(iris)[index]
        stats
      },
      FUN.VALUE=data.frame(Min. = numeric(0),
        "1st Qu." = numeric(0),
        Median = numeric(0),
        Mean = numeric(0),
        "3rd Qu." = numeric(0),
        Max. = numeric(0),
        Col = character(0)), 
      parallel = TRUE)
res

The output is similar to the following:

Table 10-9 A data.frame: 4 x 7

Min.	X1st.Qu.	Median	Mean	X3rd.Qu.	Max.	Col
<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<chr>
4.3	5.1	5.80	5.843333	6.4	7.9	Sepal.Length
2.0	2.8	3.00	3.057333	3.3	4.4	Sepal.Width
1.0	1.6	4.35	3.758000	5.1	6.9	Petal.Length
0.1	0.3	1.30	1.199333	1.8	2.5	Petal.Width

Listing for This Example

R> res <- ore.indexApply(4,
+       function(index) {
+         ss <- summary(iris[, index])
+         attr.names <- attr(ss, "names")
+         stats <- data.frame(matrix(ss, 1, length(ss)))
+         names(stats) <- attr.names
+         stats$col <- names(iris)[index]
+         stats
+       },
+       FUN.VALUE=data.frame(Min. = numeric(0),
+         "1st Qu." = numeric(0),
+         Median = numeric(0),
+         Mean = numeric(0),
+         "3rd Qu." = numeric(0),
+         Max. = numeric(0),
+         Col = character(0)),
+       parallel = TRUE)
R> res
  Min. X1st.Qu. Median  Mean X3rd.Qu. Max.          Col
1  2.0      2.8   3.00 3.057      3.3  4.4  Sepal.Width
2  4.3      5.1   5.80 5.843      6.4  7.9 Sepal.Length
3  0.1      0.3   1.30 1.199      1.8  2.5  Petal.Width
4  1.0      1.6   4.35 3.758      5.1  6.9 Petal.Length
Warning message:
ORE object has no unique key - using random order

10.3.7.3 Simulations Use Case

You can use the ore.indexApply function in simulations, which can take advantage of high-performance computing hardware like an Oracle Exadata Database Machine.

Example 10-14 Using the ore.indexApply Function in a Simulation

This example takes multiple samples from a random normal distribution to compare the distribution of the summary statistics. Each simulation occurs in a separate R engine in the database, in parallel, up to the degree of parallelism allowed by the database. The example defines variables for the sample size, the mean and standard deviations of the random numbers, and the number of simulations to perform. The example specifies num.simulations as the first argument to the ore.indexApply function. The ore.indexApply function passes num.simulations to the user-defined function as the index argument. This input function then sets the random seed based on the index so that each invocation of the input function generates a different set of random numbers.

The input function next uses the rnorm function to produce sample.size random normal values. It calls the summary function on the vector of random numbers, and then prepares a data.frame as the result it returns. The ore.indexApply function specifies the FUN.VALUE argument so that it returns an ore.frame that structures the combined results of the simulations. The res variable gets the ore.frame returned by the ore.indexApply function.

To get the distribution of samples, the example calls the boxplot function on the data.frame that is the result of using the ore.pull function to bring selected columns from res to the client.

%r

options("ore.warn.order" = FALSE)


sample.size = 1000
mean.val = 100
std.dev.val = 10
num.simulations = 10
 
res <- ore.indexApply(num.simulations,
      function(index, sample.size = 1000, mean = 0, std.dev = 1) {
        set.seed(index)
        x <- rnorm(sample.size, mean, std.dev)
        ss <- summary(x)
        attr.names <- attr(ss, "names")
        stats <- data.frame(matrix(ss, 1, length(ss)))
        names(stats) <- attr.names
        stats$index <- index
        stats
      },
      FUN.VALUE=data.frame(Min. = numeric(0),
        "1st Qu." = numeric(0),
        Median = numeric(0),
        Mean = numeric(0),
        "3rd Qu." = numeric(0),
        Max. = numeric(0),
        Index = numeric(0)),
      parallel = TRUE,
      sample.size = sample.size,
      mean = mean.val, std.dev = std.dev.val)
      

head(res, 3)
tail(res, 3)

boxplot(ore.pull(res[, 1:6]),
  main=sprintf("Boxplot of %d rnorm samples size %d, mean=%d, sd=%d",
               num.simulations, sample.size, mean.val, std.dev.val))

The output is similar to the following:

Table 10-10 A data.frame: 3 x 7

Min.	X1st.Qu.	Median	Mean	X3rd.Qu.	Max.	Index
<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
69.91951	93.02627	99.64676	99.88352	106.8843	138.1028	1
72.78184	93.68699	100.50135	100.61999	107.7106	130.0882	2
69.43672	93.15461	100.32338	100.06397	106.7667	135.1930	3

Table 10-11 A data.frame: 3 x 7

Min.	X1st.Qu.	Median	Mean	X3rd.Qu.	Max.	Index
<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
8	67.18068	92.73174	99.71516	99.58738	106.6340	129.7804	8
9	69.58926	93.51445	100.31074	100.05885	106.6231	127.6253	9
10	69.87836	93.22607	99.96999	100.11375	107.2746	135.4114	10

Listing for This Example

R> res <- ore.indexApply(num.simulations,
+       function(index, sample.size = 1000, mean = 0, std.dev = 1) {
+         set.seed(index)
+         x <- rnorm(sample.size, mean, std.dev)
+         ss <- summary(x)
+         attr.names <- attr(ss, "names")
+         stats <- data.frame(matrix(ss, 1, length(ss)))
+         names(stats) <- attr.names
+         stats$index <- index
+         stats
+       },
+       FUN.VALUE=data.frame(Min. = numeric(0),
+         "1st Qu." = numeric(0),
+         Median = numeric(0),
+         Mean = numeric(0),
+         "3rd Qu." = numeric(0),
+         Max. = numeric(0),
+         Index = numeric(0)),
+       parallel = TRUE,
+       sample.size = sample.size,
+       mean = mean.val, std.dev = std.dev.val)
R> options("ore.warn.order" = FALSE)
R> head(res, 3)
   Min. X1st.Qu. Median   Mean X3rd.Qu.  Max. Index
1 67.56    93.11  99.42  99.30    105.8 128.0   847
2 67.73    94.19  99.86 100.10    106.3 130.7   258
3 65.58    93.15  99.78  99.82    106.2 134.3   264
R> tail(res, 3)
   Min. X1st.Qu. Median   Mean X3rd.Qu.  Max. Index
1 65.02    93.44  100.2 100.20    106.9 134.0     5
2 71.60    93.34   99.6  99.66    106.4 131.7     4
3 69.44    93.15  100.3 100.10    106.8 135.2     3
R> boxplot(ore.pull(res[, 1:6]),
+   main=sprintf("Boxplot of %d rnorm samples size %d, mean=%d, sd=%d",
+                num.simulations, sample.size, mean.val, std.dev.val))

Figure 10-2 Display of the boxplot Function in Example 10-14

Description of "Figure 10-2 Display of the boxplot Function in Example 10-14"

10.4 SQL Interface for Embedded R Execution

SQL Interface for Oracle Machine Learning for R Embedded R execution allows you to run R functions in production database applications.

The SQL interface has procedures for the following actions:

• Adding and removing a script from the OML4R script repository

• Granting or revoking read privilege access to a script by the owner to other users

• Executing an R script in an embedded R session

• Deleting an OML4R datastore

Data dictionary views provide information about scripts and datastores.

This SQL interface is described in the following topics:

• About Oracle Machine Learning for R SQL Table Functions
  OML4R provides SQL table functions that are equivalents of most of the R interface functions for embedded R execution.
• Manage Scripts in SQL
  This topic lists the PL/SQL procedures and Oracle Database data dictionary views for creating and managing R scripts.
• Manage Datastores in SQL
  Oracle Machine Learning for R provides PL/SQL procedures and Oracle Database data dictionary views for the basic management of datastores in SQL.

10.4.1 About Oracle Machine Learning for R SQL Table Functions

OML4R provides SQL table functions that are equivalents of most of the R interface functions for embedded R execution.

Executing a SELECT FROM TABLE statement and specifying one of the table functions results in the invocation of the specified R script. The script runs in one or more R engines on the Oracle Database server.

The SQL table functions for embedded R execution are:

• rqEval2

• rqGroupEval2

• rqRowEval2

• rqTableEval2

The R interface functions and the SQL equivalents are listed in Table 10-1.

For the rqGroupEval2 function, OML4R provides a generic implementation of the group apply functionality in SQL. You must write a table function that captures the structure of the input cursor.

See the reference pages for the functions for information about them, including examples of their use.

Some general aspects of the SQL table functions are described in the following topics:

• Parameters of the SQL Table Functions
  The SQL table functions have some parameters in common and some functions have parameters that are unique to that function.
• Return Value of SQL Table Functions
  The Oracle Machine Learning for R SQL table functions return a table.
• Connect to Oracle Machine Learning for R in Embedded R Execution
  To establish a connection to OML4R on the Oracle Database server during the Embedded R Execution, you can specify the control argument ore.connect in the parameters list.

10.4.1.1 Parameters of the SQL Table Functions

The SQL table functions have some parameters in common and some functions have parameters that are unique to that function.

The parameters of the SQL table functions are the following.

Table 10-12 SQL Table Function Parameters

Parameter	Description
INP_NAM	The name of a table or view that specifies the data to pass to the R function specified by the SCR_NAME parameter. If you use a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view. Note: This argument is for all of the SQL table functions except rqEval2.
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the SCR_NAME parameter. Special control arguments, which start with ore, are not passed to the function specified by SCR_NAME, instead control what happens before or after the invocation of the function. For example, to omit rows with missing values from input table, use: {"ore.na.omit":true} See also: Special Control Arguments
OUT_FMT	An output table definition. The value of this argument can be NULL or a string that defines the structure of the R data.frame returned by the R function specified by SCR_NAME. The string can be a SELECT statement, 'XML', or 'PNG'. The format of the output returned by the function. It can be one of the following: The name of a table or view to use as a prototype. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view. A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as PNG bytes. See also: Output Formats
GRP_COL	For the rqGroupEval2 function, the name of the grouping column.
ROW_NUM	For the rqRowEval2 function, the number of rows to pass to each invocation of the R function.
SCR_NAME	The name of a script in the OML4R script repository.
SCR_OWNER	The owner of a script in the OML4R script repository. The default value is NULL. If the value is NULL, search for the R script in the user's script repository.

10.4.1.2 Return Value of SQL Table Functions

The Oracle Machine Learning for R SQL table functions return a table.

The structure and contents of the table are determined by the results of the R function passed to the SQL table function and by the OUT_FMT parameter. The R function can return a data.frame object, other R objects, and graphics. The structure of the table that represents the results of the R function is specified by one of the following OUT_FMT values:

• The name of a table or view to use as a prototype. If you are using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.

• A JSON string that specifies the column names and data types of the table returned by the function. The result of the R function must be a data.frame. No images are returned.

• The string 'PNG', which results in a table that has a BLOB that contains graph images in PNG format. The table has the column names name, id, and image.

• The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function.

10.4.1.3 Connect to Oracle Machine Learning for R in Embedded R Execution

To establish a connection to OML4R on the Oracle Database server during the Embedded R Execution, you can specify the control argument ore.connect in the parameters list.

Doing so establishes a connection using the credentials of the user who invoked the embedded R function. It also automatically loads the ORE package. Establishing an OML4R connection is required to save objects in an OML4R R object datastore or to load objects from a datastore. It also allows you to explicitly use the OML4R transparency layer.

See Also:

Optional and Control Arguments for information on other control arguments

Example 10-15 Connect to OML4R in Embedded R execution

This example establishes a connection to OML4R on the Oracle Database server during the Embedded R Execution.

select VALUE from table(rqEval2('{"ore.connect":1}', 'XML', '<script name>'));

10.4.2 Manage Scripts in SQL

This topic lists the PL/SQL procedures and Oracle Database data dictionary views for creating and managing R scripts.

The functions in the SQL API for Embedded R Execution require as an argument a named script that is stored in the OML4R script repository. The PL/SQL procedures sys.rqScriptCreate and sys.rqScriptDrop create and drop scripts. To create a script or drop one from the script repository requires the RQADMIN role.

When using the sys.rqScriptCreate function, you must specify a name for the script and an R function script that contains a single R function definition. Calls to the functions sys.rqScriptCreate and sys.rqScriptDrop must be wrapped in a BEGIN-END PL/SQL block. The script repository stores the R function as a character large object (a CLOB), so you must enclose the function definition in single quotes to specify it as a string.

The owner of a script can use the rqGrant procedure to grant to another user read privilege access to a script or use the rqRevoke procedure to revoke the privilege. To use a script granted to you by another user, you must specify the owner by prepending the owner’s name and a period to the name of the script, as in the following:

select * from table(rqEval2(NULL, '{"x": 1}', 'owner_name.script_name'));

The owner prefix is not required for a public script or for a script owned by the user.

The following tables list the PL/SQL procedures for managing script repository scripts and the data dictionary views that contain information about scripts.

Table 10-13 PL/SQL Procedures for Managing Scripts

PL/SQL Procedure	Description
rqGrant	Grants read privilege access to a datastore or script.
rqRevoke	Revokes read privilege access to a datastore or script.
sys.rqScriptCreate	Adds the provided R function into the script repository with the provided name.
sys.rqScriptDrop	Removes the named R function from the script repository.

Table 10-14 Data Dictionary Views for Scripts

Data Dictionary View	Description
ALL_RQ_SCRIPTS	Describes the scripts in the OML4R script repository that are available to the current user
USER_RQ_SCRIPTS	Describes the scripts in the script repository that are owned by the current user.
USER_RQ_SCRIPT_PRIVS	Describes the scripts in the script repository to which the current user has granted read access and the users to whom access has been granted.
SYS.RQ_SCRIPTS	Describes the system scripts in the script repository.

Example 10-16 Create a Script with the SQL APIs

This example uses the sys.rqScriptCreate procedure to create a script in the Oracle Machine Learning for R script repository.

The example creates the user-defined function named myRandomRedDots2. The user-defined function accepts two arguments, and it returns a data.frame object that has two columns and that plots the specified number of random normal values. The sys.rqScriptCreate function stores the user-defined function in the OML4R script repository.

-- Create a script named myRandomRedDots2 and add it to the script repository.
-- Specify that the script is private and to overwrite a script with the same name.
BEGIN
  sys.rqScriptCreate('myRandomRedDots2',
    'function(divisor = 100, numDots = 100) {
       id <- 1:10
       plot(1:numDots, rnorm(numDots), pch = 21, bg = "red", cex = 2 )
       data.frame(id = id, val = id / divisor)}',
       v_global => FALSE,
       v_overwrite => TRUE);
END;
/

-- Grant read privilege access to OMLUSER.
BEGIN
  rqGrant('myRandomRedDots2', 'rqscript', 'OMLUSER');
END;
 /

-- View the users granted read access to myRandomRedDots2.
select * from USER_RQ_SCRIPT_PRIVS;

NAME                GRANTEE
----------------    -------
myRandomRedDots       OMLUSER

-- Revoke the read privilege access from OMLUSER.
BEGIN
  rqRevoke('myRandomRedDots2', 'rqscript', 'OMLUSER');
END;
 /

-- Remove the script from the script repository.
BEGIN
  sys.rqScriptDrop('myRandomRedDots2');
 END; 
 /

10.4.3 Manage Datastores in SQL

Oracle Machine Learning for R provides PL/SQL procedures and Oracle Database data dictionary views for the basic management of datastores in SQL.

The following tables list the procedures and views.

Table 10-15 PL/SQL Procedures for Managing Datastores

PL/SQL Procedures	Description
rqGrant	Grants read privilege access to a datastore or script.
rqRevoke	Revokes read privilege access to a datastore or script.
rqDropDataStore	Deletes a datastore.

Table 10-16 Data Dictionary Views for Datastores

Views	Description
ALL_RQ_DATASTORES	Describes the datastores available to the current user, including whether the datastore is grantable.
RQUSER_DATASTORELIST	Describes the datastores in the Oracle Database schema..
RQUSER_DATASTORECONTENTS	Describes the objects in the datastores in the Oracle Database schema.
USER_RQ_DATASTORE_PRIVS	Describes the datastores and the users to whom the current user has granted read privilege access.
USER_RQ_DATASTORES	Describes the datastores owned by the current user, including whether the datastore is grantable.

10.5 SQL API for Embedded R Execution with On-premises Database

The OML4R SQL APIs comprise SQL table functions for executing R functions in one or more embedded R sessions on the OML4R Server database, and PL/SQL procedures for managing OML4R datastores and for managing scripts in the OML4R script repository.

The SQL API for Embedded R Execution with On-premises Database is described in the following topics:

• rqDropDataStore Procedure
  The rqDropDataStore procedure deletes a datastore from an Oracle Database schema.
• rqEval Function
  The rqEval function executes the R function in the script specified by the EXP_NAM parameter.
• rqGrant Procedure
  The rqGrant procedure grants read privilege access to an OML4R datastore or to a script in the OML4R script repository.
• rqGroupEval Function
  The rqGroupEval function is a user-defined function that identifies a grouping column.
• rqRevoke Procedure
  The rqRevoke procedure revokes read privilege access to an OML4R datastore or to a script in the OML4R script repository.
• rqRowEval Function
  The rqRowEval function executes the R function in the script specified by the EXP_NAM parameter.
• rqTableEval Function
  The rqTableEval function executes the R function in the script specified by the EXP_NAM parameter.
• sys.rqScriptCreate Procedure
  The sys.rqScriptCreate procedure creates a script and adds it to the OML4R script repository.
• sys.rqScriptDrop Procedure
  The sys.rqScriptDrop procedure removes a script from the OML4R script repository.

10.5.1 rqDropDataStore Procedure

The rqDropDataStore procedure deletes a datastore from an Oracle Database schema.

Syntax

rqDropDataStore (
    DS_NAME     VARCHAR2     IN)

Parameters

Parameter	Description
DS_NAME	The name of the datastore to drop.

Example 10-17 Dropping a Datastore

This example deletes the datastore datastore_1 from the current user schema.

rqDropDataStore('datastore_1')

10.5.2 rqEval Function

The rqEval function executes the R function in the script specified by the EXP_NAM parameter.

You can pass arguments to the R function with the PAR_CUR parameter.

The rqEval function does not automatically receive any data from the database. The R function generates the data that it uses or it explicitly retrieves it from a data source such as Oracle Database, other databases, or flat files.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

Syntax

rqEval (
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN)
     EXP_NAM     VARCHAR2       IN)

Parameters

Parameter	Description
PAR_CUR	A cursor that contains argument values to pass to the R function specified by the EXP_NAME parameter.
OUT_QRY	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL SELECT statement that specifies the column names and data types of the table returned by rqEval. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a data.frame. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
EXP_NAM	The name of a script in the OML4R script repository.

Return Value

Function rqEval returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

Example 10-18 Using rqEval

This example creates the script myRandomRedDots2. The value of the first parameter to rqEval is NULL, which specifies that no arguments are supplied to the function myRandomRedDots2. The value of second parameter is a string that specifies a SQL statement that describes the column names and data types of the data.frame returned by rqEval. The value of third parameter is the name of the script in the OML4R script repository.

-- Create a script named myRandomRedDots2 and add it to the script repository.
-- Specify that the script is private and to overwrite a script with the same name.
BEGIN
  sys.rqScriptCreate('myRandomRedDots2',
    'function(divisor = 100, numDots = 100) {
       id <- 1:10
       plot(1:numDots, rnorm(numDots), pch = 21, bg = "red", cex = 2 )
       data.frame(id = id, val = id / divisor)}',
       v_global => FALSE,
       v_overwrite => TRUE);
END;
/

SELECT *
  FROM table(rqEval(NULL, 'SELECT 1 id, 1 val FROM dual', 'myRandomRedDots2'));

In Oracle SQL Developer, the results of the SELECT statement are:

        ID        VAL
---------- ----------
         1        .01 
         2        .02 
         3        .03 
         4        .04 
         5        .05 
         6        .06 
         7        .07 
         8        .08 
         9        .09 
        10         .1 
 
 10 rows selected 

Example 10-19 Passing Arguments to the R Function invoked by rqEval

This example provides arguments to the R function by specifying a cursor as the first parameter to rqEval. The cursor specifies multiple arguments in a single row of scalar values.

SELECT *
  FROM table(rqEval(cursor(SELECT 50 "divisor", 500 "numDots" FROM dual),
    'SELECT 1 id, 1 val FROM dual',
    'myRandomRedDots2'));

In Oracle SQL Developer, the results of the SELECT statement are:

        ID        VAL
---------- ----------
         1        .02 
         2        .04 
         3        .06 
         4        .08 
         5         .1 
         6        .12 
         7        .14 
         8        .16 
         9        .18 
        10         .2 
 
 10 rows selected 

Example 10-20 Specifying PNG as the Output Table Definition

This example creates a script named PNG_Example and stores it in the script repository. The invocation of rqEval specifies an OUT_QRY value of 'PNG'.

BEGIN
  sys.rqScriptDrop('PNG_Example');
  sys.rqScriptCreate('PNG_Example',
    'function(){
      dat <- data.frame(y = log(1:100), x = 1:100)
      plot(lm(y ~ x, dat))
      }');
END;
/
SELECT *
  FROM table(rqEval(NULL,'PNG','PNG_Example'));

In Oracle SQL Developer, the results of the SELECT statement are:

NAME     ID  IMAGE
------ ----  ------
          1  (BLOB)
          2  (BLOB)
          3  (BLOB)
          4  (BLOB)

10.5.3 rqGrant Procedure

The rqGrant procedure grants read privilege access to an OML4R datastore or to a script in the OML4R script repository.

Syntax

rqGrant (
    V_NAME     VARCHAR2     IN
    V_TYPE     VARCHAR2     IN
    V_USER     VARCHAR2     IN     DEFAULT)
  

Parameters

Parameter	Description
V_NAME	The name of an OML4R datastore or a script in the OML4R script repository.
V_TYPE	For a datastore, the type is datastore; for a script, the type is rqscript.
V_USER	The name of the user to whom to grant access.

Example 10-21 Granting Read Access to a Script

-- Grant read privilege access to Scott.
BEGIN
  rqGrant('myRandomRedDots2', 'rqscript', 'SCOTT');
END;
/

10.5.4 rqGroupEval Function

The rqGroupEval function is a user-defined function that identifies a grouping column.

The user defines an rqGroupEval function in PL/SQL using the SQL object rqGroupEvalImpl, which is a generic implementation of the group apply functionality in SQL. The implementation supports data-parallel execution, in which one or more R engines perform the same R function, or task, on different partitions of data. The data is partitioned according to the values of the grouping column.

Only one grouping column is supported. If you have multiple columns, then combine the columns into one column and use the new column as the grouping column.

The rqGroupEval function executes the R function in the script specified by the EXP_NAM parameter. You pass data to the R function with the INP_CUR parameter. You can pass arguments to the R function with the PAR_CUR parameter.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

To create an rqGroupEval function, you create the following two PL/SQL objects:

• A PL/SQL package that specifies the types of the result to return.

• A function that takes the return value of the package and uses the return value with PIPELINED_PARALLEL_ENABLE set to indicate the column on which to partition data.

Syntax

rqGroupEval (
     INP_CUR     REF CURSOR     IN
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN
     GRP_COL     VARCHAR2       IN
     EXP_NAM     VARCHAR2       IN)

Parameters

Parameter	Description
INP_CUR	A cursor that specifies the data to pass to the R function specified by the EXP_NAME parameter.
PAR_CUR	A cursor that contains argument values to pass to the R function.
OUT_QRY	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL SELECT statement that specifies the column names and data types of the table returned by rqEval. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a data.frame. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
GRP_COL	The name of the grouping column by which to partition the data.
EXP_NAM	The name of a script in the OML4R script repository.

Return Value

The user-defined rqGroupEval function returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

This example has a PL/SQL block that drops the script myC5.0Function to ensure that the script does not exist in the OML4R script repository. It then creates a function and stores it as the script myC5.0Function in the script repository.

The R function accepts two arguments: the data on which to operate and a prefix to use in creating datastores. The function uses the C50 package to build C5.0 models on the churn data set from C50. The function builds one churn model on the data for each state.

The myC5.0Function function loads the C50 package so that the function body has access to it when the function executes in an R engine on the database server. The function then creates a datastore name using the datastore prefix and the name of a state. To exclude the state name from the model, the function deletes the column from the data.frame. Because factors in the data.frame are converted to character vectors when they are loaded in the user-defined embedded R function, the myC5.0Function function explicitly converts the character vectors back to R factors.

The myC5.0Function function gets the data for the state from the specified columns and then creates a model for the state and saves the model in a datastore. The R function returns TRUE to have a simple value that can appear as the result of the function execution.

The example next creates a PL/SQL package, churnPkg, and a user-defined function, churnGroupEval. In defining an rqGroupEval function implementation, the PARALLEL_ENABLE clause is optional but the CLUSTER BY clause is required.

Finally, the example executes a SELECT statement that invokes the churnGroupEval function. In the INP_CUR argument of the churnGroupEval function, the SELECT statement specifies the PARALLEL hint to use parallel execution of the R function and the data set to pass to the R function. The INP_CUR argument of the churnGroupEval function specifies connecting to OML4R and the datastore prefix to pass to the R function. The OUT_QRY argument specifies returning the value in XML format, the GRP_NAM argument specifies using the state column of the data set as the grouping column, and the EXP_NAM argument specifies the myC5.0Function script in the script repository as the R function to invoke.

For each of 50 states plus Washington, D.C., the SELECT statement returns from the churnGroupEval table function the name of the state and an XML string that contains the value TRUE.

Example 10-22 Using an rqGroupEval Function

BEGIN
  sys.rqScriptDrop('myC5.0Function');
  sys.rqScriptCreate('myC5.0Function',
    'function(dat, datastorePrefix) {
       library(C50)
       datastoreName <- paste(datastorePrefix, dat[1, "state"], sep = "_")
       dat$state <- NULL
       dat$churn <- as.factor(dat$churn)
       dat$area_code <- as.factor(dat$area_code)
       dat$international_plan <- as.factor(dat$international_plan)
       dat$voice_mail_plan <- as.factor(dat$voice_mail_plan)
       mod <- C5.0(churn ~ ., data = dat, rules = TRUE)
       ore.save(mod, name = datastoreName)
       TRUE
      }');
END;
/

CREATE OR REPLACE PACKAGE churnPkg AS
  TYPE cur IS REF CURSOR RETURN CHURN_TRAIN%ROWTYPE;
END churnPkg;
/
CREATE OR REPLACE FUNCTION churnGroupEval(
  inp_cur churnPkg.cur,
  par_cur SYS_REFCURSOR, 
  out_qry VARCHAR2,
  grp_col VARCHAR2,
  exp_txt CLOB)
RETURN SYS.AnyDataSet
PIPELINED PARALLEL_ENABLE (PARTITION inp_cur BY HASH ("state"))
CLUSTER inp_cur BY ("state")
USING rqGroupEvalImpl;
/
 
SELECT *
  FROM table(churnGroupEval(
    cursor(SELECT * /*+ parallel(t,4) */ FROM CHURN_TRAIN t),
    cursor(SELECT 1 AS "ore.connect",
           'myC5.0model' AS "datastorePrefix" FROM dual),
    'XML', 'state', 'myC5.0Function'));

10.5.5 rqRevoke Procedure

The rqRevoke procedure revokes read privilege access to an OML4R datastore or to a script in the OML4R script repository.

Syntax

rqGrant (
    V_NAME     VARCHAR2     IN
    V_TYPE     VARCHAR2     IN
    V_USER     VARCHAR2     IN     DEFAULT)
  

Parameters

Parameter	Description
V_NAME	The name of an OML4R datastore or a script in the OML4R script repository.
V_TYPE	For a datastore, the type is datastore; for a script, the type is rqscript.
V_USER	The name of the user from whom to revoke access.

Example 10-23 Revoking Read Access to a Script

-- Revoke read privilege access to Scott.
BEGIN
  rqRevoke('myRandomRedDots2', 'rqscript', 'SCOTT');
END;
/

10.5.6 rqRowEval Function

The rqRowEval function executes the R function in the script specified by the EXP_NAM parameter.

You pass data to the R function with the INP_CUR parameter. You can pass arguments to the R function with the PAR_CUR parameter. The ROW_NUM parameter specifies the number of rows that should be passed to each invocation of the R function. The last chunk may have fewer rows than the number specified.

The rqRowEval function supports data-parallel execution, in which one or more R engines perform the same R function, or task, on disjoint chunks of data. Oracle Database handles the management and control of the potentially multiple R engines that run on the database server machine, automatically chunking and passing data to the R engines executing in parallel. Oracle Database ensures that R function executions for all chunks of rows complete, or the rqRowEval function returns an error.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

Syntax

rqRowEval (
     INP_CUR     REF CURSOR     IN
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN
     ROW_NUM     NUMBER         IN
     EXP_NAM     VARCHAR2       IN)

Parameters

Table 10-17 Parameters of the rqRowEval Function

Parameter	Description
INP_CUR	A cursor that specifies the data to pass to the R function specified by the EXP_NAME parameter.
PAR_CUR	A cursor that contains argument values to pass to the R function.
OUT_QRY	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL SELECT statement that specifies the column names and data types of the table returned by rqEval. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a data.frame. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
ROW_NUM	The number of rows to include in each invocation of the R function.
EXP_NAM	The name of a script in the OML4R script repository.

Return Value

Function rqRowEval returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

This example uses the C50 package to score churn data (that is, to predict which customers are likely to churn) using C5.0 decision tree models. The example scores the customers from the specified state in parallel. This example produces the same result as the invocation of function ore.rowApply.

Tip:

This example uses the CHURN_TEST table and the myXLevels datastore. So in R you should invoke the functions that create the table and that get the xlevels object and save it in the myXLevels datastore before running this example.

Example 10-24 Using an rqRowEval Function

This example creates a user-defined function and saves the function in the OML4R script repository. The user-defined function creates a C5.0 model for a state and saves the model in a datastore. The function myC5.0FunctionForLevels returns the value TRUE.

This example creates the PL/SQL package churnPkg and the function churnGroupEval. The example declares a cursor to get the names of the datastores that include the string myC5.0modelFL and then executes a PL/SQL block that deletes those datastores. The example next executes a SELECT statement that invokes the churnGroupEval function. The churnGroupEval function invokes the myC5.0FunctionForLevels function to generate the C5.0 models and save them in datastores.

The example then creates the myScoringFunction function and stores it in the script repository. The function scores a C5.0 model for the levels of a state and returns the results in a data.frame.

Finally, the example executes a SELECT statement that invokes the rqRowEval function. The input cursor to the rqRowEval function uses the PARALLEL hint to specify the degree of parallelism to use. The cursor specifies the CHURN_TEST table as the data source and filters the rows to include only those for Massachusetts. All rows processed use the same predictive model.

The parameters cursor specifies the ore.connect control argument to connect to OML4R on the database server and specifies values for the datastorePrefix and xlevelsDatastore arguments to the myScoringFunction function.

The SELECT statement for the OUT_QRY parameter specifies the format of the output. The ROW_NUM parameter specifies 200 as the number of rows to process at a time in each parallel R engine. The EXP_NAME parameter specifies myScoringFunction in the script repository as the R function to invoke.

BEGIN
  sys.rqScriptDrop('myC5.0FunctionForLevels');
  sys.rqScriptCreate('myC5.0FunctionForLevels',
    'function(dat, xlevelsDatastore, datastorePrefix) {
       library(C50)
       state <- dat[1,"state"]
       datastoreName <- paste(datastorePrefix, dat[1, "state"], sep = "_")
       dat$state <- NULL
       ore.load(name = xlevelsDatastore) # To get the xlevels object.
       for (j in names(xlevels))
         dat[[j]] <- factor(dat[[j]], levels = xlevels[[j]])
       c5mod <- C5.0(churn ~ ., data = dat, rules = TRUE)
       ore.save(c5mod, name = datastoreName)
       TRUE
      }');
END;
/

CREATE OR REPLACE PACKAGE churnPkg AS
  TYPE cur IS REF CURSOR RETURN CHURN_TEST%ROWTYPE;
END churnPkg;
/
CREATE OR REPLACE FUNCTION churnGroupEval(
  inp_cur churnPkg.cur,
  par_cur SYS_REFCURSOR, 
  out_qry VARCHAR2,
  grp_col VARCHAR2,
  exp_txt CLOB)
RETURN SYS.AnyDataSet
PIPELINED PARALLEL_ENABLE (PARTITION inp_cur BY HASH ("state"))
CLUSTER inp_cur BY ("state")
USING rqGroupEvalImpl;
/

DECLARE
 CURSOR c1
  IS
    SELECT dsname FROM RQUSER_DATASTORELIST WHERE dsname like 'myC5.0modelFL%';

BEGIN
  FOR dsname_st IN c1
  LOOP
    rqDropDataStore(dsname_st.dsname);
  END LOOP;
END;

SELECT *
  FROM table(churnGroupEval(
    cursor(SELECT * /*+ parallel(t,4) */ FROM CHURN_TEST t),
    cursor(SELECT 1 AS "ore.connect",
           'myXLevels' as "xlevelsDatastore",
           'myC5.0modelFL' AS "datastorePrefix" FROM dual),
    'XML', 'state', 'myC5.0FunctionForLevels'));

BEGIN
  sys.rqScriptDrop('myScoringFunction');
  sys.rqScriptCreate('myScoringFunction',
    'function(dat, xlevelsDatastore, datastorePrefix) {
       library(C50)
       state <- dat[1, "state"]
       datastoreName <- paste(datastorePrefix, state, sep = "_")
       dat$state <- NULL
       ore.load(name = xlevelsDatastore) # To get the xlevels object.
       for (j in names(xlevels))
         dat[[j]] <- factor(dat[[j]], levels = xlevels[[j]])
       ore.load(name = datastoreName)
       res <- data.frame(pred = predict(c5mod, dat, type = "class"),
                         actual= dat$churn,
                         state = state)
       res
     }');
END;
/

SELECT * FROM table(rqRowEval(
  cursor(select /*+ parallel(t, 4) */ *
         FROM CHURN_TEST t
         WHERE "state" = 'MA'),
  cursor(SELECT 1 as "ore.connect", 
         'myC5.0modelFL' as "datastorePrefix",
         'myXLevels' as "xlevelsDatastore"
         FROM dual),
  'SELECT ''aaa'' "pred",''aaa'' "actual" , ''aa'' "state" FROM dual',
   200, 'myScoringFunction'));

In Oracle SQL Developer, the results of the last SELECT statement are:

pred actual state
---- ------ -----
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   yes    MA    
yes  yes    MA    
yes  yes    MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
yes  yes    MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   no     MA    
no   yes    MA    
no   no     MA    
no   no     MA    
no   no     MA    
yes  yes    MA    
no   no     MA    
no   no     MA    
no   no     MA    
 
 38 rows selected

10.5.7 rqTableEval Function

The rqTableEval function executes the R function in the script specified by the EXP_NAM parameter.

You pass data to the R function with the INP_CUR parameter. You can pass arguments to the R function with the PAR_CUR parameter.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

Syntax

rqTableEval (
     INP_CUR     REF CURSOR     IN
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN
     EXP_NAM     VARCHAR2       IN)

Parameters

Table 10-18 Parameters of the rqTableEval Function

Parameter	Description
INP_CUR	A cursor that specifies the data to pass to the R function specified by the EXP_NAME parameter.
PAR_CUR	A cursor that contains argument values to pass to the input function.
OUT_QRY	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL SELECT statement that specifies the column names and data types of the table returned by rqEval. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a data.frame. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
EXP_NAM	The name of a script in the OML4R script repository.

Return Value

Function rqTableEval returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

This example first has a PL/SQL block that drops the script myNaiveBayesModel to ensure that the script does not exist in the OML4R script repository. It then creates a function and stores it as the script myNaiveBayesModel in the repository.

The R function accepts two arguments: the data on which to operate and the name of a datastore. The function builds a Naive Bayes model on the iris data set. Naive Bayes is found in the e1071 package.

The myNaiveBayesModel function loads the e1071 package so that the function body has access to it when the function executes in an R engine on the database server. Because factors in the data.frame are converted to character vectors when they are loaded in the user-defined embedded R function, the myNaiveBayesModel function explicitly converts the character vector to an R factor.

The myNaiveBayesModel function gets the data from the specified column and then creates a model and saves it in a datastore. The R function returns TRUE to have a simple value that can appear as the result of the function execution.

The example next executes a SELECT statement that invokes the rqTableEval function. In the INP_CUR argument of the rqTableEval function, the SELECT statement specifies the data set to pass to the R function. The data is from the IRIS table that was created by invoking ore.create(iris, "IRIS"), which is not shown in the example. The INP_CUR argument of the rqTableEval function specifies the name of a datastore to pass to the R function and specifies the ore.connect control argument to establish an OML4R connection to the database during the embedded R execution of the user-defined R function. The OUT_QRY argument specifies returning the value in XML format, and the EXP_NAM argument specifies the myNaiveBayesModel script in the script repository as the R function to invoke.

Example 10-25 Using the rqTableEval Function

BEGIN
  sys.rqScriptDrop('myNaiveBayesModel');
  sys.rqScriptCreate('myNaiveBayesModel',
    'function(dat, datastoreName) {
       library(e1071)
       dat$Species <- as.factor(dat$Species)
       nbmod <- naiveBayes(Species ~ ., dat)
       ore.save(nbmod, name = datastoreName)
       TRUE
     }');
END;
/

SELECT *
  FROM table(rqTableEval(
    cursor(SELECT * FROM IRIS),
    cursor(SELECT 'myNaiveBayesDatastore' "datastoreName", 
           1 as "ore.connect" FROM dual),
    'XML', 'myNaiveBayesModel'));

The SELECT statement returns from the rqTableEval table function an XML string that contains the value TRUE.

The myNaiveBayesDatastore datastore now exists and contains the object nbmod, as shown by the following SELECT statement.

SQL> SELECT * from RQUSER_DATASTORECONTENTS 
  2     WHERE dsname = 'myNaiveBayesDatastore';
 
DSNAME                  OBJNAME  CLASS       OBJSIZE  LENGTH  NROW  NCOL
---------------------   -------  ----------  -------  ------  ----  ----
myNaiveBayesDatastore   nbmod    naiveBayes     1485       4

In a local R session, you could load the model and display it, as in the following:

R> ore.load("myNaiveBayesDatastore")
[1] "nbmod"
R> nbmod
$apriori
Y
    setosa versicolor  virginica 
        50         50         50 
 
$tables
$tables$Sepal.Length
            Sepal.Length
Y             [,1]      [,2]
  setosa     5.006 0.3524897
  versicolor 5.936 0.5161711
  virginica  6.588 0.6358796
 
$tables$Sepal.Width
            Sepal.Width
Y             [,1]      [,2]
  setosa     3.428 0.3790644
  versicolor 2.770 0.3137983
  virginica  2.974 0.3224966
 
$tables$Petal.Length
            Petal.Length
Y             [,1]      [,2]
  setosa     1.462 0.1736640
  versicolor 4.260 0.4699110
  virginica  5.552 0.5518947
 
$tables$Petal.Width
            Petal.Width
Y             [,1]      [,2]
  setosa     0.246 0.1053856
  versicolor 1.326 0.1977527
  virginica  2.026 0.2746501
 
 
$levels
[1] "setosa"     "versicolor" "virginica" 
 
$call
naiveBayes.default(x = X, y = Y, laplace = laplace)
 
attr(,"class")
[1] "naiveBayes"

10.5.8 sys.rqScriptCreate Procedure

The sys.rqScriptCreate procedure creates a script and adds it to the OML4R script repository.

Syntax

sys.rqScriptCreate (
    V_NAME          VARCHAR2    IN
    V_SCRIPT        CLOB        IN
    V_GLOBAL        BOOLEAN     IN     DEFAULT
    V_OVERWRITE     BOOLEAN     IN     DEFAULT)

Parameter	Description
V_NAME	A name for the script in the OML4R script repository.
V_SCRIPT	The R function definition to store in the script.
V_GLOBAL	TRUE specifies that the script is public; FALSE specifies that the script is private.
V_OVERWRITE	If the OML4R script repository already has a script with the same name as V_NAME, then TRUE replaces the content of that script with V_SCRIPT and FALSE does not replace it.

10.5.9 sys.rqScriptDrop Procedure

The sys.rqScriptDrop procedure removes a script from the OML4R script repository.

Syntax

sys.rqScriptDrop (
    V_NAME          VARCHAR2    IN
    V_GLOBAL        BOOLEAN     IN     DEFAULT
    V_SILENT        BOOLEAN     IN     DEFAULT)

Parameter	Description
V_NAME	A name for the script in the OML4R script repository.
V_GLOBAL	TRUE (the default) specifies that the script is public; FALSE specifies that the script is private.
V_SILENT	FALSE (the default) specifies that sys.rqqScriptDrop displays an error message if it encounters an error in dropping the specified R script. TRUE specifies that the procedure does not display an error message.

10.6 SQL API for Embedded R Execution with Autonomous Database

The SQL API for Embedded R Execution with Autonomous Database provides SQL interfaces for setting authorization tokens, managing access control list (ACL) privileges, executing R scripts, and synchronously and asynchronously running jobs.

The following topics describe the SQL API.

• Access and Authorization Procedures and Functions
• Embedded R Execution Functions (Autonomous Database)
• ore_async_flag Argument
• Special Control Arguments
• Output Formats

• Access and Authorization Procedures and Functions
  Use the network access control lists (ACL) API to control access by users to external network services and resources from the database. Use the token store API to persist the authorization token issued by a cloud host so it can be used with subsequent SQL calls.
• Embedded R Execution Functions (Autonomous Database)
  The SQL API for Embedded R Execution with Autonomous Database functions are described in the following topics.
• Asynchronous Jobs
  When a function is run asynchronously, it's run as a job which can be tracked by using the rqJobStatus and rqJobResult functions.
• Special Control Arguments
  Use the PAR_LST parameter to specify special control arguments and additional arguments to be passed into the R script.
• Output Formats
  The OUT_FMT parameter controls the format of output returned by the table functions rqEval2, rqGroupEval2, rqIndexEval2, rqRowEval2, rqTableEval2, and rqJobResult.

10.6.1 Access and Authorization Procedures and Functions

Use the network access control lists (ACL) API to control access by users to external network services and resources from the database. Use the token store API to persist the authorization token issued by a cloud host so it can be used with subsequent SQL calls.

Use the following to manage ACL privileges. An ADMIN user is required.

• rqAppendHostACE Procedure
• rqGetHostACE Function
• rqRemoveHostACE Procedure

Use the following to manage authorization tokens:

• rqSetAuthToken Procedure
• rqIsTokenSet Function

Workflow

The typical workflow for using the SQL API for Embedded R Execution with Autonomous Database is:

1. Connect to PDB as the ADMIN user, and add a normal user OMLUSER to the ACL list of the cloud host of which the root domain is adb.us-region-1.oraclecloudapps.com:

   exec rqAppendHostAce('OMLUSER','adb.us-region-1.oraclecloudapps.com');

2. The OML Rest URLs can be obtained from the Oracle Autonomous Database that is provisioned.

   1. Sign into your Oracle Cloud Infrastructure account. You will need your OCI user name and password.
   2. Click the hamburger menu and select Autonomous Database instance that is provisioned. For more information on provisioning an Autonomous Database, see: Provision an Oracle Autonomous Database.
   3. Click Database Action.

   4. Scroll down the Database Action page, and click Oracle Machine Learning RESTful Services under the Related services tile. TheOracle Machine Learning RESTful Services dialog opens.

      ClickCopy to obtain the following URLs for:

      • Obtaining the REST authentication token for REST APIs provided by OML:

        <oml-cloud-service-location-url>/omlusers/

   The URL <oml-cloud-service-location-url> includes the tenancy ID, location, and database name. For example, https://qtraya2braestch-omldb.adb.us-sanjose-1.oraclecloudapps.com.

   In this example,

   • qtraya2braestch is the tenancy ID
   • omldb is the database name
   • us-sanjose-1 is the datacenter region
   • oraclecloudapps.com is the root domain

3. The Oracle Machine Learning REST API uses tokens to authenticate an Oracle Machine Learning user. To authenticate and obtain an access token, send a POST request to the Oracle Machine Learning User Management Cloud Service REST endpoint /oauth2/v1/token with your OML username and password.

   curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' 
   -d '{"grant_type":"password", "username":"'${username}'", "password":"'${password}'"}' 
   "<oml-cloud-service-location-url>/omlusers/api/oauth2/v1/token"

   The example uses the following values:

   • username is the OML username.
   • password is the OML user password.
   • oml-cloud-service-location-url is a variable containing the REST server portion of the Oracle Machine Learning User Management Cloud Service instance URL that includes the tenancy ID, database name, and the location name. You can obtain the omlserver URL from the Development tab in the Service Console of your Oracle Autonomous Database instance.

   Note:

   When a token expires, all calls to the OML Services REST endpoints with return a message stating that the token has expired along with the HTTP error: HTTP/1.1 401 Unauthorized

4. Connect to PDB as OMLUSER, set the access token, and run rqIndexEval2:

   exec rqSetAuthToken('<access token>');
   select *
       from table(rqIndexEval2(
           par_qry => NULL,
           out_fmt => '{"ID":"number", "RES":"varchar2(3)"}',
           times_num => 3,
           scr_name => 'idx_ret_df'));
   
           ID RES
   ---------- ---
            1 a
            2 b
            3 c
   
   3 rows selected.

• rqAppendHostACE Procedure
  The rqAppendHostACE procedure appends an access control entry (ACE) to the access control list (ACL) of the cloud host. The ACL controls access to the cloud host from the database, and the ACE specifies the connect privilege granted to the specified user name.
• rqGetHostACE Function
  The rqGetHostACE function gets the existing host access control entry (ACE) for the specified user. An exception is raised if the host ACE doesn't exist for the specified user.
• rqRemoveHostACE Procedure
  
• rqSetAuthToken Procedure
  The rqSetAuthToken procedure sets the access token in the token store.
• rqIsTokenSet Function
  The rqIsTokenSet function returns whether the authorization token is set or not.

10.6.1.1 rqAppendHostACE Procedure

The rqAppendHostACE procedure appends an access control entry (ACE) to the access control list (ACL) of the cloud host. The ACL controls access to the cloud host from the database, and the ACE specifies the connect privilege granted to the specified user name.

Syntax

PROCEDURE SYS.rqAppendHostACE(
  username IN VARCHAR2,
  host_root_domain IN VARCHAR2
)

Parameter

username - Database user to whom the connect privilege to the cloud host is granted.

host_root_domain - Root domain of the cloud host. For example, if the URL is https://qtraya2braestch-omldb.adb.us-sanjose-1.oraclecloudapps.com, the root domain of the cloud host is: adb.us-sanjose-1.oraclecloudapps.com.

10.6.1.2 rqGetHostACE Function

The rqGetHostACE function gets the existing host access control entry (ACE) for the specified user. An exception is raised if the host ACE doesn't exist for the specified user.

Syntax

FUNCTION sys.rqGetHostACE(
  p_username IN VARCHAR2
)

Parameter

p_username - Database user to look for the host ACE.

Example

If user OMLUSER has access to cloud host, i.e., ibuwlq4mjqkeils-omlrgpy1.adb.us-region-1.oraclecloudapps.com, the ADMIN user can run the following to check the user's privileges:

SQL> set serveroutput on
DECLARE 
    hostname VARCHAR2(4000);
BEGIN
    hostname := rqGetHostACE('OMLUSER'); 
    DBMS_OUTPUT.put_line ('hostname: ' || hostname);
END;
/
SQL> hostname: ibuwlq4mjqkeils-omlrgpy1.adb.us-region-1.oraclecloudapps.com 
PL/SQL procedure successfully completed.

10.6.1.3 rqRemoveHostACE Procedure

The rqRemoveHostACE procedure removes the existing host access control entry (ACE) from the specified username. If an access token was set for the cloud host, the token is also removed. An exception is raised if the host ACE does not exist.

Syntax

PROCEDURE SYS.rqRemoveHostACE(
   username IN  VARCHAR2
)

Parameter

username - Database user from whom the connect privilege to the cloud host is revoked.

10.6.1.4 rqSetAuthToken Procedure

The rqSetAuthToken procedure sets the access token in the token store.

Syntax

PROCEDURE SYS.rqSetAuthToken(
  access_token IN VARCHAR2
)

10.6.1.5 rqIsTokenSet Function

The rqIsTokenSet function returns whether the authorization token is set or not.

Syntax

FUNCTION SYS.rqIsTokenSet() RETURN BOOLEAN

Example

The following example shows how to use the rqSetAuthToken procedure and the rqIsTokenSet function.

DECLARE
    is_set BOOLEAN;
BEGIN
    rqSetAuthToken('<access token>');
    is_set := rqIsTokenSet();
    IF (is_set) THEN
        DBMS_OUTPUT.put_line ('token is set');
    END IF;
END;
/

10.6.2 Embedded R Execution Functions (Autonomous Database)

The SQL API for Embedded R Execution with Autonomous Database functions are described in the following topics.

• rqGrant Function
  This topic describes the rqGrant function when used in Oracle Autonomous Database.
• rqRevoke Procedure
  The rqRevoke procedure revokes read privilege access to an OML4R datastore or to a script in the OML4R script repository.
• rqListEnvs Function
  The function rqListEnvs when used in Oracle Autonomous Database, lists the environments saved in an Object Storage.
• rqEval2 Function
  The function rqEval2 when used in Oracle Autonomous Database, runs a user-defined R function that explicitly retrieves data or for which external data is to be automatically loaded for the function.
• rqTableEval2 Function
  The function rqTableEval2 runs the uer-defined R function in the script specified by the SCR_NAME parameter.
• rqRowEval2 Function
  The function rqRowEval2 when used in Oracle Autonomous Database,chunks data into sets of rows and then runs a user-defined R function on each chunk.
• rqGroupEval2 Function
  The function rqGroupEval2 when used in Oracle Autonomous Database, groups data by one or more columns and runs a user-defined R function on each group.
• rqIndexEval2 Function
  The function rqIndexEval2 when used in Oracle Autonomous Database, runs a user-defined R function multiple times in R engines spawned by the database environment.
• sys.rqScriptCreate Procedure
  The sys.rqScriptCreate procedure creates a script and adds it to the OML4R script repository.
• sys.rqScriptDrop Procedure
  The sys.rqScriptDrop procedure removes a script from the OML4R script repository.

10.6.2.1 rqGrant Function

This topic describes the rqGrant function when used in Oracle Autonomous Database.

The rqGrant function grants read privilege access to an OML4R datastore or to a script in the OML4R script repository.

Syntax

rqGrant (
    V_NAME          VARCHAR2    IN
    V_TYPE          VARCHAR2    IN
    V_USER          VARCHAR2    IN     DEFAULT)

Parameters

Parameter	Description
V_NAME	The name of an OML4R datastore or a script in the OML4R script repository.
V_TYPE	For a datastore, the type is datastore; for script the type is rqScript.
V_USER	The name of the user to whom to grant access.

Example 10-26 Granting Read Access to a script

-- Grant read privilege access to OMLUSER.
BEGIN
  rqGrant('RandomRedDots2', 'rqscript', 'OMLUSER');
END;
/

Example 10-27 Granting Read Access to a datastore

-- Grant read privilege access to datastore ds1 to OMLUSER.
BEGIN
  rqGrant('ds1', 'datastore', 'OMLUSER');
END;
/

Example 10-28 Granting Read Access to a Script to all Users

-- Grant read privilege access to script RandomRedDots to all users.
BEGIN
  rqGrant('rqFun1', 'rqscript', NULL);
END;
/

Example 10-29 Granting Read Access to a datastore to all Users

-- Grant read privilege access to datastore ds1 to all users.
BEGIN
  rqGrant('ds1', 'datastore', NULL);
END;
/

10.6.2.2 rqRevoke Procedure

The rqRevoke procedure revokes read privilege access to an OML4R datastore or to a script in the OML4R script repository.

Syntax

rqRevoke (
    V_NAME     VARCHAR2     IN
    V_TYPE     VARCHAR2     IN
    V_USER     VARCHAR2     IN     DEFAULT)
  

Parameters

Parameter	Description
V_NAME	The name of an OML4R datastore or a script in the OML4R script repository.
V_TYPE	For a datastore, the type is datastore; for a script, the type is rqscript.
V_USER	The name of the user from whom to revoke access.

Example 10-30 Revoking Read Access to a Script

-- Revoke read privilege access to OMLUSER.
BEGIN
  rqRevoke('myRandomRedDots2', 'rqscript', 'OMLUSER');
END;
/

10.6.2.3 rqListEnvs Function

The function rqListEnvs when used in Oracle Autonomous Database, lists the environments saved in an Object Storage.

Syntax

FUNCTION rqListEnvs
RETURN SYS.AnyDataSet

Example

Issue a query that calls the rqListEnvs function and lists the environments present.

select * from table(rqListEnvs());

The output is similar to the following:

NAME
----------
VALUE
--------------------------------------------------------------------------------
{"envs":[{"size":"831.5 MiB","name":"myrenv","description":"Install R forecast and ggplot2 packages","tags":{"application":"OML4R", "user":"OMLUSER"},"number_of_installed_packages":121}]}

10.6.2.4 rqEval2 Function

The function rqEval2 when used in Oracle Autonomous Database, runs a user-defined R function that explicitly retrieves data or for which external data is to be automatically loaded for the function.

The function rqEval2 runs the R function in the script specified by the SCR_NAME parameter.

Syntax

rqEval2 (
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME  VARCHAR2 DEFAULT NULL
    )

Parameters

Parameter	Description
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the SCR_NAME parameter. Special control arguments, which start with ore, are not passed to the function specified by SCR_NAME, but instead control what happens before or after the invocation of the function. For example, to omit rows with missing values from input table, use: '{"ore.na.omit":true}' See also: Special Control Arguments.
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
SCR_NAME	The name of a user-defined R function in the OML4R script repository.
SCR_OWNER	The owner of the R script. The default value is NULL. If NULL, will search for the R script in the user's script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined R function.

Return Value

Function rqEval2 returns a table that has the structure specified by the out_fmt parameter value.

Examples

Example 10-31 Using rqEval2

This example defines a R function and stores it in the OML4R script respository. The PL/SQL block, creates the script RandomRedDots2 and add it to the script repository. Specify that the script is private and overwrite the script with the same name. It calls the rqEval2 function on the user defined R function.

BEGIN
  sys.rqScriptCreate('RandomRedDots2',
    'function(divisor = 100, numDots = 100) {
       id <- 1:10
       plot(1:numDots, rnorm(numDots), pch = 21, bg = "red", cex = 2 )
       data.frame(id = id, val = id / divisor)}',
       v_global => FALSE,
       v_overwrite => TRUE);
END;
/

Example 10-32 JSON Output

The PAR_LST argument specifies using LOW service level with the special control argument oml_service_level. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string. The SCR_NAME parameter specifies the RandomRedDots2 function in the script repository as the R function to call. The JSON output is a CLOB. You can call set long [length] to get more output.

%script
set long 500
SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'JSON',
    scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE 
[{"val":0.01,"id":1},{"val":0.02,"id":2},{"val":0.03,"id":3},{"val":0.04,"id":4},{"val":0.05,"id":5},{"val":0.06,"id":6},{"val":0.07,"id":7},{"val":0.08,"id":8},{"val":0.09,"id":9},{"val":0.1,"id":10}]

Example 10-33 PNG Output.

The PAR_LST argument specifies using LOW service level with the special control argument oml_service_level. In the OUT_FMT argument, the string 'PNG' to include images returned by reqEval2. The SCR_NAME parameter specifies the RandomRedDots2 function in the script repository as the R function to call. The JSON output is a CLOB.

%script
SELECT * FROM table(rqEval2(
    par_lst => '
    {"ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'PNG',
    scr_name => 'RandomRedDots2'));

The result is:

---------------------------
NAME   ID   VALUE   IMAGE   
       1            89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE950000200049444154789C

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length].

Example 10-34 XML Output.

The PAR_LST argument specifies using LOW service level with the special control argument oml_service_level. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string. The SCR_NAME parameter specifies the RandomRedDots2 function in the script repository as the R function to call. The JSON output is a CLOB.

%script
set long 1000
SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE
      <root><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09<

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length]. The set long 1000 gives the complete output.

Example 10-35 XML Output

Run the Select statement to get an XML output. ore_graphics_flag is set to true so that both structured data and images are included in the XML

%script


set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The result is:

---------------------------
NAME   VALUE                
<root><R-data><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09</val></ROW-frame_obj><ROW-frame_obj><id>10</id><val>0.1</val></ROW-frame_obj></frame_obj></R-data><images><image><img src="data:image/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3hT5fvH8Xe6m6QDSgtl07KRjciQIZvKkiECCspQBNkiICBLUBRxACoqU0RAkT0EREGGTCm77LJll2Z0pc/vD9Qf8u04QJPTcb+uq9clzZM8n9Qkd845zzAopRRCCCGEcCk3vQMIIYQQOZEUYCGEEEIHUoCFEEIIHUgBFkIIIXQgBVgIIYTQgRRgIYQQQgdSgIUQQggdSAEWQgghdCAFWAghhNCBFGAhhBBCB1KAhRBCCB1IARZCCCF0IAVYCCGE0IEUYCGEEEIHUoCFEEIIHU 

Example 10-36 Relational Output

Run the Select statement to get a Relational output. The OUT_FMT argument specifies a JSON string that contains the column names and data types of the table returned by rqEval2.

%script
SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => '{"val":"NUMBER","id":"NUMBER"}',
    scr_name => 'RandomRedDots2'));

The result is:

val id 
0.01 1 
0.02 2 
0.03 3 
0.04 4 
0.05 5 
0.06 6 
0.07 7 
0.08 8 
0.09 9 
0.1 10 

10 rows selected.

Example 10-37 Passing arguments using rqEval2:

Run the Select statement to get an XML output by passing arguments to the rqEval2 function.

%script
set long 500
SELECT * FROM table(rqEval2(
        par_lst => '{"ore_service_level":"LOW", "divisor":50, "numDots":500}',
        out_fmt => 'XML',
        scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE
       <root><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09<

10.6.2.5 rqTableEval2 Function

The function rqTableEval2 runs the uer-defined R function in the script specified by the SCR_NAME parameter.

Pass data to the user-defined R function with the table name specified in the INP_NAM parameter. Pass arguments to the user-defined R function with the PAR_LST parameter.

You define the form of the returned value with the OUT_FMT parameter.

Syntax

rqTableEval2(
   INP_NAM VARCHAR2,
   PAR_LST VARCHAR2,
   OUT_FMT VARCHAR2,
   SCR_NAME VARCHAR2,
   SCR_OWNER VARCHAR2 DEFAULT NULL,
   ENV_NAME  VARCHAR2 DEFAULT NULL
   )

Parameters

Table 10-19 Parameters of the rqTableEval Function

Parameter	Description
INP_NAM	The name of a table or view that specifies the data to pass to the R function specified by the SCR_NAME parameter. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the SCR_NAME parameter. Special control arguments, which start with ore, are not passed to the function specified by SCR_NAME, but instead control what happens before or after the invocation of the function. For example, to specify converting the one column input data.frame to a vector, use: '{"ore.drop":true}' See also: Special Control Arguments.
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
SCR_NAME	The name of a user-defined R function in the OML4R script repository.
SCR_OWNER	The owner of the R script. The default value is NULL. If NULL, will search for the R script in the user’s script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined R function.

Return Value

Function rqTableEval2 returns a table that has the structure specified by the OUT_FMT parameter value.

Examples

This example creates a function and stores it as the script buildLM in the repository.

Example 10-38 Using the rqTableEval2 Function

In a PL/SQL block, creates the R function buildLM and stores in the script repository with the name buildLM, overwriting any existing user-defined R function stored in the script repository with the same name.

BEGIN
    sys.rqScriptCreate('buildLM',
        'function(dat, dsname) {
            mod <- lm(Petal.Length~Petal.Width, dat)
            ore.save(mod, name=dsname, overwrite=TRUE)
            plot(predict(mod), dat$Petal.Length, pch=21, bg=c("red","blue"),  xlab = "Predicted Values", ylab = "Observed Values")
            abline(a = 0, b = 1, lwd=2, col = "green")
            return(data.frame(Coef=mod$coef))}',
    v_global => FALSE,  
    v_overwrite => TRUE);
END;
/

Example 10-39 JSON Output

The INP_NAM argument passes the 'IRIS' table to the user defined function. The PAR_LST argument specifies using LOW service level with the special control argument ore_service_level. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string. The scr_name parameter specifies the buildLM function in the script repository as the R function to call. The JSON output is a CLOB. You can call set long [length] to get more output.

%script
set long 500
SELECT * FROM table(rqTableEval2(
    inp_nam => 'IRIS',
    par_lst => '{"dsname":"ds-1", "ore_service_level":"LOW"}',
    out_fmt => 'JSON',
    scr_name => 'buildLM'));

The result is:

---------------------------
NAME   VALUE
       [{"_row":"(Intercept)","Coef":1.0836},{"_row":"Petal.Width","Coef":2.2299}]
---------------------------

Example 10-40 PNG Output

The par_lst argument specifies using LOW service level with the special control argument ore_service_level. In the out_fmt argument, the string 'PNG' specifies to include images in the BLOB column., The scr_name parameter specifies the buildLM function in the script repository as the R function to call.

%script

set long 500

SELECT * FROM table(rqTableEval2( 
    inp_nam => 'IRIS', 
    par_lst => '{"dsname":"ds-1", "ore_graphics_flag":true, "ore_service_level":"LOW"}', 
    out_fmt => 'PNG', 
    scr_name => 'buildLM'));

The result is:

---------------------------
NAME   ID   VALUE   IMAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          1         89504E470D0A1A0A0000000D49484452000001E0000001E0080

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length]

.

Example 10-41 Relational Output

The inp_nam argument passes the 'IRIS' table to the user defined function. The par_lst argument specifies using LOW service level with the special control argument ore_service_level. In the out_fmt argument, specifies the column names and data types of the table returned by the function. The scr_name parameter specifies the buildLM function in the script repository as the R function to call.

%script

SELECT * FROM table(rqTableEval2( 
    inp_nam => 'IRIS', 
    par_lst => '{"dsname":"ds-1", "ore_service_level":"LOW"}', 
    out_fmt => '{"Coef":"number"}', 
    scr_name => 'buildLM'));

The result is:

Coef     
  1.0836 
  2.2299

10.6.2.6 rqRowEval2 Function

The function rqRowEval2 when used in Oracle Autonomous Database,chunks data into sets of rows and then runs a user-defined R function on each chunk.

The function rqRowEval2 passes the data specified by the INP_NAM parameter to the user-defined R function. You can pass arguments to the R function with the PAR_LST parameter. The ROW_NUM parameter specifies the number of rows that should be passed to each invocation of the R function. The last chunk may have fewer rows than the number specified.

The rqRowEval2 function supports data-parallel execution, in which one or more R engines perform the same R function, or task, on disjoint chunks of data. Oracle Database handles the management and control of the potentially multiple R engines that run on the database server machine, automatically chunking and passing data to the R engines executing in parallel. Oracle Database ensures that R function executions for all chunks of rows complete, or the rqRowEval2 function returns an error.

You define the form of the returned value with the OUT_FMT parameter.

Syntax

rqRowEval2(
    INP_NAM VARCHAR2,
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    ROW_NUM NUMBER,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME  VARCHAR2 DEFAULT NULL
    )

Parameters

Table 10-20 Parameters of the rqRowEval2 Function

Parameter	Description
INP_NAM	The name of a table or view that specifies the data to pass to the R function specified by the SCR_NAME parameter. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the SCR_NAME parameter. Special control arguments, which start with ore, are not passed to the function specified by SCR_NAME, but instead control what happens before or after the invocation of the function. For example, to capture images rendered in the R function, use: '{"ore_graphics_flag":true}' See also: Special Control Arguments.
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
ROW_NUM	The number of rows in a chunk. The R script is executed in each chunk.
SCR_NAME	The name of a user-defined R function in the OML4R script repository.
SCR_OWNER	The owner of the registered R script. The default value is NULL. If NULL, will search for the R script in the user’s script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined R function.

Return Value

Function rqRowEval2 returns a table that has the structure specified by the OUT_FMT parameter value.

Examples

Example 10-42 Using an rqRowEval2 Function

This example creates a user-defined function and saves the function in the OML4R script repository.

The PL/SQL block, creates the script scoreLM and add it to the script repository.

%script

BEGIN
    sys.rqScriptCreate('scoreLM',
        'function(dat, dsname){
            ore.load(dsname)
            dat$Petal.Length_pred <- predict(mod, newdata=dat)
            dat[,c("Petal.Length_pred","Petal.Length","Species")]}',
        v_global => FALSE,
        v_overwrite => TRUE);
END;
/

The results is:

PL/SQL procedure successfully completed.


---------------------------

Example 10-43 JSON Output

The PAR_LST argument specifies using MEDIUM service level with the special control argument ore_service_level and. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string. The SCR_NAME parameter specifies the scoreLM function in the script repository as the R function to call. The JSON output is a CLOB. You can call set long [length] to get more output.

%script

set long 1000

SELECT * FROM table(rqRowEval2(
 inp_nam => 'IRIS',
 par_lst => '{"dsname":"ds-1", "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
 out_fmt => 'JSON',
 row_num => 5,
 scr_name => 'scoreLM'));

The result is:

---------------------------
NAME   VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
       [{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1},{"Petal.Length_pred":1.3066,"Species":"setosa","Petal.Length":1.1},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.2},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.2},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.7525,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.7525,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.9755,"Species":"setosa","Petal.Length":1.3},{"Petal.Length_pred":1.3066,"Species":"setosa","Petal.Length":1.4},{"Petal.Length_pred":1.3066,"Species":"setosa","Petal.Length":1.4},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1.4},{"Petal.Length_pred":1.5295,"Species":"setosa","Petal.Length":1 

Example 10-44 Relational

Run the Select statement to get an Relational output.

%script

SELECT * FROM table(rqRowEval2(
 inp_nam => 'IRIS',
 par_lst => '{"dsname":"ds-1", "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
 out_fmt => '{"Petal.Length_pred":"NUMBER", "Petal.Length":"NUMBER", "Species":"VARCHAR2(10)"}',
 row_num => 5,
 scr_name => 'scoreLM'));

The result is:

Petal.Length_pred   Petal.Length   Species   
             1.5295              1 setosa    
             1.3066            1.1 setosa    
             1.5295            1.2 setosa    
             1.5295            1.2 setosa    
             1.5295            1.3 setosa    
             1.5295            1.3 setosa    
             1.5295            1.3 setosa    
             1.5295            1.3 setosa    
             1.7525            1.3 setosa    
             1.7525            1.3 setosa    
             1.9755            1.3 setosa    
             1.3066            1.4 setosa    
             1.3066            1.4 setosa    
             1.5295            1.4 setosa    

Petal.Length_pred   Petal.Length   Species   
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.5295            1.4 setosa    
             1.7525            1.4 setosa    
             1.7525            1.4 setosa    
             1.7525            1.4 setosa    
             1.3066            1.5 setosa    
             1.3066            1.5 setosa    
             1.5295            1.5 setosa    
             1.5295            1.5 setosa    

Petal.Length_pred   Petal.Length   Species   
             1.5295            1.5 setosa    
             1.5295            1.5 setosa    
             1.5295            1.5 setosa    
             1.5295            1.5 setosa    
             1.5295            1.5 setosa    
             1.7525            1.5 setosa    
             1.9755            1.5 setosa    
             1.9755            1.5 setosa    
             1.9755            1.5 setosa    
             1.5295            1.6 setosa    
             1.5295            1.6 setosa    
             1.5295            1.6 setosa    
             1.5295            1.6 setosa    
             1.5295            1.6 setosa    

Petal.Length_pred   Petal.Length   Species      
             1.9755            1.6 setosa       
             2.4215            1.6 setosa       
             1.5295            1.7 setosa       
             1.7525            1.7 setosa       
             1.9755            1.7 setosa       
             2.1985            1.7 setosa       
             1.5295            1.9 setosa       
             1.9755            1.9 setosa       
             3.5365              3 versicolor   
             3.3135            3.3 versicolor   
             3.3135            3.3 versicolor   
             3.3135            3.5 versicolor   
             3.3135            3.5 versicolor   
             3.9825            3.6 versicolor   

Petal.Length_pred   Petal.Length   Species      
             3.3135            3.7 versicolor   
             3.5365            3.8 versicolor   
             3.5365            3.9 versicolor   
             3.7595            3.9 versicolor   
             4.2055            3.9 versicolor   
             3.3135              4 versicolor   
             3.7595              4 versicolor   
             3.9825              4 versicolor   
             3.9825              4 versicolor   
             3.9825              4 versicolor   
             3.3135            4.1 versicolor   
             3.9825            4.1 versicolor   
             3.9825            4.1 versicolor   
             3.7595            4.2 versicolor   

Petal.Length_pred   Petal.Length   Species      
             3.9825            4.2 versicolor   
             3.9825            4.2 versicolor   
             4.4285            4.2 versicolor   
             3.9825            4.3 versicolor   
             3.9825            4.3 versicolor   
             3.7595            4.4 versicolor   
             3.9825            4.4 versicolor   
             4.2055            4.4 versicolor   
             4.2055            4.4 versicolor   
             4.8745            4.5 virginica    
             3.9825            4.5 versicolor   
             4.4285            4.5 versicolor   
             4.4285            4.5 versicolor   
             4.4285            4.5 versicolor   

Petal.Length_pred   Petal.Length   Species      
             4.4285            4.5 versicolor   
             4.4285            4.5 versicolor   
             4.6515            4.5 versicolor   
             3.9825            4.6 versicolor   
             4.2055            4.6 versicolor   
             4.4285            4.6 versicolor   
             3.7595            4.7 versicolor   
             4.2055            4.7 versicolor   
             4.2055            4.7 versicolor   
             4.4285            4.7 versicolor   
             4.6515            4.7 versicolor   
             5.0975            4.8 virginica    
             5.0975            4.8 virginica    
             4.2055            4.8 versicolor   

Petal.Length_pred   Petal.Length   Species      
             5.0975            4.8 versicolor   
             5.0975            4.9 virginica    
             5.0975            4.9 virginica    
             5.5434            4.9 virginica    
             4.4285            4.9 versicolor   
             4.4285            4.9 versicolor   
             4.4285              5 virginica    
             5.3204              5 virginica    
             5.5434              5 virginica    
             4.8745              5 versicolor   
             4.4285            5.1 virginica    
             5.0975            5.1 virginica    
             5.3204            5.1 virginica    
             5.3204            5.1 virginica    

Petal.Length_pred   Petal.Length   Species      
             5.5434            5.1 virginica    
             6.2124            5.1 virginica    
             6.4354            5.1 virginica    
             4.6515            5.1 versicolor   
             5.5434            5.2 virginica    
             6.2124            5.2 virginica    
             5.3204            5.3 virginica    
             6.2124            5.3 virginica    
             5.7664            5.4 virginica    
             6.2124            5.4 virginica    
             5.0975            5.5 virginica    
             5.0975            5.5 virginica    
             5.7664            5.5 virginica    
             4.2055            5.6 virginica    

Petal.Length_pred   Petal.Length   Species     
             5.0975            5.6 virginica   
             5.7664            5.6 virginica   
             5.9894            5.6 virginica   
             6.4354            5.6 virginica   
             6.4354            5.6 virginica   
             5.7664            5.7 virginica   
             6.2124            5.7 virginica   
             6.6584            5.7 virginica   
             4.6515            5.8 virginica   
             5.0975            5.8 virginica   
             5.9894            5.8 virginica   
             5.7664            5.9 virginica   
             6.2124            5.9 virginica   
             5.0975              6 virginica   

Petal.Length_pred   Petal.Length   Species     
             6.6584              6 virginica   
             5.3204            6.1 virginica   
             6.2124            6.1 virginica   
             6.6584            6.1 virginica   
             5.0975            6.3 virginica   
             5.5434            6.4 virginica   
             5.7664            6.6 virginica   
             5.5434            6.7 virginica   
             5.9894            6.7 virginica   
             6.2124            6.9 virginica   


150 rows selected. 


---------------------------

10.6.2.7 rqGroupEval2 Function

The function rqGroupEval2 when used in Oracle Autonomous Database, groups data by one or more columns and runs a user-defined R function on each group.

The function rqGroupEval2 runs the user-defined R function specified by the scr_name parameter. Pass data to the user-defined R function with the inp_nam parameter, pass arguments to the user-defined R function with the par_lst parameter. Specify one or more grouping columns with the grp_col parameter. Define the form of the returned value with the out_fmt parameter.

Syntax

rqGroupEval2 (
    INP_NAM VARCHAR2,
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    GRP_COL VARCHAR2,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME  VARCHAR2 DEFAULT NULL
    )

Parameters

Parameter	Description
INP_NAM	The name of a table or view that specifies the data to pass to the R function specified by the SCR_NAME parameter. If using a table or view owned by another user, use the format <owner name>.<table/view name> You must have read access to the specified table or view.
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the scr_name parameter. Special control arguments, which start with ore, are not passed to the function specified by scr_name, but instead control what happens before or after the invocation of the function. For example, to run the R function with data parallelism, use: '{"ore_parallel_flag":true}' See also: Special Control Arguments
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
GRP_COL	The names of the grouping columns by which to partition the data. Use commas to separate multiple columns. For example, to group by GENDER and YEAR: "GENDER,YEAR"
SCR_NAME	The name of a user-defined R function in the OML4R script repository.
SCR_OWNER	The owner of the registered R script. The default value is NULL. If NULL, will search for the R script in the user’s script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined R function.

Return Value

The user-defined rqGroupEval2 function returns a table that has the structure specified by the OUT_FMT parameter value.

Examples

Example 10-45 Using an rqGroupEval2 Function

This example uses the IRIS table created in the example shown in rqTableEval2 Function (Autonomous Database). Define the R function and store it with the name groupCount in the script repository.

%script
BEGIN
    sys.rqScriptCreate('groupCount',
        'function(dat){
            x <- data.frame(table(dat$Species))
            names(x) <- c("Species", "Count")
            x}',
        FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/

The output is similar to the following:

PL/SQL procedure successfully completed. 

---------------------------

Example 10-46 JSON Output

Calls the rqGroupEval2 function, which runs the user defined function groupCount. In the function, the INP_NAM argument specifies the data in the IRIS table to pass to the function. The PAR_LST argument specifies the special control argument ore_input_type. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string The GRP_COL parameter specifies the column to group by. The SCR_NAME parameter specifies the user-defined R function stored with the name groupCount in the script repository.

%script
set long 500
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{"ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => 'JSON',
            grp_col => 'Species',
            scr_name => 'groupCount'));

The output is similar to the following:

NAME VALUE 
[{"Count":50,"Species":"setosa"},{"Count":50,"Species":"versicolor"},{"Count":50,"Species":"virginica"}]

Example 10-47 XML Output

Calls the rqGroupEval2 function, which runs the user defined function groupCount. In the function, the INP_NAM argument specifies the data in the IRIS table to pass to the function. The PAR_LST argument specifies using MEDIUM service level with special control argument ore_service_level and set the special control argument ore_parallel_flag to true. The OUT_FMT parameter specifies returning the value in XML format. The GRP_COL parameter specifies the column to group by. The SCR_NAME parameter specifies the user-defined R function stored with the name groupCount in the script repository.

%script
set long 500
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => 'XML',
            grp_col => 'Species',
            scr_name => 'groupCount'));

The ouput is similar to the following:

NAME VALUE
       <root><frame_obj><ROW-frame_obj><Species>setosa</Species><Count>50</Count></ROW-frame_obj><ROW-frame_obj><Species>versicolor</Species><Count>50</Count></ROW-frame_obj><ROW-frame_obj><Species>virginica</Species><Count>50</Count></ROW-frame_obj></frame_obj></root>

Example 10-48 Relational Output

Run the Select statement to get a Relational output.

%script
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{"ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => '{"Species":"VARCHAR2(10)", "Count":"NUMBER"}',
            grp_col => 'Species',
            scr_name => 'groupCount'));

Species Count 
setosa 50 
versicolor 50 
virginica 50

10.6.2.8 rqIndexEval2 Function

The function rqIndexEval2 when used in Oracle Autonomous Database, runs a user-defined R function multiple times in R engines spawned by the database environment.

You can pass arguments to the user-defined R function with the PAR_LST parameter. Additional arguments can be passed to the parameter PAR_LST such as ore_parallel_flag, ore_service_level, etc. The boolean argument ore_parallel_flag, which has a default value of false, runs the user-defined R function with data parallelism. Different levels of performance and concurrency in Autonomous Database can be controlled by the argument ore_service_level, which has a default service level of LOW. See also: Special Control Arguments.

Syntax

rqIndexEval2(
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    TIMES_NUM NUMBER,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME VARCHAR2 DEFAULT NULL
)

Parameters

Parameter	Description
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined R function specified by the SCR_NAME parameter. Special control arguments, which start with ore, are not passed to the function specified by SCR_NAME, but instead control what happens before or after the invocation of the function. See also: Special Control Arguments.
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
TIMES_NUM	The number of times to run the R script.
SCR_NAME	The name of a user-defined R function in the OML4R script repository.
SCR_OWNER	The owner of the registered R script. The default value is NULL. If NULL, will search for the R script in the user’s script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined R function.

Example

The PL/SQL block, creates the script computeMean and add it to the script repository. Specify that the script is private and overwrite the script with the same name.

BEGIN
    sys.rqScriptCreate('computeMean',
        'function(idx, rseed){
            set.seed(rseed)
            x <- round(runif(100,2,10),4)
            return(mean(x))}',
        v_global => FALSE,
        v_overwrite => TRUE);
END;
/

The result is:

PL/SQL procedure successfully completed.


---------------------------

Example 10-49 JSON Output

Run the Select statement to get a JSON output.

%script

SELECT * FROM table(rqIndexEval2(
        par_lst => '{"rseed":99, "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
        out_fmt => 'JSON',
        times_num => 5,
        scr_name => 'computeMean'));

The result is:

NAME   VALUE                                                      
       {"1":5.8977,"2":5.8977,"3":5.8977,"4":5.8977,"5":5.8977}   



---------------------------

Example 10-50 XML Output

Run the Select statement to get an XML output.

%script

SELECT * FROM table(rqIndexEval2(
        par_lst => '{"rseed":99, "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
        out_fmt => 'XML',
        times_num => 5,
        scr_name => 'computeMean'));

The result is:

NAME   VALUE                                                                              
1      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
2      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
3      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
4      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
5      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   

10.6.2.9 sys.rqScriptCreate Procedure

The sys.rqScriptCreate procedure creates a script and adds it to the OML4R script repository.

Syntax

sys.rqScriptCreate (
    V_NAME          VARCHAR2    IN
    V_SCRIPT        CLOB        IN
    V_GLOBAL        BOOLEAN     IN     DEFAULT
    V_OVERWRITE     BOOLEAN     IN     DEFAULT)

Parameter	Description
V_NAME	A name for the script in the OML4R script repository.
V_SCRIPT	The R function definition to store in the script.
V_GLOBAL	TRUE specifies that the script is public; FALSE specifies that the script is private.
V_OVERWRITE	If the OML4R script repository already has a script with the same name as V_NAME, then TRUE replaces the content of that script with V_SCRIPT and FALSE does not replace it.

10.6.2.10 sys.rqScriptDrop Procedure

The sys.rqScriptDrop procedure removes a script from the OML4R script repository.

Syntax

sys.rqScriptDrop (
    V_NAME          VARCHAR2    IN
    V_GLOBAL        BOOLEAN     IN     DEFAULT
    V_SILENT        BOOLEAN     IN     DEFAULT)

Parameter	Description
V_NAME	A name for the script in the OML4R script repository.
V_GLOBAL	TRUE (the default) specifies that the script is public; FALSE specifies that the script is private.
V_SILENT	FALSE (the default) specifies that sys.rqqScriptDrop displays an error message if it encounters an error in dropping the specified R script. TRUE specifies that the procedure does not display an error message.

10.6.3 Asynchronous Jobs

When a function is run asynchronously, it's run as a job which can be tracked by using the rqJobStatus and rqJobResult functions.

Topics:

• ore_async_flag Argument

• rqJobStatus Function

• rqJobResult Function

• Asynchronous Job Example

• ore_async_flag Argument
  The special control argument ore_async_flag determines if a job is run synchronously or asynchronously. The default value is false.
• rqJobStatus Function
  Use the rqJobStatus function to look up the status of an asynchronous job. If the job is pending, it returns job is still running . If the job is completed, the function returns a URL.
• rqJobResult Function
  Use the rqJobResult function to return the job result.
• Asynchronous Job Example
  The following examples shows how to submit asynchronous jobs with non-XML output and with XML output.

10.6.3.1 ore_async_flag Argument

The special control argument ore_async_flag determines if a job is run synchronously or asynchronously. The default value is false.

Set the ore_async_flag Argument

• To run a function in synchronous mode, set ore_async_flag to false.

  In synchronous mode, the SQL API waits for the HTTP call to finish and returns when the HTTP response is ready.

  By default, rq*Eval2 functions are executed synchronously. The default connection timeout limit is 60 seconds. Synchronous mode is used if ore_async_flag is not set or if it's set to false.

• To run a function in asynchronous mode, set ore_async_flag to true.

  In asynchronous mode, the SQL API returns a URL directly after the asynchronous job is submitted to the web server. The URL contains a job ID, which can be used to fetch the job status and result in subsequent SQL calls.

Submit Asynchronous Job Example

In the following code, the R function RandomRedDots2 is run in an asynchronous mode with argument ore_async_flag set to true.

%script

set long 500

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => NULL,
    scr_name => 'RandomRedDots2'));

The VALUE column of the result contains a URL containing the job ID of the asynchronous job:

NAME
--------------------------------------------------------------------------------
VALUE
-------------------------------------------------------------------------------- 
https://<host_name>/oml/api/r-scripts/v1/jobs/<job_id>
1 row selected.

10.6.3.2 rqJobStatus Function

Use the rqJobStatus function to look up the status of an asynchronous job. If the job is pending, it returns job is still running . If the job is completed, the function returns a URL.

Syntax

FUNCTION RQSYS.rqJobStatus(
  job_id       VARCHAR2 
)
RETURN RQSYS.rqClobSet

Parameters

Parameter	Description
job_id	The ID of the asynchronous job.

Example

The following example shows a rqJobStatus call and its output.

SQL> select * from rqJobStatus(
       job_id => '<job id>'
);

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host_name>/oml/api/r-scripts/v1/jobs/<job_id>/result

1 row selected.

10.6.3.3 rqJobResult Function

Use the rqJobResult function to return the job result.

Syntax

FUNCTION RQSYS.rqJobResult(
  job_id       VARCHAR2, 
  out_fmt      VARCHAR2 DEFAULT 'JSON'
)
RETURN SYS.AnyDataSet

Parameters

Parameter	Description
job_id	The ID of the asynchronous job.
out_fmt	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation.

Example

The following example shows a rqJobResult call and its output.

SQL> select * from rqJobResult(
    job_id => '<job id>',
    out_fmt => '{"val":"NUMBER","id":"NUMBER"}'
);

val id
  0.01 1
  0.02 2
  0.03 3
  0.04 4
  0.05 5
  0.06 6
  0.07 7
  0.08 8
  0.09 9
   0.1 10


10 rows selected.

10.6.3.4 Asynchronous Job Example

The following examples shows how to submit asynchronous jobs with non-XML output and with XML output.

Non-XML Output

When submitting asynchronous jobs, for JSON, PNG and relational outputs, set the OUT_FMT argument to NULL when submitting the job. When fetching the job result, specify OUT_FMT in the rqJobResult call.

Issue a rqEval2 function call to submit an asynchronous job. In the function. The PAR_LST argument specifies submitting the job asynchronously with the special control argument ore_async_flag, capturing the images rendered in the script with the special control argument ore_graphics_flag.

The OUT_FMT argument is NULL. The SCR_NAME parameter specifies the user-defined R function stored with the name RandomRedDots2 in the script repository.

The asynchronous call returns a job status URL in CLOB, you can call set long [length] to get the full URL.

%script

set long 500

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => NULL,
    scr_name => 'RandomRedDots2'));

The output is the following:

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------  
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>
1 row selected. 

Run a SELECT statement that calls the rqJobStatus function, which returns a resource URL containing the job ID when the job result is ready.

select * from rqJobStatus(
job_id => '<job id>');  

The output is the following when the job is still pending.

NAME
---------------------------------------------------------------------- 
VALUE 
----------------------------------------------------------------------  
job is still running
1 row selected. 

The output is the following when the job finishes.

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------  
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result
1 row selected. 

Run a SELECT statement that calls the rqJobResult function.

In the OUT_FMT argument, the string 'PNG' specifies to include both return value and images (titles and image bytes) in the result.

select * from rqJobResult(                
        job_id => '<job id>',
        out_fmt => 'PNG' 
);

The output is the following.

---------------------------
NAME   ID   VALUE   IMAGE   
          1
      89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE950000200049444154789CECDD775853E

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length].

XML Ouput

If XML output is expected from the asynchronous job, set the OUT_FMT argument to 'XML' when submitting the job and fetching the job result.

This example uses the script RandomRedDots2 created in the example shown in the rqIndexEval2 Function topic.

Issue a rqEval2 function call to submit an asynchronous job. In the function, the PAR_LST argument specifies submitting the job asynchronously with the special control argument ore_async_flag and specifies using LOW service level with the special control argument ore_service_level.

The asynchronous call returns a job status URL in CLOB, you can call set long [length] to get the full URL.

%script

set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The output is the following.

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------  
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>
 
1 row selected.

Run a SELECT statement that calls the rqJobStatus function, which returns a resource URL containing the job id when the job result is ready.

select * from rqJobStatus(
job_id => '<job id>' 
);
      

The output is the following when the job is still pending.

NAME
---------------------------------------------------------------------- 
VALUE
----------------------------------------------------------------------  
job is still running   
1 row selected. 

The output is the following when the job result is ready.

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------  
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result
1 row selected. 

Run a SELECT statement that calls the rqJobResult function.

In the OUT_FMT argument, the string 'XML' specifies that the table returned contains a CLOB that is an XML string.

%script

set long 1000

select * from rqJobResult(
         job_id => '<job id>',
         out_fmt => 'XML' 
);

The output is the following.

---------------------------
NAME   VALUE                
<root><R-data><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09</val></ROW-frame_obj><ROW-frame_obj><id>10</id><val>0.1</val></ROW-frame_obj></frame_obj></R-data><images><image><img src="data:image/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3xTZfvH8U/StEmTtGUV2rKnCCjrARGZZcsegqgoKoKCqIAyHIgKylABUUFAZIuAAjIUZMkqZVVk7yl7tSTpSNP79wfqT7HjFJqctlzv16uvx6e5c873pKVXcs59rtuglFIIIYQQwqeMegcQQggh7kVSgIUQQggdSAEWQgghdCAFWAghhNCBFGAhhBBCB1KAhRBCCB1IARZCCCF0IAVYCCGE0IEUYCGEEEIHUoCFEEIIHUgBFkIIIXQgBVgIIYTQgRRgIYQQQgdSgIUQQggdSA

10.6.4 Special Control Arguments

Use the PAR_LST parameter to specify special control arguments and additional arguments to be passed into the R script.

Argument	Syntax and Description
ore.drop	Syntax ore.drop: bool, true (default) Description Controls the object type for the input data. If true, a onecolumn data.frame will be converted to a vector. If false, a one column data.frame will not be converted.
ore.na.omit	Syntax ore.na.omit : bool, false (default) Description Controls the handling of missing values in the input data. If true, rows or vector elements, depending on the ore.drop setting,containing missing values will be removed from the input data. If false, do not omit rows with missing values from the table. If all therows in an rqRowEval chunk contain missing values, the input data for that chunk will be an empty data.frame or vector.
ore.png.*	Syntax ore.png.*: numeric or character Description If ore_graphics_flag is true, additional parameters for the png graphics device driver. The naming convention for these arguments is to add an ore.png. prefix to the arguments of the png function. For example, if ore.png.height is supplied, argument height will be passed to the png function. If not set, standard default values for the png function are used.
ore.characterAsFactor	Syntax ore.characterAsFactor: bool, false (default) Description Controls the type that character and factor columns of the input table are treated as.If true, all character and factor columns are treated as factor type.If false, all character and factor columns are treated as character type. For functions rqGroupEval2 and rqRowEval2, each partition will be only aware of factor levels in itself and will not be aware of levels in other partitions.
ore_async_flag	Syntax ore_async_flag: bool, false (default) Description If true, the job will be submitted asynchronously. If false, the job will be executed in synchronous mode.
ore_graphics_flag	Syntax ore_graphics_flag:bool, false (default) Description If true, the server will capture images rendered in the R script. If false, the server will not capture images rendered in the R script.
ore_parallel_flag	Syntax ore_parallel_flag:bool, false (default) Description If true, the R script will be run with data parallelism. Data parallelism is only applicable to rqRowEval2, rqGroupEval2, and rqIndexEval2. If false, the R script will not be run with data parallelism.
ore_service_level	Syntax ore_service_level : string, allowed values: 'LOW'(default), 'MEDIUM', 'HIGH' Description Controls the different levels of performance and concurrency in Autonomous Database.

10.6.5 Output Formats

The OUT_FMT parameter controls the format of output returned by the table functions rqEval2, rqGroupEval2, rqIndexEval2, rqRowEval2, rqTableEval2, and rqJobResult.

The output formats are:

• JSON
• Relational
• XML
• PNG
• Asynchronous Mode Output

JSON

When OUT_FMT is set to JSON, the table functions return a table containing a CLOB that is a JSON string.

The following example calls the rqEval2 function on the 'RandomRedDots2' created in the rqEval2 function section.

%script

set long 500

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'JSON',
    scr_name => 'RandomRedDots2'));

---------------------------
NAME   VALUE                                                                                                                                                                                                       
       [{"val":0.01,"id":1},{"val":0.02,"id":2},{"val":0.03,"id":3},{"val":0.04,"id":4},{"val":0.05,"id":5},{"val":0.06,"id":6},{"val":0.07,"id":7},{"val":0.08,"id":8},{"val":0.09,"id":9},{"val":0.1,"id":10}]   



---------------------------

Relational

When OUT_FMT is specified with a JSON string where column names are mapped to column types, the table functions return the response by reshaping it into table columns. For example, if OUT_FMT is specified with {"NAME":"varchar2(10)", "COUNT":"number"}, the output should contain a NAME column of type VARCHAR2(10) and a COUNT column of type NUMBER. The following example uses the table rqGroupEval2 and the script groupCount (created in rqGroupEval2 Function and calls the groupCount function:

%script

SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{"ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => '{"Species":"VARCHAR2(10)", "Count":"NUMBER"}',
            grp_col => 'Species',
            scr_name => 'groupCount'));

Species Count
setosa 50
versicolor 50
virginica 50

XML

When OUT_FMT is specified with XML, the table functions return the response in a table with fixed columns. The output consists of two columns. The NAME column contains the name of the row. The NAME column value is NULL for rqEval2, rqTableEval2,rqRowEval2 function returns. For rqGroupEval2, rqIndexEval2, the NAME column value is the group/index name. The VALUE column contains the XML string.

The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. To include images in the XML string, the special control argument ore_graphics_flag must be set to true.

In the following code, the R function RandomRedDots2 is created in the script repository.

%script

set long 500

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The following example shows the XML output of a rqEval2 function call where both structured data and images are included in the result:

set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

--------------------------------------------------------------------------------
 NAME VALUE
----------------------------------------------------------------------

<root><root><R-data><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val>
</ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj>
<ROW-frame_obj><id>9</id><val>0.09</val></ROW-frame_obj><ROW-frame_obj><id>10</id><val>0.1</val></ROW-frame_obj></frame_obj></R-data><images><image><img src="data:image/pngbase64">
<![CDATA[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3hT5fvH8Xe6MzoopZS9ZE9BliiIgLKHooCgyBRRQFSGG0GUJSKKogxl42KIqCB8AQEVkD0rlL1HgTZJZ3r//gD9IXaEtkk67td15VJ6npzzOWmaO+ec5zyPQUQEpZRSSrmVl6cDKKWUUvmRFmCllFLKA7QAK6WUUh6gBVgppZTyAC3ASimllAdoAVZKKaU8QAuwUkop5QFagJVSSikP0AKslFJKeYAWYKWUUsoDtAArpZRSHqAFWCmllPIALcBKKaWUB2gBVkoppTxAC7BSSi

PNG

When OUT_FMT is specified with PNG, the table functions return the response in a table with fixed columns (including an image bytes column). When calling the SQL API, you must set the special control argument ore_graphics_flag to true so that the web server can capture images rendered in the executed script.

The PNG output consists of four columns. The NAME column contains the name of the row. The NAME column value is NULL for rqEval2 and rqTableEval2 function returns. For rqRowEval2, rqGroupEval2, rqIndexEval2, the NAME column value is the chunk/group/index name. The ID column indicates the ID of the image. The VALUE column contains the return value of the executed script. The IMAGE column is a BLOB column containing the bytes of the PNG images rendered by the executed script.

The following example shows the PNG output of a rqTableEval2 function call.

set long 500

SELECT * FROM table(rqTableEval2( 
    inp_nam => 'IRIS', 
    par_lst => '{"dsname":"ds-1", "ore_graphics_flag":true, "ore_service_level":"LOW"}', 
    out_fmt => 'PNG', 
    scr_name => 'buildLM'));

---------------------------
NAME   ID   VALUE   IMAGE   
          1
      89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE950000200049444154789CE

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length].

Asynchronous Mode Output

When you set ore_async_flag to true to run an asynchronous job, set OUT_FMT to NULL for jobs that return non-XML results, or set it to XML for jobs that return XML results, as described below.

Asynchronous Mode: Non-XML Output

When submitting asynchronous jobs, for JSON, PNG, and relational outputs, set OUT_FMT to NULL when submitting the job. When fetching the job result, specify OUT_FMT in the rqJobResult call.

The following example shows how to get the JSON output from an asynchronous rqEval2 function call:

%script

set long 500

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'NULL',
    scr_name => 'RandomRedDots2'));

NAME
--------------------------------------------------------------------
VALUE 
--------------------------------------------------------------------

https://<host name>/oml/api/r-scripts/v1/jobs/<job id>

1 row selected.

SQL> select * from rqJobStatus(
        job_id => '<job id>');

NAME
--------------------------------------------------------------------
VALUE
--------------------------------------------------------------------

https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result

1 row selected.

SQL> select * from rqJobResult(
     job_id => '<job id>',
     out_fmt => 'PNG'
     );

---------------------------
NAME   ID   VALUE   IMAGE   
          1
      89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE95000020004944

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length]

Asynchronous Mode: XML Output

If XML output is expected from the asynchronous job, you must set OUT_FMT to XML when submitting the job and fetching the job result.

The following example shows how to get the XML output from an asynchronous rqEval2 function call.

set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>

1 row selected.

set long 500

SELECT * FROM rqJobStatus(
    job_id => '<Job id>'
);

  2  
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result

1 row selected.

set long 1000

SELECT * FROM rqJobResult(
    job_id => '<job id>',
    out_fmt => 'XML'
);

The result is:

---------------------------
NAME   VALUE                
       <root><R-data><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09</val></ROW-frame_obj><ROW-frame_obj><id>10</id><val>0.1</val></ROW-frame_obj></frame_obj></R-data><images><image><img src="data:image/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3xTZfvH8U/StEmTtGUV2rKnCCjrARGZZcsegqgoKoKCqIAyHIgKylABUUFAZIuAAjIUZMkqZVVk7yl7tSTpSNP79wfqT7HjFJqctlzv16uvx6e5c873pKVXcs59rtuglFIIIYQQwqeMegcQQggh7kVSgIUQQggdSAEWQgghdCAFWAghhNCBFGAhhBBCB1KAhRBCCB1IARZCCCF0IAVYCCGE0IEUYCGEEEIHUoCFEEIIHUgBFkIIIXQgBVgIIYTQgRRgIYQQQgdSgIUQQggdSA