DBMS_CLOUD_FUNCTION Package

The DBMS_CLOUD_FUNCTION package allows you invoke OCI and AWS Lambda remote functions in your Autonomous Database as SQL functions.

Summary of DBMS_CLOUD_FUNCTION Subprograms
This table summarizes the subprograms included in the DBMS_CLOUD_FUNCTION package.

Parent topic: Autonomous Database Supplied Package Reference

Summary of DBMS_CLOUD_FUNCTION Subprograms

This table summarizes the subprograms included in the DBMS_CLOUD_FUNCTION package.

Subprogram	Description
CREATE_CATALOG Procedure	This procedure creates a catalog.
CREATE_FUNCTION Procedure	This procedure creates functions in a catalog.
DROP_CATALOG Procedure	This procedure drops a catalog and functions created using the catalog
DROP_FUNCTION Procedure	This procedure drops functions from a catalog.
LIST_FUNCTIONS Procedure	This procedure lists all the functions in a catalog.
SYNC_FUNCTIONS Procedure	This procedure creates a PL/SQL wrapper for adding new functions to the catalog and removing wrappers for functions that have been deleted from the catalog.

CREATE_CATALOG Procedure
This procedure creates a catalog in the database. The DBMS_CLOUD_FUNCTION.CREATE_CATALOG procedure creates a catalog. A catalog is a set of functions that creates the required infrastructure to execute subroutines. This procedure is overloaded.
CREATE_FUNCTION Procedure
This procedure creates functions in a catalog. There are two overloaded DBMS_CLOUD_FUNCTION.CREATE_FUNCTION procedures.
DROP_CATALOG Procedure
The DBMS_CLOUD_FUNCTION.DROP_CATALOG procedure drops the catalog and functions created using the catalog. This procedure is overloaded.
DROP_FUNCTION Procedure
The DBMS_CLOUD_FUNCTION.DROP_FUNCTION procedure drops the function. This procedure is overloaded.
LIST_FUNCTIONS Procedure
This procedure lists all the functions in a catalog.
SYNC_FUNCTIONS Procedure
This procedure creates a PL/SQL wrapper for adding new functions to the catalog and removing wrappers for functions that have been deleted from the catalog.

Parent topic: DBMS_CLOUD_FUNCTION Package

CREATE_CATALOG Procedure

This procedure creates a catalog in the database. The DBMS_CLOUD_FUNCTION.CREATE_CATALOG procedure creates a catalog. A catalog is a set of functions that creates the required infrastructure to execute subroutines. This procedure is overloaded.

Syntax

DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
    credential_name             IN VARCHAR2,
    catalog_name                IN VARCHAR2,
    service_provider            IN VARCHAR2, 
    cloud_params                IN CLOB
);

DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
    library_name                IN VARCHAR2,
    library_listener_url        IN VARCHAR2,
    library_wallet_dir_name     IN VARCHAR2,
    library_ssl_server_cert_dn  IN VARCHAR2,
    library_remote_path         IN VARCHAR2
);

Parameters

Parameter	Description
`credential_name`	Specifies the name of the credential for authentication. This parameter is mandatory.
`service_provider`	Specifies the type of the service provider. This parameter can have `OCI` or `AWS` or `AZURE` as a parameter value. This parameter is mandatory.
`catalog_name`	Specifies the catalog name. This parameter is mandatory.
`cloud_params`	Provides parameter to the function. For example, Compartment OCID, Regions and Azure subscription_id. This parameter is mandatory.
`library_name`	Specifies the name of the library when creating a remote library. This parameter is mandatory.
`library_listener_url`	Specifies the remote location of the library. The parameter accepts a String value in `host_name:port_number` format. For example: `EHRPMZ_DBDOMAIN.adb-us-phoenix1.com:16000` This parameter is mandatory.
`library_remote_path`	Specifies the remote library path. You must provide the full absolute path to the remote library. For example:`/u01/app/oracle/product/21.0.0.0/client_1/lib/libst_shape.so` This parameter is mandatory.
`library_wallet_dir_name`	Specifies the directory where the self-signed wallet is stored. This parameter is mandatory.
`library_ssl_server_cert_dn`	Specifies the server certificate Distinguished Name (DN). This parameter is mandatory.

Errors

Error Code	Description
`ORA-20000`	This error is raised in either of the following conditions: `cloud_params` value is missing or incorrect parameter values are passed. `library_name` value is not unique when creating a library.
`ORA-20001`	This error is raised in either of the following conditions: The credential referenced in the `credential_name` does not exist. The Listener specified at the `library_listener_url` is not reachable when creating a library.
`ORA-20002`	This error is raised in either of the following conditions: This error is raised when the catalog already exists. The specified Server certificate directory is empty when creating a library.
`ORA-20009`	This error is raised when the service provider doesn't exist.

Examples

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        credential_name  => 'DEFAULT_CREDENTIAL', 
        catalog_name     => 'OCI_DEMO_CATALOG', 
        service_provider => 'OCI',
        cloud_params     => ("region_id":"us-phoenix-1", "compartment_id":"compartment_id"
 );
END;
/

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        credential_name  => 'AZURE$PA', 
        catalog_name     => 'AZURE_DEMO_CATALOG', 
        service_provider => 'AZURE',
        cloud_params     => '{"subscription_id":"44495e6a-8ff1-4161-b387-0e14e675b878"}'
 );
END;
/

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (   
    library_name               => 'EXT_DEMOLIB',
    library_listener_url       => 'remote_extproc_hostname:16000',
    library_wallet_dir_name    => 'WALLET_DIR',
    library_ssl_server_cert_dn => 'CN=VM Hostname',
    library_remote_path        => '/u01/app/oracle/extproc_libs/library name'
 );
END;
/

Usage Note

To create a catalog you must be logged in as the ADMIN user or have privileges on the following:
- DBMS_CLOUD_OCI_FNC_FUNCTIONS_INVOKE
- DBMS_CLOUD_OCI_FNC_FUNCTIONS_INVOKE_INVOKE_FUNCTION_RESPONSE_T
- DBMS_CLOUD
- Read privilege on USER_CLOUD_FUNCTION
- Read privilege on USER_CLOUD_FUNCTION_CATALOG

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

CREATE_FUNCTION Procedure

This procedure creates functions in a catalog. There are two overloaded DBMS_CLOUD_FUNCTION.CREATE_FUNCTION procedures.

CREATE_FUNCTION Syntax

The DBMS_CLOUD_FUNCTION.CREATE_FUNCTION procedure is only supported for cloud functions.

DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
    credential_name   IN VARCHAR2,
    catalog_name      IN VARCHAR2,
    function_name     IN VARCHAR2,
    function_id       IN VARCHAR2,
    input_args        IN CLOB      DEFAULT NULL,
    return_type       IN VARCHAR2  DEFAULT 'CLOB',
    response_handler  IN VARCHAR2  DEFAULT NULL
);

Response Handler signature

<USER DEFINED TYPE> response_handler_name(function_response in CLOB)RETURNS CLOB;

The return type of this is user defined type or PL/SQL type. The function_response is of JSON with fields.

'{  
"STATUS":"<RESPONCE STATUS>",  
"RESPONSE_BODY":"<FUNCTION RESPONSE>"
}'

CREATE_FUNCTION Parameters

CREATE_FUNCTION Exceptions

Parameter	Description
`credential_name`	Specifies the name of the credential for authentication. This parameter is mandatory.
`catalog_name`	Specifies the catalog name. This parameter is mandatory.
`function_name`	Specifies the PL/SQL function name. This parameter is mandatory.
`function_id`	The `function_id` parameter value refers to the OCI function, AWS Lambda or Azure function. This parameter is mandatory.
`input_args`	Specifies the key value JSON pair accepting input arguments and their types.
`return_type`	Defines the return type of the function. The return type is of `CLOB` data type.
`response_handler`	Specifies the user defined callback to handle response.

Error Code	Description
`ORA-20001`	This error is raised when the credential referenced in the `credential_name` does not exist.
`ORA-20003`	This error is raised when the specified catalog does not exist.
`ORA-20004`	This error is raised when the specified function already exists.
`ORA-20005`	This error is raised when the function ID or function Amazon Resource Names (ARN) does not exist.
`ORA-20006`	This error is raised when the input arguments are invalid.
`ORA-20007`	This error is raised when the return type is missing or invalid.
`ORA-20008`	This error is raised when the response handler is missing or invalid.

CREATE_FUNCTION Example

VAR function_args CLOB;
EXEC :function_args := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');
BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
	credential_name  => 'DEFAULT_CREDENTIAL',    
       catalog_name     => 'OCI_DEMO_CATALOG',
       function_name    => 'demo_function', 
       function_id      => 'ocid1.fnfunc.oc1.phx.aaaaaaaazkrbjv6ntowwxlbbp5ct4otsj4o2hdw4ayosyosv4sthmya2lyza',
       input_args       => :function_args);
 );
END;
/

CREATE_FUNCTION Syntax

DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
  library_name         IN VARCHAR2,
  function_name        IN VARCHAR2,
  function_id          IN VARCHAR2 DEFAULT NULL,
  plsql_params         IN CLOB DEFAULT NULL,
  external_params      IN CLOB DEFAULT NULL,
  api_type             IN VARCHAR2 DEFAULT 'FUNCTION',
  with_context         IN BOOLEAN DEFAULT FALSE,
  return_type          IN VARCHAR2 DEFAULT NULL
);

CREATE_FUNCTION Parameters

Parameter	Description
`library_name`	Specifies the remote library name. This parameter is mandatory.
`function_name`	Specifies the PL/SQL function name. This parameter is mandatory.
`function_id`	The `function_id` parameter value refers to the external procedures (`extproc`). If the value for `function_id` is not provided, the value in the `function_name` is used.
`plsql_params`	Specifies the key value JSON pair accepting the parameters for the PL/SQL wrapper. The values must be provided in the `"var_name":"modetype, datatype"` format. `var_name` : is the name of the variable. This parameter is mandatory. `modetype` : specifies the variable mode. The variable mode can be one of the following: `IN` `OUT` `IN OUT` `datatype` : specifies the variable datatype. This parameter is mandatory. The default value for `plsql_params` is `NULL`.
`external_params`	Specifies the parameters that need to be provided to the external C function. If value is not provided for `external_params`, the PL/SQL parameters are used.
`api_type`	Specifies the type of API (function or procedure). The default value for `api_type` is `function`.
`with_context`	Specifies that a context pointer is passed to the external procedure. This context is used by the external C library for connecting back to the database. The default value for `with_context` is `FALSE`.
`return_type`	Specifies the return type of the function created.

CREATE_FUNCTION Example

DECLARE
    plsql_params clob    := TO_CLOB('{"sal": "IN, FLOAT", "comm" :"IN, FLOAT"}');
    external_params clob := TO_CLOB('sal FLOAT, sal INDICATOR SHORT, comm FLOAT, comm INDICATOR SHORT,
    RETURN INDICATOR SHORT, RETURN FLOAT');
BEGIN
DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
    LIBRARY_NAME    => 'demolib',
    FUNCTION_NAME   => '"PercentComm"',
    PLSQL_PARAMS    => plsql_params,
    EXTERNAL_PARAMS => external_params,
    API_TYPE        => 'FUNCTION',
    WITH_CONTEXT    => FALSE,
    RETURN_TYPE     => 'FLOAT'
);
END;
/

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

DROP_CATALOG Procedure

The DBMS_CLOUD_FUNCTION.DROP_CATALOG procedure drops the catalog and functions created using the catalog. This procedure is overloaded.

Syntax

DBMS_CLOUD_FUNCTION.DROP_CATALOG (
    catalog_name      IN VARCHAR2
 );

DBMS_CLOUD_FUNCTION.DROP_CATALOG (
    library_name      IN VARCHAR2
 );

Parameters

Parameter Description

Parameter	Description
`catalog_name`	Specifies the catalog name. This parameter is mandatory.
`library_name`	Specifies the library name. This parameter is mandatory.

catalog_name

Specifies the catalog name.

This parameter is mandatory.

library_name

Specifies the library name.

This parameter is mandatory.

Errors

Error Code	Description
`ORA-20003`	This error is raised when the specified catalog doesn't exist.

Example:

BEGIN
    DBMS_CLOUD_FUNCTION.DROP_CATALOG (
      catalog_name     => 'OCI_DEMO_CATALOG'
  );
END;
/

Example:

BEGIN
    DBMS_CLOUD_FUNCTION.DROP_CATALOG (
      library_name     => 'library_name'
  );
END;
/

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

DROP_FUNCTION Procedure

The DBMS_CLOUD_FUNCTION.DROP_FUNCTION procedure drops the function. This procedure is overloaded.

Syntax

The DBMS_CLOUD_FUNCTION.DROP_FUNCTION procedure is only supported for cloud functions.

DBMS_CLOUD_FUNCTION.DROP_FUNCTION (
    catalog_name      IN VARCHAR2,
    function_name     IN VARCHAR2
);

DBMS_CLOUD_FUNCTION.DROP_FUNCTION (
    library_name     IN VARCHAR2,
    function_name    IN VARCHAR2
);

Parameters

Parameter Description

Parameter	Description
`catalog_name`	Specifies the catalog name. This parameter is mandatory.
`function_name`	Specifies the name of the function to be dropped. This parameter is mandatory.
`library_name`	Specifies the library name. This parameter is mandatory.

catalog_name

Specifies the catalog name.

This parameter is mandatory.

function_name

Specifies the name of the function to be dropped.

This parameter is mandatory.

library_name

Specifies the library name.

This parameter is mandatory.

Examples

BEGIN
    DBMS_CLOUD_FUNCTION.DROP_FUNCTION (
       catalog_name     => 'OCI_DEMO_CATALOG',
       function_name    => 'demo_function');
END;
/

BEGIN
    DBMS_CLOUD_FUNCTION.DROP_FUNCTION (
       library_name     => 'EXTPROC_DEMO_LIBRARY',
       function_name    => 'demo_function');
END;
/

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

LIST_FUNCTIONS Procedure

This procedure lists all the functions in a catalog.

Syntax

DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (
    credential_name   IN VARCHAR2,
    catalog_name      IN VARCHAR2,
    function_list     OUT VARCHAR2
);

Parameters

Parameter Description

credential_name

Specifies the name of the credential for authentication.

This parameter is mandatory.

function_list

Returns the list of functions in JSON format.

This parameter is mandatory.

catalog_name

Specifies the catalog name.

This parameter is mandatory.

Errors

Error Code	Description
`ORA-20000`	This error is raised when `cloud_params` value is missing or incorrect parameter values are passed.
`ORA-20001`	This error is raised when the credential referenced in the `credential_name` does not exist.
`ORA-20003`	This error is raised when the specified catalog does not exist.

Example:

VAR function_list CLOB;
BEGIN
    DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (
	credential_name  => 'DEFAULT_CREDENTIAL', 
	catalog_name     => 'OCI_DEMO_CATALOG',  
	function_list    => :function_list);
 );
END;
/
SELECT JSON_QUERY(:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual;

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

SYNC_FUNCTIONS Procedure

This procedure creates a PL/SQL wrapper for adding new functions to the catalog and removing wrappers for functions that have been deleted from the catalog.

Syntax

DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS (
    catalog_name      IN VARCHAR2,
    refresh_rate     IN VARCHAR2 DEFAULT 'DAILY'
);

Parameters

Parameter Description

Parameter	Description
`catalog_name`	Specifies the catalog name. This parameter is mandatory.
`refresh_rate`	Specifies the refresh rate of the function. `refresh_rate` can accept the following values: `HOURLY` `DAILY` `WEEKLY` `MONTHLY` The default value for this parameter is `DAILY`.

catalog_name

Specifies the catalog name.

This parameter is mandatory.

refresh_rate

Specifies the refresh rate of the function.

refresh_rate can accept the following values:

HOURLY
DAILY
WEEKLY
MONTHLY

The default value for this parameter is DAILY.

Errors

Error Code	Description
`ORA-20003`	This error is raised when the specified catalog does not exist.
`ORA-20004`	This error is raised when an invalid value is passed for the `refresh_rate` parameter.

Example:


BEGIN
    DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS (
       catalog_name     => 'OCI_DEMO_CATALOG'
 );
END;
/

Parent topic: Summary of DBMS_CLOUD_FUNCTION Subprograms

Oracle Cloud Infrastructure Documentation

Summary of DBMS_CLOUD_FUNCTION Subprograms

CREATE_CATALOG Procedure

CREATE_FUNCTION Procedure

DROP_CATALOG Procedure

DROP_FUNCTION Procedure

LIST_FUNCTIONS Procedure

SYNC_FUNCTIONS Procedure