Recently, we released functionality in the Autonomous Database Shared Infrastructure (ADB-S) to enable a user to call REST API Endpoints using simple PL/SQL scripts that run directly in the database. ADB supports REST API calls from the 3 major cloud providers, Oracle Cloud, AWS and Azure.
This feature gets rid of the need to deploy a running server someplace and then call a cloud REST API via a programmable tool such as oci-curl; instead, you simply call the DBMS_CLOUD.SEND_REQUEST procedure with familiar PL/SQL syntax and it runs straight out of your database server. This procedure makes use of the underlying UTL_HTTP database package. This also means you can invoke your REST API call script with existing in-built database features like triggers and job schedulers that your existing code is likely already using!
Below, I give an example of using the DBMS_CLOUD package to invoke an Oracle Function. Oracle Functions are fully managed, highly scalable, Functions-as-a-Service platform, available in the Oracle Cloud (OCI) and powered by the Fn Project open-source engine. An Oracle Function is intended to be a single unit of work deployed as a serverless, callable function in OCI, which is billed only for the resources consumed during the function's execution.
If you already have an Oracle Function deployed and ready to go in Oracle Cloud, jump to (2)
Before we can jump right into deploying an Oracle Function, we must perform the following steps to get set up. Since there are several steps, we will defer to the well-structured Functions documentation:
Next, we will be deploying a basic Oracle Function that accepts a parameter during invocation and returns "Hello <parameter>!" as a response. This involves registering a Docker image with the helloworld-func function code, and then deploying the function to an application in Oracle Functions.
Next, 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.
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..');
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
--HTTP POST Request
resp := DBMS_CLOUD.send_request(
credential_name => 'OCI_KEY_CRED',
uri => 'https://5pjfkzq5fhq.ca-toronto-...actions/invoke',
method => DBMS_CLOUD.METHOD_POST,
body => UTL_RAW.cast_to_raw('Nilay')
-- 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) ||
If all went according to plan, you should see a 200 OK Response Code and the response text "Hello Nilay!" (or the name you passed into the function) as in the screen below.
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:
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!