DBMS_CLOUD_FUNCTION Package

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

Note:

Support for DBMS_CLOUD_FUNCTION is available in Oracle Database 19c starting with version 19.29, and in Oracle AI Database 26ai starting with version 23.26.

Summary of DBMS_CLOUD_FUNCTION Subprograms

This table summarizes the subprograms included in the DBMS_CLOUD_FUNCTION package.

Table - DBMS_CLOUD_FUNCTION Subprograms

Subprogram	Desription
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 enables adding new functions to the catalog and removing functions that have been deleted from the catalog.

CREATE_CATALOG Procedure

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

Syntax

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

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` 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 and Regions. This parameter is mandatory.

Errors

Error Code	Description
`ORA-20000`	This error is raised in the following condition: `cloud_params` value is missing or incorrect parameter values are passed.
`ORA-20001`	This error is raised in the following condition: The credential referenced in the `credential_name` does not exist.
`ORA-20002`	This error is raised in the following condition: This error is raised when the catalog already exists.
`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;
/

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

CREATE_FUNCTION Procedure

This procedure creates functions in a catalog.

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>"
}'

Parameters

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 or AWS Lambda. 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.

Errors

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.

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

DROP_CATALOG Procedure

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

Syntax

DBMS_CLOUD_FUNCTION.DROP_CATALOG (
    catalog_name      IN VARCHAR2
 );

Parameters

Parameter Description

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

catalog_name

Specifies the catalog 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;
/

DROP_FUNCTION Procedure

The DBMS_CLOUD_FUNCTION.DROP_FUNCTION procedure drops the function.

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

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.

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.

Errors

Error Code	Description
`ORA-20003`	This error is raised when the specified function does not exist.

Examples

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

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

SYNC_FUNCTIONS Procedure

This procedure enables adding new functions to the catalog and removing 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;
/

Title and Copyright Information

Oracle and/or its affiliates.