Invoke OCI Functions from Autonomous DB and send mails.

This is a 2 part blog.

Part1 will discuss how we can configure OCI Functions to send Gmail.

The Part2 will discuss how you can invoke the OCI function from Oracle Autonomous Database.

OCI Functions is a serverless platform that allows developers to build and run code without having to worry about managing the infrastructure. In this blog we will call the OCI function and send email from ADB.

we will use the new DBMS_CLOUD functionality in ADB to invoke the function we just deployed. Open up SQL Developer Web from ADB instance Service Console and run the following scripts as directed. If you don't yet have an ADB instance and need a guide on how to set one up click here.

Create a user credential that is required for authentication to call OCI APIs by filling in your user_ocid, tenancy_ocid, private_key and fingerprint. Click here if you are unsure where to find this information. BEGIN DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'OCI_KEY_CRED', user_ocid => 'ocid1.user.oc1..aaaaaaaam2...', tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaakc...', private_key => 'MIIEogIBAAKCAQEAtU...', fingerprint => 'f2:db:d9:18:a4:aa:fc:83:f4:f6..'); END; / And finally, we use the SEND_REQUEST procedure to invoke the deployed function using the function endpoint. You may identify your function's invoke endpoint using this CLI command or simply copying it from your Oracle Cloud UI under Developer Services Replace the uri parameter below with your Function's invoke endpoint and, if you like, your own custom name in the body parameter. SET SERVEROUTPUT ON DECLARE resp DBMS_CLOUD_TYPES.resp; payload clob; BEGIN --HTTP POST Request resp := DBMS_CLOUD.send_request( credential_name => 'OCI_FUNCTIONS', uri => 'https://fuukiqbujgq.us-ashburn-function.. ..actions/invoke', method => DBMS_CLOUD.METHOD_POST, body => UTL_RAW.cast_to_raw('{ "sender-email":"manly.hogan@gmail.com", "sender-name":"Test", "recipient":"vijay.balebail@oracle.com","subject":"Hello subject", "body":"This is a test email" }') ); -- Response Body in TEXT format DBMS_OUTPUT.put_line('Body: ' || '------------' || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10)); -- Response Headers in JSON format DBMS_OUTPUT.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10)); -- Response Status Code DBMS_OUTPUT.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_status_code(resp)); END; /

If all went according to plan, you should see a 200 OK Response Code and the response text that the email was sent. Note: While invoking Oracle Functions, use the troubleshooting guide to help resolve issues. In addition to that, here are two places I stumbled so you won't have to: If you are still seeing access errors after already uploading your public key to your User, or creating the policies to give Functions access to your user, you may want to wait a few minutes. It sometimes may take a little while for the changes to propagate. If you are seeing a 502 error and are using a public subnet in your VCN, you may need to create an internet gateway and set up its routing table (you can use CIDR 0.0.0.0/0 as default) to give public internet access to your gateway. Click here for more information about networking or to use the simplified Virtual Networking Wizard. While this is a simple example to walk you through the necessary steps, Oracle Functions is an extremely powerful service and the ability to call Functions from the database expands Autonomous Database functionality to essentially any custom functionality you desire. You may of course also use the SEND_REQUEST procedure to call any other Cloud Service REST API, such as object storage operations, instance scaling operations, Oracle Streams, and many more!