Oracle APEX Tips & Tricks Series – Part 2

What is New in Oracle APEX 24.1?

Oracle APEX version 24.1, released in June 2024, is now available, featuring several new enhancements and features. Whether you are an experienced developer or new to Oracle APEX, version 24.1 provides tools and capabilities to elevate your projects. This version has opened the door to the integration of AI in APEX, making it one of the most exciting versions yet.

Now, let us take a deep dive and experience the new features in APEX 24.1.

1. Support for Generative AI

• •APEX 24.1 supports your own personal AI Assistant, which can be easily configured from the Generative AI section under the Workspace Utilities panel.



• •Click on the “Create” button under the Generative AI section to configure the AI service for your workspace.
• •Configure your workspace by selecting one of the AI providers: Open AI, OCI (Oracle Cloud Infrastructure) Generative AI Service, or Cohere, and provide the necessary credentials to complete the configuration.
• •

  Currently, all three AI Providers are paid services. click on the ‘?’ button against the AI provider drop down values to see more details.

• •

  Here we configure our workspace with ‘Open AI’ as our AI Provider using the ‘gpt-3.5-turbo’ model.

• •

  Once Generative AI is configured in the workspace, you will find the APEX Assistant available in all code editors within the workspace providing you AI-Assisted App Development. The APEX Assistant assists you in writing complex code and queries.

• •You can also Create Applications using Generative AI, by providing relevant prompts.

Also Read – Oracle APEX Tips and Tricks Series Part 1

2. New Page Items

• APEX 24.1 brings with it two new Page Items, offering developers increased functionality and more customization options when it comes to selection type page items:
• •Select One
• •Select Many

• •The select one page item allow us to select one value among many based on list of values which can be static or from SQL queries while the select many page items allows user to select from many options.

2.1. Select One

• •Select One page item displays as an item with a list of values icon which supports the selection of one value and search filtering.
• •When the end user clicks the field, a popup window appears with a list of suggested values.
• •The Select One page item provides a more user-friendly option compared to a Select List item and supports filtering, groups, and template directives.

Settings

• •

  Maximum Values in List: Provide the maximum values to be displayed in the dropdown list. A maximum of 250 values can be displayed in the dropdown list.

• •

  Fetch on Search: Specify whether matching values are retrieved from the database as the user types on the item. It retrieves matching records from the database each time the user types a character. Otherwise, all list values are loaded on page load.

• •

  Use Cache: Specify whether search results are cached in the browser when Fetch on Search is enabled.

Search

• •

  Match Type: Provide the search criteria as either ‘Starts With’ or ‘Contains’

• •

  Case Sensitive: Toggle ‘Case Sensitive’ option if you want your search to be case sensitive.

• •

  Minimum Characters: Specify the minimum number of characters that must be entered before a search is executed.

• •

  No Data Found Message: We can specify what should be displayed in the list if no matches are found for your search.

• •

  List of Values: We need to provide the List of Values to use Select One page item.

2.2.Select Many

• •The ‘Select Many’ Page Item contains all the settings like the select one page item, but it provides an additional feature to select multiple values in a single drop down.

• •

  You can return the selected values from the dropdown list as either a Delimited List or as a JSON Array.

• •List of Values: We need to provide the List of Values to use Select Many Page Item.

3. Dynamic Actions

• •APEX 24.1 introduces several exciting new actions for dynamic actions, and we are thrilled to share the details with you.
• •

  Print Report: Use this new dynamic action to leverage the OCI Document Generation Pre-Built Function to print pixel-perfect reports.

• •This dynamic action needs a report query to function which needs to be configured from the shared component.

• •

  Download Action: This is a new dynamic action and a page process now available in 24.1. It allows you to download content directly. You can set it up to download single or multiple files as a zip archive. It supports CLOB/varchar2 datatype as file content.

• •

  Open AI Assistant: Using this action, we can open a chatbot in the application. To configure it we need to choose the Generative AI service used by the application.

4. Other Notable Updates

4.1. Enhancement in working copy: When working on the main application the APEX UI will indicate to the developer that they are working on the main application. 

4.2. Oracle AI Vector Search: Supports Vector datatypes and Operations and allows integration with Large Language Models (LLMs).

• •Now that Oracle has launched a new database version 23ai vector database, we can perform vector search using it.
• •To use vector search and generate vector embeddings we will need to do some prior configurations:
• •Get a pretrained model which is converted to ONNX Format.
• •Load the ONNX Format model into the database directory.
• •Load the model to your database from the directory. Follow the steps below to perform import of a pre-trained ONNX formatted embedding model into your Oracle Database:
• •Login to SQL*Plus as SYSDBA.
• •Grant the DB_DEVELOPER_ROLE to the user. (In our case user: CONNQ_DEV_DB)
• •GRANT DB_DEVELOPER_ROLE TO CONNQ_DEV_DB;
• •Grant CREATE MINING MODEL privilege to the user.
• •GRANT CREATE MINING MODEL TO CONNQ_DEV_DB;
• •Set up your working directory to load the ONNX embedding model.
• •CREATE DIRECTORY CONNQ_DEV_DB_BKT_23AI_DIR AS ‘<your work directory path>’;
• •Grant READ permissions on the created directory to the user.
• •GRANT READ ON DIRECTORY CONNQ_DEV_DB_BKT_23AI_DIR TO CONNQ_DEV_DB;
• •Grant WRITE permissions on the created directory to the user.
• •GRANT WRITE ON DIRECTORY CONNQ_DEV_DB_BKT_23AI_DIR TO CONNQ_DEV_DB;
• •Make sure to drop the model if it already exists before loading.
• •exec DBMS_VECTOR.DROP_ONNX_MODEL (model_name => ‘<your model name>’, force => true);
• •Now, login as the user in your Oracle Database.
• •Load the ONNX model into the Database using ‘DBMS_VECTOR.LOAD_ONNX_MODEL’. We are using Hugging-face embedding model ‘sentence-transformers/all-MiniLM-L6-v2’.

• •You can find more insights about how to load ONNX model in your database at: Link
• •

  Now that we have loaded the ONNX embedding model into our database, we will demonstrate a simple use case for Vector Search.

• •Here, we create a table named ‘phrases’ that stores the columns: phrase and phrase vector.
• •CREATE TABLE PHRASES

   (                   “ID” NUMBER,

                        “PHRASE” VARCHAR2(1000 BYTE),

                        “PHRASE_VECTOR” VECTOR

   )

• •Now, we then create an on insert and update trigger, that generates the Vector value for the Phrase. We have also created a sequence and a trigger for auto-generation of ID (Primary Key).

• •After inserting some phrases in the table, we get the vector for each of them as shown in the column: phrase_vector

Here is a Simple demo for Vector Search using Oracle APEX

• •Create an application and a page in it
• •

  Create a ‘Page Item P2_SEARCH’, a button ‘SEARCH’ and a Classic Report ‘Result’

• •On change of ‘P2_SEARCH’ create a dynamic action to set focus on the ‘SEARCH’ button.
• •On-click of the ‘SEARCH’ button create a dynamic action to Refresh the ‘Result’ Classic report.
• •Configure the Result report with the query, use the VECTOR_DISTANCE method to obtain the distance between the vectors.

• •Run the application.
• •Enter any phrase, sentence, or word in the search box
• •The Phrase with the closest vector distance will be the top-most!

• •For this blog, we demonstrated a simple use-case for Vector Search but in real-life, the Vector Search feature can offer significant benefits for complex searches.

CONCLUSION

The latest features in APEX 24.1 and Oracle Database 23AI offer developers advanced tools for creating innovative applications. With AI integration, new dynamic actions, and vector search capabilities, these updates ensure Oracle APEX continues to lead the pack in low-code application development (LCAP) market, enabling developers to create even more sophisticated and user-friendly applications.