Steps to Invoke Azure Functions as SQL Functions

Shows the steps to invoke Azure remote functions as SQL functions in your database using HTTP trigger.

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

  1. To access Azure functions you need to use Azure Service Principal with Autonomous Database. You must grant the Website Contributor role to the Azure Service Principal for the Azure function app under its Access control (IAM).
  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 Azure functions.

    BEGIN
        DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
            credential_name  => 'AZURE$PA', 
            catalog_name     => 'AZURE_DEMO_CATALOG', 
            service_provider => 'AZURE',
            cloud_params     => '{"subscription_id":"XXXXXXXXXXXXXXXXXXXXXXXX_example"}'
     );
    END;
    /
    

    The SUBSCRIPTION_ID value in the CLOUD_PARAMS is the subscription_id of the Azure function app.

    This creates the AZURE_DEMO_CATALOG catalog and catalogs all the functions of the provided SUBSCRIPTION_ID.

    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 Azure functions:

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

    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 Azure functions. For example:

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

    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.

    Note

    When an Azure function is added, deleted, or disabled on the Azure function app, you must run the SYNC_FUNCTIONS to update the corresponding PL/SQL wrappers in 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';
    
    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 Azure function using AZURE_DEMO_CATALOG.

    Example to create a function in the AZURE_DEMO_CATALOG catalog.

    BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
      credential_name => 'AZURE$PA',
      catalog_name    => 'AZURE_DEMO_CATALOG', 
      function_name   => 'azure_testfunc', 
      function_id     => 'function_id_path',
      input_args      => :function_args
    );
    END;
    /
    
    Note

    The maximum length of the function name is limited to 100 characters.

    This creates the AZURE_TESTFUNC function in the AZURE_DEMO_CATALOG catalog.

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

    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.

    See Azure Functions HTTP trigger for more information.

  5. After the function is created you can DESCRIBE and invoke it.
    DESC AZURE_TESTFUNC
    SELECT AZURE_TESTFUNC(NULL) FROM dual;

    This invokes the AZURE_TESTFUNC function by calling the function reference /subscriptions/44496e556-8ssp-4262-b389-0f15f685c879/resources/ADBStest/providers/Microsoft.Web/sites/AZUREADBS/functions/HttpTrigger_example in the AZURE_DEMO_CATALOG catalog.

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

    This drops the AZURE_TESTFUNC function from the AZURE_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     => 'AZURE_DEMO_CATALOG'
      );
    END;
    /
    

    This drops the AZURE_DEMO_CATALOG from your database.

    See DROP_CATALOG Procedure for more information.