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.

-- 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; 
 /