The SQL interface for Oracle R Enterprise embedded R execution allows you to execute R functions in production database applications. The SQL interface has procedures for the following actions:
Adding and removing a script from the Oracle R Enterprise R 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 Oracle R Enterprise datastore
Data dictionary views provide information about scripts and datastores.
This SQL interface is described in the following topics:
Oracle R Enterprise 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:
rqEval
rqGroupEval
rqRowEval
rqTableEval
The R interface functions and the SQL equivalents are listed in Table 6-1.
For the rqGroupEval function, Oracle R Enterprise 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.
Some general aspects of the SQL table functions are described in the following topics:
See the reference pages for the functions for more information about them, including examples of their use.
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 6-2 SQL Table Function Parameters
| Parameter | Description |
|---|---|
|
|
A cursor that specifies the data that is input to the R function specified by |
|
|
A cursor that specifies arguments to pass to the R function. The parameters cursor consists of a single row of scalar values. An argument can be a string or a numeric value. You can specify multiple arguments in the cursor. Arguments to an R function are case sensitive, so you should put names, such as a column name, in double quotes. In the cursor, you can also specify as scalar values an Oracle R Enterprise control argument or the names of serialized R objects, such as predictive models, that are in an Oracle R Enterprise datastore. The value of this parameters cursor can be |
|
|
An output table definition. The value of this argument can be |
|
|
For the |
|
|
For the |
|
|
The name of a script in the Oracle R Enterprise R script repository. |
Related Topics
The Oracle R Enterprise 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_QRY 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_QRY values:
NULL, which results in a table that has a serialized object that can contain both data and image objects.
A table signature specified in a SELECT statement, which results in a table that has the defined structure. The result of the R function must be a data.frame. No images are returned.
The string 'XML', which results in a table that has a CLOB that can contain both structured data and graph images in an XML string. The non-image R objects, such as data.frame or model objects, are provided first, followed by the base 64 encoding of a PNG representation of the image.
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.
To establish a connection to Oracle R Enterprise on the Oracle Database server during the embedded R execution, you can specify the control argument ore.connect in the parameters cursor. 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 Oracle R Enterprise connection is required to save objects in an Oracle R Enterprise R object datastore or to load objects from a datastore. It also allows you to explicitly use the Oracle R Enterprise transparency layer.
See Also:
"Optional and Control Arguments" for information on other control arguments
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 Oracle R Enterprise R script repository. The PL/SQL procedures sys.rqScriptCreate and sys.rqScriptDrop create and drop scripts. To create a script or drop one from the Oracle R Enterprise R 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 R 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(rqEval(NULL, ‘select 1 x from dual’, ‘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 Oracle R Enterprise R script repository scripts and the data dictionary views that contain information about scripts.
Table 6-3 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 Oracle R Enterprise R script repository with the provided name. |
sys.rqScriptDrop |
Removes the named R function from the Oracle R Enterprise R script repository. |
Table 6-4 Data Dictionary Views for Scripts
| Data Dictionary View | Description |
|---|---|
ALL_RQ_SCRIPTS |
Describes the scripts in the Oracle R Enterprise R script repository that are available to the current user |
USER_RQ_SCRIPTS |
Describes the scripts in the Oracle R Enterprise R script repository that are owned by the current user. |
USER_RQ_SCRIPT_PRIVS |
Describes the scripts in the Oracle R Enterprise R 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 Oracle R Enterprise R script repository. |
This example uses the sys.rqScriptCreate procedure to create a script in the Oracle R Enterprise R script repository.
This 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 Oracle R Enterprise script repository.
-- Create a script named myRandomRedDots2 and add it to the R 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 Scott.
BEGIN
rqGrant('myRandomRedDots2', 'rqscript', 'SCOTT');
END;
/
-- View the users granted read access to myRandomRedDots2.
select * from USER_RQ_SCRIPT_PRIVS;
NAME GRANTEE
---------------- -------
myRandomRedDots SCOTT
-- Revoke the read privilege access from Scott.
BEGIN
rqRevoke('myRandomRedDots2', 'rqscript', 'SCOTT');
END;
/
-- Remove the script from the R script repository.
BEGIN
sys.rqScriptDrop('myRandomRedDots2');
END;
/
Oracle R Enterprise 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 6-5 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 6-6 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. |