X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

  • February 19, 2020

How to level up and invoke an Oracle Function (or any Cloud REST API) from within your Autonomous Database

Nilay Panchal
Product Manager

 

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.

 

1)  Create and deploy an Oracle Function

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.

 

 

2) Invoke your Oracle Function using DBMS_CLOUD

 

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.

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;
  BEGIN
    --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) ||
  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 "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:

  • 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!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.