Skip to main content

Making AI Accessible to APEX

· 6 min read
Adrian Png

Figuring out sentiments.

The Oracle Cloud Infrastructure (OCI) is a great platform in many ways. However, as much as I love it, I have long griped about the lack of out-of-the-box support for AI services that many of Oracle's competitors have. Well, as of May 6, 2021, I am glad to see that things are starting to turn around with the launch of the OCI Language service.

Today, there are different ways to "AI-enable" an Oracle Application Express (APEX). For a long time now, Oracle has had several PL/SQL packages under the Oracle Enterprise Database's Machine Learning option, or what used to be known as Oracle Advanced Analytics. This option is in fact, no longer an option and is included in all editions of the database.

Specifically, Oracle Machine Learning for SQL (OML4SQL) allows the PL/SQL developer to train and apply ML models without the data leaving the database. It supports a variety of ML algorithms, and generally, does not require developers to have advanced theoretical knowledge of ML. And because it relies only on PL/SQL and SQL, it can be easily integrated into APEX. However, developers are limited to ML algorithms that have been implemented by Oracle.

About a month ago, Oracle announced the general availability of AutoML and OML Services on the Autonomous Database (ADB). Developers who are not too ML-savvy can use the AutoML UI (user interface), available through the Oracle Machine Learning Notebooks that is included with every ADB. AutoMl automates the process of selecting, training, testing, and evaluating ML models. The generated models can then be exposed through OML Services' REST APIs.

However, sometimes, all we need is a ready-to-use AI/ML model that performs a rather generic task. Very common these days is the need to perform Sentiment Analysis on a body of text. Microsoft Azure's Cognitive Services has Text Analytics and AWS has Amazon Comprehend. What does Oracle have? At least now we can say, "OCI Language Services." It took a while, but better late than never!

Identity and Policy

As a best practice, we should create an OCI user specifically for interacting with the OCI REST API on behalf of the APEX application. The user should have limited OCI capabilities and should only be allowed to have API access and nothing more. You will need to generate the API keys and can easily do this from the user's details page in the OCI console.

Generate and download the API key pair from OCI console.

You should also download the keys in PEM format. Also, gather the information (click the View Configuration File link on the user's details page) that you will need to setup the APEX Web Credentials for OCI, specifically:

  • User OCID
  • Tenant OCID; and
  • The API key's fingerprint

Gather the OCI information that you will need.

Open the private key that you had downloaded from the OCI console and remove (a) the enclosing tags -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY-----; and (b) all newline characters. The end result should be a very long string of characters. This is the OCI Private Key that you will need to provide in the APEX Web Credentials. Copy the text, but do not save the PEM file.

Create the Web Credentials with the required information about your user and tenancy.

Setup APEX Web Credentials.

Next, create an IAM Group, e.g., AiLanguageUsers, and assign the user to the group.

To use the Language Services, we will need to create a policy to allow users access. We have the option of creating a blanket policy with the following statement:

allow any-user to use ai-service-language-family in tenancy


allow group AiLanguageUsers to use ai-service-language-family in tenancy

I recommend the latter.

Create a policy to allow a group access to the Language Services.

Demo Application

Create a simple APEX application to demonstrate the utility of the Language Service through its REST API.

The key items are:

  • Two textarea page items
  • A button that triggers a dynamic action
  • A dynamic action that sets one item using a PL/SQL function with the code below:
c_base_url constant varchar2(59) := '';
c_endpoint_path constant varchar2(50) := '/20210101/actions/detectLanguageSentiments';
c_web_credential constant varchar2(30) := 'DG_ADBAGENT01';

l_response clob;
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';

l_response := apex_web_service.make_rest_request(
p_url => c_base_url || c_endpoint_path
, p_http_method => 'POST'
, p_body => '{ "text": "' || apex_escape.json(:P1_INPUT) || '" }'
, p_credential_static_id => c_web_credential

return l_response;

This DA simply takes the input text and makes a POST request to the appropriate endpoint for performing sentiment analysis. The expected output is a JSON containing the model's prediction.

We made it!

Unfortunately, it doesn't seem to work right now and I'm in touch with Carsten to figure this out.

[TODO] I will update the post as soon as there's a positive outcome.

[UPDATE, 2021-05-10] A noob error! I should have checked the debugger logs. As it turns out, I was missing the HTTP header Content-Type. All systems go! The above code snippet and screenshot have been updated to reflect the success! Special thanks to Carsten who helped validate that this REST call is supported and should have been successful.

Meanwhile, I've tried looking at the OCI PL/SQL SDK that I had discussed in the previous post, but it doesn't appear to have been updated on my ADB.

[TODO] I will also update this post as that becomes available.

What's working though is the OCI CLI, but you will need to upgrade it to access the new commands. Here's an example and the output that we should have obtained through the example APEX application.

$ oci ai language detect-sentiments --text "I feel great with the launch of OCI AI Language Services!"
"data": {
"aspects": [
"length": 6,
"offset": 22,
"scores": {
"Negative": 0.0004437448224052787,
"Neutral": 0.0,
"Positive": 0.9995562434196472
"sentiment": "Positive",
"text": "launch"
"length": 24,
"offset": 32,
"scores": {
"Negative": 0.00011140753485960886,
"Neutral": 0.0,
"Positive": 0.99988853931427
"sentiment": "Positive",
"text": "OCI AI Language Services"

Exciting times! As I have repeatedly said, the OCI keeps getting better. I'm certainly looking forward to resolving the issues I have encountered, and hopefully, more AI services to be launched in the near future!


Photo by Markus Winkler on Unsplash