Building Innovative Q&A Experiences: Oracle APEX Meets OCI Generative AI

From precisely summarizing intricate data to crafting context-aware responses, Generative AI stands at the forefront of a technological shift, promising to redefine how we navigate and leverage knowledge in our day-to-day interactions. With the introduction of the native Generative AI Service on Oracle Cloud Infrastructure (OCI), enabling your applications with generative AI capabilities has never been easier.

In this blog post, you will learn how you can bring generative AI capabilities based on large language models (LLMs) to your applications built using Oracle APEX, your favourite low-code Platform. The Generative AI service can be accessed through REST APIs, and by using the powerful REST Data Source capabilities of APEX, you can effortlessly incorporate this advanced technology into your applications with a low-code approach.

NOTE: The OCI Generative AI service is currently under Limited Availability. You can try this service by registering for the Beta Program.
The OCI Generative AI Service is generally available. Read the OCI Generative AI blog to learn more. 

What is OCI Generative AI service?

Oracle Cloud Infrastructure Generative AI is a fully managed service that provides a set of state-of-the-art, customizable large language models (LLMs) that cover a wide range of use cases for text generation. Generative AI currently supports the following pre-trained foundational models available from Meta and Cohere:

1. Llama 2
2. Command
3. Summarize
4. Embed

You can read more about these models from the documentation.

This blog will guide you through the process of utilizing the generation models within the Generative AI Service to develop an “Ask Questions” feature for the New York High Schools APEX application. This functionality empowers parents to inquire about school facilities, policies, and more, aiding them in making an informed decision about whether a specific school is the ideal fit for their child.

Here’s a sneak peek into how the final application powered by Generative AI looks like:

You can download the demo app and cookbook here: NYC_Schools_GenAI_Demo.zip

Prerequisites for integrating OCI Generative AI into APEX

1. An OCI account.
2. An OCI compartment. You can use the pre-configured tenancy root compartment or create your own.
3. The logged-in user should have the necessary privileges to access Generative AI resources. You can configure these privileges with Identity and Access Management (IAM) policies. 
   You can either give permissions to use individual Generative AI resource types or use the aggregate resource type, generative-ai-family. See Generative AI documentation details.

Adding API Keys on OCI

In Oracle Cloud Infrastructure (OCI), API keys are used for secure authentication when accessing OCI resources through the API. OCI API keys consist of two parts: a public key and a private key.

You can use the Console to generate the private/public key pair for you. If you already have a key pair, you can choose to upload the public key. When you use the Console to add the key pair, the Console also generates a configuration file preview snippet for you.

The configuration file preview snippet is generated with the following information:

• user – the OCID of the user for whom the key pair is being added.
• fingerprint – the fingerprint of the key that was just added.
• tenancy – your tenancy’s OCID.
• region – the currently selected region in the Console.
• key_file – the path to your downloaded private key file. You must update this value to the path on your file system where you saved the private key file.

Click here to learn how To generate an API signing key pair.

Creating Web Credentials in Oracle APEX

To integrate Generative AI with APEX, we create Web Credentials in APEX. Web Credentials securely store and encrypt authentication credentials for connecting to external REST services or REST Enabled SQL services.

These credentials are stored at the workspace level, making them accessible to all applications. We use these credentials to interact with Generative AI APIs.

Learn how to create Web Credentials by clicking here.

Understanding the Generative AI REST API

Generative AI on Oracle Cloud Infrastructure (OCI) offers a diverse range of functionalities, including generating text based on prompts, summarizing text based on the input,  producing embeddings (low-level numerical representation) etc. 

Currently, Generative AI Service is only available in the Chicago OCI Region. However, your APEX instance need not be in the same OCI region.

So the supported API End point is :  

https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/

In this blog post, you will learn how to use the GenerateText endpoint.

POST /20231130/actions/generateText 

The combined API Endpoint will be :

https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/generateText

The request body for this POST request is a JSON Object as shown below:

{
  "compartmentId": "<compartment_id>",
  "servingMode": {
    "modelId": "cohere.command",
    "servingType": "ON_DEMAND"
  },
  "inferenceRequest": {
    "prompt": "Tell me something about the Earth",
    "maxTokens": 300,
    "temperature": 1,
    "frequencyPenalty": 0,
    "presencePenalty": 0,
    "topP": 0.75,
    "topK": 0,
    "returnLikelihoods": "GENERATION",
    "isStream": false,
    "stopSequences": [],
    "runtimeType": "COHERE"
  }
}

Here, CompartmentId is the OCID of your Compartment. prompt, inside the inferenceRequest object, is a string holding the text inputs against which the service would generate text output. In the above example, as well as in this blog post, the pre-trained model, cohere.command is used. This information is provided through the servingMode object. 

The exhaustive list of available attributes in the request body can be found in the documentation.

Configuring the Generative AI as REST Data Source

Now that you understand the specification of Generative AI REST API, you can go ahead and use this REST API in Oracle APEX by creating a REST Data Source for this endpoint.

The REST Data Source will be set up with the following parameters:

REST Data Source Type: Oracle Cloud Infrastructure (OCI)
URL Endpoint: https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/generateText

Authentication: We use the Web Credentials created above.

The request to the Generative AI service generateText is an HTTP POST request. So, update the POST operation with the below settings:

Compose the Request Body Template as shown below.

{
  "compartmentId": "Your compartment id",
  "servingMode": {
    "modelId": "cohere.command",
    "servingType": "ON_DEMAND"
  },
  "inferenceRequest": {
    "prompt": "#PROMPT#",
    "maxTokens": 300,
    "temperature": 1,
    "frequencyPenalty": 0,
    "presencePenalty": 0,
    "topP": 0.75,
    "topK": 0,
    "returnLikelihoods": "GENERATION",
    "isStream": false,
    "stopSequences": [],
    "runtimeType": "COHERE"
  }
}

Pass your OCI Compartment ID for the compartmentId attribute. #PROMPT# is a substitution string for the prompt input that gets passed to the Generative AI service.

For the Operation parameters, use the Synchronize with Body button to get all the substitutions defined in the Request Body Template as parameter

Additionally, Create two Operation parameters:

• RESPONSE (Response Body) – to hold the JSON response from the POST request
• Content-Type (HTTP header) – It can be a static value defaulted to application/json
   

Preparing the Prompt – Prompt Engineering

The Generative AI Service cannot retrieve school-related information from the database when provided with a user prompt alone. In order to generate an accurate response, the REST call must explicitly include both the context and the user’s question by combining them into a single prompt. The Generative AI Service relies on this contextual information to provide relevant answers.

On Page Rendering, the Prompt Context is compiled and stored into a Page Item – P6_PROMPT_CONTEXT. 

SELECT
    'Overview of the school : '|| OVERVIEW_PARAGRAPH ||chr(10) || chr(13)||
    'The following Language Courses are taught here : '||LANGUAGE_CLASSES||chr(10) || chr(13)||
    'The following Advanced Placement Courses are taught : '||ADVANCEDPLACEMENT_COURSES||chr(10) || chr(13)||
    'The following is the Diversity in Admimission Policy for this school: '||diadetails||chr(10) || chr(13)||
    'The below extra curricular activities are available : '|| extracurricular_activities|| chr(10) ||chr(13)||
    'The below are Public Schools Athletic League (PSAL) sports for boys: '||PSAL_SPORTS_BOYS || chr(10) ||chr(13)||
    'The below are Public Schools Athletic League (PSAL) sports for girls : '||PSAL_SPORTS_GIRLS || chr(10) ||chr(13)||
    'Other facilities in this school : '||addtl_info1 || chr(10) ||chr(13)||
    'The following academic oppurtunities are available : '||academic_opportunities || chr(10)||chr(13)
    as prompt_context
FROM high_schools WHERE id = :P6_ID;

:P6_FINAL_PROMPT := apex_string.join_clobs(
                        apex_t_clob( '<CONTEXT>',
                                     :P6_PROMPT_CONTEXT||chr(10),
                                     '<QUESTION> Based on the context above answer the following question: '||:P6_PROMPT||CHR(10),
                                     'If this question cannot be answered based on above context say - "Information not found!"')
                     );

{
  "modelId": "cohere.command",
  "modelVersion": "15.6",
  "inferenceResponse": {
    "runtimeType": "COHERE",
    "generatedTexts": [
      {
        "id": "55043b85-2f48-4737-be63-955e2c98ff81",
        "text": " Based on the given context, the advanced placement courses taught at the Young Women's Leadership School of East Harlem are AP Spanish Literature and Culture, AP Biology, AP Calculus AB, AP United States History, AP Statistics, and AP English Literature and Composition. \n\nWould you like to know more about any of these courses? ",
        "likelihood": -0.025589365512132645,
        "tokenLikelihoods": [
          {
            "token": " Based",
            "likelihood": -0.3324306
          },
          {
            "token": " on",
            "likelihood": -0.0000019765537
          }, {...}
        ]
      }
    ],
    "timeCreated": "2024-01-26T08:09:26.301Z"
  }
}

INSERT INTO PROMPTS (SESSION_ID, SCHOOL_ID, PROMPT, RESPONSE, ASKED_ON) 
VALUES (:APP_SESSION, :P6_ID, :P6_PROMPT, (SELECT LLM_OUTPUT
        FROM
            JSON_TABLE ( :P6_RESPONSE, '$.inferenceResponse.generatedTexts[0]'
                COLUMNS
                    LLM_OUTPUT clob PATH '$.text'
            ) 
), systimestamp) RETURNING id into :P6_RECENT_PROMPT_ID;