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

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.

Examples

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        credential_name  => 'OCI_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  => 'AWS_CREDENTIAL',
        catalog_name     => 'AWS_DEMO_CATALOG',
        service_provider => 'AWS',
        cloud_params     => '{"region_id":"us-phoenix-1"}'
 );
END;
/

You must use the overloaded procedure for creating a library with External Procedures. This procedure does not require a credential object. The parameters require specifications of the EXTPROC VM and the shared library that contains the external procedures. See External Procedures Overview.

Syntax

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	Descriptions
`library_name`	Specifies the name provided to the catalog/library in the database. This parameter is mandatory.
`library_listener_url`	Specifies the FQDN (fully qualified domain name) of the EXTPROC VM that is hosting the shared library that contains the external procedures written in a third generation language like C/C++, and the Port Number at which the SQL*Net listener running in the EXTPROC VM container is accepting connection requests. The parameter is specified as a string in the format in `host_name:port_number` format. For example: `'extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com:16000'` This parameter is mandatory.
`library_wallet_dir_name`	Specifies the name of the DIRECTORY in the database where the self-signed wallet has been imported into, and stored.
`library_ssl_server_cert_dn`	Specifies the server certificate Distinguished Name (DN) obtained from the wallet. For example: `'CN=extproc-agent-170798'` This parameter is mandatory.
`library_remote_path`	Specifies the full pathname of the directory in EXTPROC VM, where the library containing the external procedures is stored. For example: `'/u01/app/oracle/extproc_libs/helloCextproc.so'` This parameter is mandatory.

Examples

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        library_name               => 'EXTPROC_LIBRARY',
        library_listener_url       => 'extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com:16000',
        library_wallet_dir_name    => 'EXTPROCWALLETDIR',
        library_ssl_server_cert_dn => 'CN=extproc-agent-170798',
        library_remote_path        => '/u01/app/oracle/extproc_libs/helloCextproc.so'
    );
END;
/

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.

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

You can manually create a SQL Function that calls its corresponding cloud function in your catalog. While SYNC_FUNCTIONS automatically retrieves and creates wrappers for all the cloud functions in a catalog, CREATE_FUNCTION enables you to create functions with custom return types and response handlers. The CREATE_FUNCTION Procedure is applicable only for Cloud Functions.

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

The response_handler must be defined in this format:

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

The return value of the response_handler is of <User Defined Type> and the response_json is a JSON document with two fields, in the following format:

'{  
"status":"<response status>",  
"response_body":"<response from the function>"
}'

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

The simple case with just input parameters to the function:

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

Example with return type and response handler:

VAR input_param CLOB;
VAR l_return_type VARCHAR2(100);
VAR l_response_handler VARCHAR2(1000);

-- Define function parameters
exec :input_param       := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');

PL/SQL procedure successfully completed.

exec :l_return_type     := 'fintech_rt';

PL/SQL procedure successfully completed.

exec :l_response_handler := 'fintech_response_handler';

PL/SQL procedure successfully completed.

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
        credential_name  => 'OCI_CRED',
        catalog_name     => 'OCI_DEMO_CATALOG',
        function_name    => 'FINTECH_FUNCTION',
        function_id      => 'ocid1.fnfunc.oc1.phx.aaabbbcccc_example',
        input_args       => :input_param,
        return_type      => :l_return_type,
        response_handler => :l_response_handler
 );
END;
/

Next, use this type and response handler during the manual creation of the SQL Wrapper Function.

VAR input_param CLOB;
VAR l_return_type VARCHAR2(100);
VAR l_response_handler VARCHAR2(1000);

-- Define function parameters
exec :input_param       := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');

PL/SQL procedure successfully completed.

exec :l_return_type     := 'fintech_rt';

PL/SQL procedure successfully completed.

exec :l_response_handler := 'fintech_response_handler';

PL/SQL procedure successfully completed.

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
        credential_name  => 'OCI_CRED',
        catalog_name     => 'OCI_DEMO_CATALOG',
        function_name    => 'FINTECH_FUNCTION',
        function_id      => 'ocid1.fnfunc.oc1.phx.aaabbbcccc_example',
        input_args       => :input_param,
        return_type      => :l_return_type,
        response_handler => :l_response_handler
 );
END;
/

Once the function is created, you can DESCRIBE the function to get its return details.

DESC fintech_fun
COLUMN STATUS format a30
COLUMN OUTPUT format a30

You can then invoke the function, providing values for the input parameters

SET SERVEROUTPUT ON

DECLARE
    l_comp fintech_rt;
BEGIN
    l_comp := fintech_fun(command=>'tokenize',value => 'PHI_INFORMATION');

    DBMS_OUTPUT.put_line ('Status of the function   =  '|| l_comp.status);
    DBMS_OUTPUT.put_line ('Response of the function =  '|| l_comp.output);
END;
/

This invokes the fintech_fun cloud function by calling the function reference oocid1.funfn.oci.phx.aaaaaa_example in the OCI_DEMO_CATALOG catalog.

Usage Notes

For invoking External Procedures, you can use the same syntax that is used for User Defined Functions. For example, if you have created a library called EXTPROC_LIBRARY in your database using the CREATE_CATALOG Procedure for External Procedures, you can define a SQL function as shown in this example, and invoke it from a SQL statement.

SQL> CREATE OR REPLACE FUNCTION HELLOCEXTPROC RETURN VARCHAR2 AS
    LANGUAGE C
    LIBRARY EXTPROC_LIBRARY
    NAME "helloCextproc";
/

Function created.

SQL> SELECT HELLOCEXTPROC() FROM dual;

HELLOCEXTPROC()
----------------------------------------------------------------------------------
Hello C Extproc from FQDN: extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com

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.

Note:

The LIST_FUNCTIONS Procedure is applicable only for Cloud Functions.

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.

Note:

The SYNC_FUNCTIONS Procedure is applicable only for Cloud Functions.

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.