I recently blogged a comprehensive guide to invoking serverless functions in the Oracle Cloud. I was quite sure that I had included every possible method for invoking them when I published that article, but a few days later it dawned on me that I had missed one. Granted, it may be more of an edge case than a common way you might want to invoke your serverless function, but I want to highlight this method here in this blog post because this method can be used to invoke additional REST APIs (some of which might come as a surprise to you).
Note! Your credentials are stored in an encrypted format in the database.
Before we can make our call to the OCI REST API to invoke our serverless function, we must first store our credentials that will be used to authenticate our REST call. To do this, we'll use the
DBMS_CLOUD.CREATE_CREDENTIALS function. We only have to do this once and to use the credentials later on we'll refer to the
credential_name which the script will use to securely retrieve and decrypt our stored credentials.
Not Setup Yet? The credentials we're using here are the same credentials that you use on your local machine for the OCI CLI. If you haven't yet set up the OCI CLI, see the instructions here. The
DBMS_CLOUD.CREATE_CREDENTIALS function does not support PEM keys with passphrases.
To create our credentials, we can run the following:
private_key value above is just the key content without any of the header/footer (without any line wraps). The rest of the data is as it would be in your
~/.oci/config file. If for some reason you need to delete a credential, you can do so by name:
In order to make our REST call from our script, we'll utilize the
DBMS_CLOUD.SEND_REQUEST function. This would look like so:
SEND_REQUEST function expects a
credential_name, the API URI, a HTTP request method and (optionally) a
body parameter (both of which are expected to be a JSON_OBJECT) and returns an object of type
RESP object contains the response headers, text, and status code. The
DMBS_CLOUD package contains various functions to help us retrieve the values from the
RESP object. We'll use
DBMS_CLOUD.GET_RESPONSE to get the response which will be a JSON string. For convenience, let's wrap this up in a function.
This serverless function is a simple "hello world" that expects a JSON object containing a single key - the name of the person to say hello to. We can call our SQL function like so:
Which gives us the JSON response from the serverless invocation.
Having the JSON string is nice, but what if we wanted to do something further with the response. Say we wanted to join the data returned to us with another table or return the data as a resultset? We can take advantage of the
JSON_TABLE function to help us out with that.
Which gives us:
Now that we've seen how to invoke a serverless function with
SEND_REQUEST, it's easy to see that this method can be used to call any REST endpoint in the Oracle Cloud.
Here's an example of getting a list of compute instances in a given compartment.
Called like so:
As I mentioned above, this method can be used to invoke literally any OCI REST API.
Yes, you read that right. Not only can you use the
DBMS_CLOUD package to store OCI credentials and invoke OCI REST APIs, but you can also use it to store AWS credentials and invoke AWS REST APIs. There are a lot of clients who utilize a "multi-cloud" strategy, so it makes perfect sense to support invoking REST APIs for some of the more popular cloud providers in the industry. The process is mostly identical to the method we used above, with some slight but notable differences.
The first difference is the way we create our credentials for AWS. This is outlined in the documentation, but the key here is that there is an overloaded version of
CREATE_CREDENTIAL that accepts a
password. For AWS, the username is your access key ID and the password is your secret access key (refer to the AWS documentation if you are not familiar with these two items). This looks something like so:
The function that we create follows the same format as above, but points at the appropriate AWS REST endpoint. Here we have a function to list all of the S3 buckets for an account:
Which we can call like so:
Since Amazon thinks we are still in 1999, we can only receive XML back from the S3 REST API.
But that's perfectly OK because we can use
XMLTABLE to work with XML:
Which gives us:
We're not done yet! We can also work with Azure to list "containers" (buckets).
For Azure, the username is your Azure storage account name and the password is an Azure storage account access key.
The proof that Microsoft also believes we are still stuck in the previous millennium:
The query to parse the XML into a usable format:
In this post, we looked at how to request REST results for an OCI tenancy with DBMS_CLOUD.SEND_REQUEST in your PL/SQL scripts. We also saw how that method can be utilized to call the REST APIs for your external cloud providers such as Microsoft Azure and Amazon Web Services.
I've written many blog posts about connecting to an Autonomous DB instance in the past. Best practices evolve as tools, services, and frameworks become...
In my last post, we looked at the technical aspects of my Brain to the Cloud project including much of the code that was used to collect and analyze the...
In my last post, we went over the inspiration, objectives, and architecture for my Brain to the Cloud project. In this post, we'll look in-depth at the...