Oracle Cloud Infrastructure Documentation

Steps to Invoke OCI Cloud Functions as SQL Functions

Shows the steps to invoke OCI remote functions as SQL functions in your database.

To invoke cloud functions in OCI as SQL functions, we will be creating a catalog of SQL wrapper functions that reference and call their respective cloud function via their API endpoints. Before you create this catalog, it is assumed here that you have created the necessary cloud functions to be referenced by this catalog.

See Creating and Deploying Functions for more information on the creation and deployment of Oracle Cloud Infrastructure Functions and Application (ie a group of OCI Functions).

  1. Create credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. The credential object that you are creating must be of type private key.
    SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'OCI_CRED',
       user_ocid       => 'user_ocid',
       tenancy_ocid    => 'tenancy_ocid',
       private_key     => 'private_key',
       fingerprint     => 'fingerprint'
  );
END;
/

PL/SQL procedure successfully completed.

    This creates the OCI_CRED credential.

    See CREATE_CREDENTIAL Procedure for more information.

    Note

    If you are using a Resource Principal for authentication, the necessary policies required for OCI Function access must be configured. See Details for Functions for more information.
  2. Create a catalog.

    A catalog is a collection of wrapper functions that reference and call their respective cloud functions via their API endpoints.

    Example to create a catalog for Oracle Cloud Infrastructure Functions. 

    BEGIN
DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
    credential_name  => 'OCI_CRED',
    catalog_name     => 'OCI_DEMO_CATALOG',
    service_provider => 'OCI',
    cloud_params     => '{"region_id":"phx",
      "compartment_id":"compartment_id"}'
);
END;
/

PL/SQL procedure successfully completed.

    This creates the OCI_DEMO_CATALOG catalog.

    See CREATE_CATALOG Procedure for more information. You can query DBA_CLOUD_FUNCTION_CATALOG View and USER_CLOUD_FUNCTION_CATALOG View views to retrieve the list of all the catalogs in your database.

  3. You can list the functions in a catalog.

    Following is the example to list Oracle Cloud Infrastructure Functions: 

    VAR function_list CLOB;

BEGIN
    DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (
      credential_name  => 'OCI_CRED', 
      catalog_name     => 'OCI_DEMO_CATALOG',
      function_list    => :function_list
 );
END;
/

PL/SQL procedure successfully completed.

SELECT JSON_QUERY (:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual;
SEARCH_RESULTS                        --This is a sample output
------------------------------------------------------------------------------------------------
[
  {
    "functionName"   : "create_par",
    "functionId"     : "ocid.funfc.oc1.phx.aaaa_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
  {
    "functionName"   : "fintech",
    "functionId"     : "ocid.funfc.oc1.phx.bbbb_example"
SEARCH_RESULTS
------------------------------------------------------------------------------------------------
4ayosyosv4sthmya2lyza",
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud.com_example"
  },
  {
    "functionName"   : "jwt_codec",
    "functionId"     : "ocid.funfc.oc1.phx.jwt_code_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
SEARCH_RESULTS
------------------------------------------------------------------------------------------------
  {
    "functionName"   : "oci-objectstorage-create-par-python",
    "functionId"     : "ocid.funfc.oc1.phx.aaaaaaaas_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
  {
    "functionName"   : "run_dbt",
    "functionId"     : "ocid.funfc.oc1.phx.aaaaaaaav_example",

SEARCH_RESULTS
------------------------------------------------------------------------------------------------
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  }
]

    See LIST_FUNCTIONS Procedure for more information.

  4. Run the DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS to create wrapper SQL functions. You can use one of the following methods to create the wrapper SQL functions in the catalog, that call their respective cloud functions:

    • SYNC_FUNCTIONS: SYNC_FUNCTIONS is the quickest and simplest method, which automatically syncs (creates or deletes) wrapper functions in the catalog with the complete list of cloud functions defined in the region, compartment, and tenancy with which the catalog was created. For example: 

    
BEGIN
 DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS (
        catalog_name => 'OCI_DEMO_CATALOG'
 );
END;
/
PL/SQL procedure successfully completed.

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

    Run the following query to verify the sync.

    
SELECT object_name FROM sys.all_objects WHERE owner='TEST_USER' AND object_type='FUNCTION';

OBJECT_NAME
--------------------------------------------------------------------------------
CREATE_PAR
FINTECH
JWT_CODEC
OCI-OBJECTSTORAGE-CREATE-PAR-PYTHON
RUN_DBT
    Note

    Keep a note of the current user in order to run this command.

    See SYNC_FUNCTIONS Procedure for more information.

    • You can manually create a SQL Function in your catalog that calls its respective cloud function using DBMS_CLOUD.CREATE_FUNCTION. For example:

    Example to create a function in the OCI_DEMO_CATALOG catalog. 

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

PL/SQL procedure successfully completed.

    This creates the FINTECH_FUN function in the OCI_DEMO_CATALOG catalog.

    The FINTECH_FUN function in the catalog is a reference to the respective cloud function whose endpoint is referenced by the FUNCTION_ID parameter. Invoking the function in the catalog along with its arguments runs the corresponding cloud function and provides the output returned by the cloud function.

    Manually creating a function in the catalog also allows you to create custom return types and response handlers. For example:

    CREATE OR REPLACE TYPE fintech_rt AS OBJECT (
    STATUS  VARCHAR2(1000),  
    OUTPUT  CLOB
);
/

Type created.
    CREATE OR REPLACE FUNCTION fintech_response_handler(function_response in CLOB) 
RETURN fintech_rt
IS
l_comp   fintech_rt;
l_json_obj  JSON_OBJECT_T;
status  VARCHAR2(1000);
output  CLOB;
BEGIN
      l_json_obj := JSON_OBJECT_T.parse(function_response); 
      status     := l_json_obj.get('STATUS').to_string;
      output     := l_json_obj.get('RESPONSE_BODY').to_string;
      l_comp     := fintech_rt(status,output);
      RETURN l_comp;
END;
/
Function created.

VAR input_param clob;
VAR  l_return_type varchar2(100);
VAR  l_reponse_handler varchar2(1000);

exec :input_param       := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');
exec :l_return_type     := 'fintech_rt';
exec :l_reponse_handler := 'fintech_response_handler';

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (CREDENTIAL_NAME => 'OCI_CRED', FUNCTION_NAME => 'fintech_fun', CATALOG_NAME => 'OCI_DEMO_CATALOG', FUNCTION_ID => 'ocid1.funfn.oci.phx.aaaaaa_example', INPUT_ARGS => :input_param, RETURN_TYPE => :l_return_type ,REPONSE_HANDLER =>:l_reponse_handler);

PL/SQL procedure successfully completed.

    You can query DBA_CLOUD_FUNCTION View and USER_CLOUD_FUNCTION View views to retrieve the list of all the functions in your database.

    See CREATE_FUNCTION Procedure for more information.

  5. After the function is created you can DESCRIBE and invoke it.
    DESC fintech_fun
COLUMN STATUS format a30
COLUMN OUTPUT format a30
    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;
/

PL/SQL procedure successfully completed.

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

  6. You can drop an existing function using DROP_FUNCTION procedure. For example:
    EXEC DBMS_CLOUD_FUNCTION.DROP_FUNCTION (CATALOG_NAME => 'OCI_DEMO_CATALOG', FUNCTION_NAME => 'fintech_fun');

PL/SQL procedure successfully completed.

    This drops the FINTECH_FUN function from the OCI_DEMO_CATALOG catalog.

    See DROP_FUNCTION Procedure for more information.

  7. You can drop an existing catalog using DROP_CATALOG procedure. For example:
    BEGIN
    DBMS_CLOUD_FUNCTION.DROP_CATALOG (
      catalog_name     => 'OCI_DEMO_CATALOG'
  );
END;
/

PL/SQL procedure successfully completed.

    This drops the OCI_DEMO_CATALOG from your database.

    See DROP_CATALOG Procedure for more information.