Oracle Cloud Infrastructure Documentation
Try Free Tier

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.

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

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        credential_name  => 'GCP$PA', 
        catalog_name     => 'GCP_DEMO_CATALOG', 
        service_provider => 'GCP',
        cloud_params     => '{"project_id":"example_XXXXXX"}'
 );
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. 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;
/

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

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

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

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

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;

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

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

Was this article helpful?