Mature clouds today provide the ability for users to easily write and run a piece of code without provisioning or managing any server – These are serverless compute services generally known as cloud functions.  You may use cloud functions to build applications that respond to events, such as:

• To build serverless backends for web applications, mobile applications, and other types of applications. This means that you don’t have to worry about managing servers or scaling infrastructure.
• To process real-time data streams from sources such as IoT devices, sensors, and social media. This allows you to build applications that respond to events immediately.
• To create intelligent applications that use machine learning and artificial intelligence, such as to classify images. This allows you to build applications that can learn and adapt over time.

On Autonomous Database (ADB), today we released new functionality that makes calling cloud functions dead simple, right in your SQL queries using User Defined Functions. User Defined Functions are simply wrapper functions that contain logic, transformations and functionality that help extend the ability of SQL, and these are used here in ADB’s DBMS_CLOUD package to invoke cloud functions that are deployed in the cloud, such as on OCI (OCI Functions) or AWS (Lambda).

Let’s dive right into what this new, simplified functionality looks like.

Create a User Defined Function (UDF) to invoke a Cloud Function

Note: If you don’t already have a cloud function deployed and ready to invoke via SQL, follow well-structured documentation to deploy your first OCI Function, which simply displays your favorite “Hello World” as its response. You may also look at using some pre-built OCI Functions for different operations, to deploy, via my colleagues’ blog post here. 

If if you’re a visual learner like me, here are also video walkthroughs to set up an OCI Function or an AWS Lambda Function.

To create a User Defined Function in ADB, you will follow these steps:

1. Create a credential for authorization to your choice of cloud. Here, we create one named OCI_CRED for sample authorization to Oracle Cloud.
    

   SET DEFINE OFF
   
   	 BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL (
          credential_name => ‘OCI_CRED’,
          user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
          tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
          private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
          fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
   END;
   /
   

    

   1. Create a function catalog in your database schema. A catalog is a container or grouping that stores the metadata and configuration of your User Defined Function. You may create multiple function catalogs for different purposes or groups of UDFs. Here we create a catalog named “OCI_DEMO_CATALOG”.
       

      BEGIN
      
      	    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
      
      	        credential_name  => 'OCI_CRED', 
      
      	        catalog_name     => 'OCI_DEMO_CATALOG', 
      
      	        service_provider => 'OCI',
      
      	        cloud_params     => ('"region_id":"us-phoenix-1", "compartment_id":"ocid1.tenancy.oc1..aaaaaaaa7fktyopaldenqcopvzvqixswwphrw62v2op46rz2yjc3rgxlhgga"');
      
      	END;
      
      	/

       

2. You may now either sync all your tenancy’s cloud functions to automatically create corresponding UDFs in your catalog with SYNC_FUNCTION.
    

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

   
   Or you may take control and manually create a User Defined Function that invokes your cloud function, placed in your function catalog. This is your UDF’s definition, which defines the name, parameters, return type, and business logic of your UDF. You may create multiple functions in your catalog. Here is an example to manually create a UDF named “demo_function” whose “function_id” can point to to the OCID of the OCI Function you have deployed previously.
    

   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    => 'demo_function',
   
   	        function_id      => 'ocid1.fnfunc.oc1.phx.aaabbbccccdddd',
   
   	        input_args       => :function_args);
   
   	END;
   
   	/

    

Invoke User Defined Functions in your SQL query

Now that we have created our catalog and synced the user defined functions above, we may list all of the available UDFs in the catalog with the following: 

 

VAR cloud_params CLOB;

VAR function_list CLOB;

BEGIN

    DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (

    credential_name  => 'OCI_CRED', 

    catalog_name     => 'OCI_DEMO_CATALOG',

       service_provider => 'OCI'

    function_list    => :function_list);

 );

END;

/

SELECT JSON_QUERY(:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual;

Finally, we may invoke any of our User Defined Functions right in a SQL query, which here simply invokes the demo cloud function you pointed to and displays its output response!

 

SELECT demo_function FROM dual;

In Conclusion

In this blog post, we’ve explored how to use User Defined Functions to invoke cloud functions in OCI and really supercharge your application’s capabilities in SQL! To see a similar example as above for AWS Lambda have a look at the documentation here. I hope you find this post useful and find many use cases for User Defined Functions in your cloud architecture.