Implementing Oracle APEX AI Assistant Using OCI Generative AI - Step-by-Step Guide

 Introduction

Oracle APEX provides built-in integration with Generative AI services such as OCI Generative AI, OpenAI, and Cohere. By configuring the OCI Generative AI service, developers can use the APEX AI Assistant to generate SQL queries, PL/SQL code, and application logic directly inside the development environment.

In this article, we will configure OCI Generative AI in Oracle APEX and use the AI Assistant to generate SQL and PL/SQL code.

The implementation includes:

  • Configuring OCI API Keys

  • Creating Web Credentials

  • Configuring OCI Generative AI Service

  • Testing the AI connection

  • Using APEX AI Assistant

  • Generating SQL / PL SQL using natural language

Prerequisites

Before starting, ensure the following requirements are completed.

RequirementDescription
OCI AccountOracle Cloud Infrastructure account
Oracle APEXAPEX workspace created
OCI Generative AIService enabled
OCI API KeyRequired for authentication

Step by Step Approaches

Step 1 - Generate OCI API Keys

Start directly with OCI API Key creation because it is required for authentication.

Explain:

  1. Login to OCI Console

  2. Navigate to Profile → API Keys

  3. Click Add API Key

  4. Select Generate API Key Pair

  5. Download Private Key

  6. Copy:

    • User OCID

    • Tenancy OCID

    • Fingerprint

    • Region

Step 2 - Create Web Credentials in APEX

Navigation:

Workspace Utilities
→ Web Credentials
→ Create

Configure OCI Native Authentication and enter:

  • OCI User ID

  • Tenancy ID

  • Private Key

  • Fingerprint

Step 3 - Configure OCI Generative AI Service

Navigation:

Workspace Utilities
→ Generative AI
→ Create

Enter:

  • AI Provider → OCI Generative AI Service

  • Compartment ID

  • Region

  • Model ID

  • Base URL

  • Credential

Then Test Connection. If successful, click 'Create'.

Step 4 - Creating a Custom Data Model Using AI 

Navigation: SQL Workshop → Utilities → Create Data Model Using AI 

1.  APEX Assistant creates Tables, Triggers, and Constraints in Oracle SQL or Quick SQL format. 

2.  A valid Generative AI configuration in Workspace Utilities is required. 

3.  Output can be in Quick SQL or Oracle SQL format. 

Step 5 - Creating an App from a Prompt

1.  APEX 24.1 allows generation of an application blueprint from a natural language prompt. 

2.  APEX Assistant leverages Dictionary Cache to identify tables and suggest pages. 

3.  The generated blueprint can be enhanced with additional natural language instructions. 

Navigation: App Builder → Create → Create App Using Generative AI → APEX Assistant



After Created Application.

Step 6 - APEX Assistant in Code Editors 

1.  Leverages Generative AI Services configured in Workspace Utilities. 

2.  Uses APEX Dictionary Cache to identify relevant tables. 

3.  Generates SQL Queries for Form and Report regions using Query Builder mode. 

4.  Generates PL/SQL, JavaScript, HTML, and CSS code using General Assistance mode. 

5.  Can improve, format, and add comments to code based on user prompts. 

6.  Comes with two quick actions: Improve Code and Explain Code.  

APEX AI Assistant is also available in the PL/SQL Code Editor.

Open:

Page Designer
→ Process
→ PL/SQL Code Editor

Click

APEX Assistant

Example prompt:

Insert sample employee record

Generated code:

declare
 v_employee_id number := 1;
 v_first_name varchar2(50) := 'John';
 v_last_name varchar2(50) := 'Doe';
 v_email varchar2(100) := 'john.doe@example.com';
 v_hire_date date := sysdate;
 v_salary number := 60000;
begin

 insert into employees (
   employee_id,
   first_name,
   last_name,
   email,
   joining_date
 )
 values (
   v_employee_id,
   v_first_name,
   v_last_name,
   v_email,
   v_hire_date
 );

 commit;

exception
 when others then
   dbms_output.put_line('Error: ' || sqlcode || ' - ' || sqlerrm);
   rollback;
end;

This helps developers generate code quickly.

Step 7 - Show AI Assistant Dynamic Action

The Show AI Assistant is a Native Dynamic Action in Oracle APEX that opens an AI chat UI. It leverages Generative AI Services and can be rendered inline or as a Dialog. 

1. Selecting 'Show AI Assistant' as the True Action 

In the Dynamic Actions editor, after creating a New True Action, open the Action dropdown. Under the AI category, select 'Show AI Assistant'. Other AI option available is 'Generate Text With AI'.


 2. Setting Up the AI Chat Region and Dynamic Action 

In Page Designer, create a Dynamic Action and select 'Show AI Assistant' as the True Action. The AI_CHAT region is placed in the page layout connected to the action.  


3. Configuring the Action Panel 

Configure Service, System Prompt, Welcome Message, Display As (Dialog or Inline), and Quick Actions in the right-side Action panel. The Service dropdown offers: Application Default, OCI Gen AI, or Open AI. 


4. Writing a System Prompt 

Click the System Prompt code editor icon to open the Code Editor. The panel shows the empty System Prompt and Welcome Message fields ready for configuration. 


 5. Domain-Specific System Prompt Example 

The example below restricts the AI Assistant to answer only Oracle APEX-related questions. The System Prompt field on the right panel shows 'Reply only in Japanese' as an additional constraint.

Step 8 - Generate Text with AI

1.  Generates a one-time response based on user input. 

2.  Leverages Generative AI Services or centralized AI Configurations. 

3.  Supports configurable System Prompt, Input Value, and Use Response attributes. 

Navigation 

1.  Create a Button → Create a Dynamic Action → Set True Action Type: Generate Text with AI. 

Generative AI Panel 
  • Configuration → AI Service. Service → OCI Gen AI.
  • Input Value Type → Item. Use Response Type → Item. 

Step 9 - Create and Use AI Configurations 

1.  AI Configurations are managed from Shared Components. 

2.  They act as centralized keys for Generative AI settings. 

3.  Includes: System Prompt, Welcome Message, and RAG Sources. 

Navigation: Shared Components → Generative AI → AI Configurations → Create 

Example System Prompt 

Use the information provided about the school to answer all questions. 

If the question cannot be answered, say 'Information not found'. 

Example Welcome Message 

Welcome! How may I help you? 

Step 10 - AI Features Available in Oracle APEX

Once configured, AI can be used in multiple areas.

FeatureDescription
AI AssistantChat-based coding assistant
Text to SQLConvert natural language to SQL
Code GenerationGenerate PL/SQL, JS, HTML
Code ExplanationExplain existing code
Code ImprovementOptimize code automatically

These capabilities significantly increase developer productivity.

Conclusion

Oracle APEX integrates seamlessly with OCI Generative AI, enabling developers to build smarter applications and generate code using natural language prompts.

By configuring:

  • OCI API Keys

  • Web Credentials

  • Generative AI Service

you can activate APEX AI Assistant and leverage AI directly inside the development environment.

This makes Oracle APEX a powerful platform for AI-assisted low-code development.

Comments

Post a Comment

Popular posts from this blog

Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL

Image
📘 Introduction Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS). In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing. In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication. 🎯 Objective The goal of this implementation is to: • Expose database data through REST APIs • Create structured API endpoints • Secure APIs using OAuth • Enable controlled external access 🧱 Prerequisites Before starting, ensure: • Oracle APEX 24.2 is installed • ORDS 20.x or later is configured • RESTful Services are enabled • A working table exists (Example: HR.EMPLOYEES) 🌐 REST API URL Structure The generated API follows this format: https://host:port/ords/schema/module/template ⚙️ Implementat...
Read more

Building a Portfolio Generator in Oracle APEX: A Step-by-Step Guide to Generate Downloadable Portfolio Documents

Image
  Introduction This tutorial demonstrates how to build a Portfolio Generator application in Oracle APEX where users can enter resume details and generate a downloadable portfolio HTML/ PDF with one click . The application collects user details, work experience, skills, and education, then dynamically generates a styled HTML portfolio that can be printed as PDF . Step- by- Step  Implementation  Guide Step 1 -  Create Database Tables First create the tables required to store resume information. We will create 5 tables . 1️⃣ Resume Master Table Stores the main profile information.   CREATE TABLE "RESUME_MASTER"     ( "RESUME_ID" NUMBER GENERATED ALWAYS AS IDENTITY, "FULL_NAME" VARCHAR2(200),  "EMAIL" VARCHAR2(200),  "PHONE" VARCHAR2(50),  "ADDRESS" VARCHAR2(500),  "DOB" DATE,  "OBJECTIVE" CLOB,  "CURRENT_COMPANY" VARCHAR2(200),  "CURRENT_ROLE" VARCHAR2(200),  "C...
Read more

Implementing WhatsApp OTP Verification in Oracle APEX Using UltraMsg API

Image
  🚀 Introduction In today’s digital applications, user verification plays a crucial role in ensuring security and trust. Traditionally, OTPs are sent via SMS — but with the widespread use of WhatsApp, sending OTPs through WhatsApp offers: Faster delivery Better user reach Improved engagement In this blog, we explore how to implement OTP verification in Oracle APEX by integrating WhatsApp messaging using an external API service. 🎯 Objective The goal of this implementation was to: ✔️ Generate OTP dynamically ✔️ Send OTP via WhatsApp ✔️ Integrate external messaging API ✔️ Enhance login/verification security ✔️ Enable real-time communication 🧩 Solution Overview This solution integrates: PL/SQL OTP generation REST API call from Oracle APEX WhatsApp messaging service This allows Oracle APEX applications to send OTP directly to users via WhatsApp instead of SMS.  🧱 Step-by-Step Approach  Step 1: Configure WhatsApp API To send OTP via WhatsApp, ...
Read more