6.3.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(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 script repository scripts and the data dictionary views that contain information about scripts.

Table 6-9 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 6-10 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 6-19 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 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 script repository.
BEGIN
  sys.rqScriptDrop('myRandomRedDots2');
 END; 
 /