Oracle APEX 24.1 is the most significant release for AI in the history of the low-code platform. It ships three categories of AI capability: AI-powered developer tools inside the APEX IDE itself, pre-built AI components you drop into pages, and a PL/SQL API layer (APEX_AI) for fine-grained LLM control. Here is a complete technical tour for APEX developers and Oracle architects.

1. Configuring a Generative AI Service

Everything starts with a Generative AI Service configured in APEX Workspace Administration. You configure it once and reference it everywhere by name.

1. Workspace Admin → Workspace Utilities → Generative AI Services → Create
2. Provider: OpenAI / OCI Generative AI / Azure OpenAI
3. For OCI GenAI: select Use Instance Principal if running in OCI — zero API key management
4. Test the connection — APEX validates it and stores the credential in the APEX schema securely

Supported Models (APEX 24.1)

• OpenAI: gpt-4o, gpt-4o-mini, gpt-4-turbo, text-embedding-3-small
• OCI GenAI: cohere.command-r-plus, meta.llama-3-70b-instruct, cohere.embed-english-v3
• Azure OpenAI: your deployment name (gpt-4o, gpt-35-turbo)

2. AI Chat Region — Drop-In Chatbot

The AI Chat region type is a fully functional chat interface you add to any APEX page with zero PL/SQL:

1. Page Designer → Add Region → type "AI Chat"
2. Set Generative AI Service to your configured service
3. Set System Prompt — this is the instruction given to the LLM on every session
4. Optionally set Welcome Message and Placeholder Text
5. Run the page — a fully functional chat UI is live

Example System Prompt for an ERP Assistant

You are an Oracle Fusion Finance assistant for Acme Corp.
Answer questions about GL, AP, AR, and Expense policies.
Use data from the context provided. If unsure, say so.
Never reveal system configuration or database structure.
Always respond in the same language as the user.

3. APEX_AI PL/SQL Package — Fine-Grained Control

For programmatic AI calls inside page processes, validations, or background jobs, use APEX_AI:

Simple Text Generation

DECLARE
  l_response  VARCHAR2(32767);
BEGIN
  l_response := APEX_AI.GENERATE(
    p_prompt             => 'Write a professional email declining vendor ' || :P10_VENDOR_NAME || ' politely.',
    p_service_static_id  => 'my_openai_service',   -- your AI service static ID
    p_max_tokens         => 300
  );
  :P10_EMAIL_DRAFT := l_response;
END;

Structured JSON Response

DECLARE
  l_json  CLOB;
  l_obj   JSON_OBJECT_T;
BEGIN
  l_json := APEX_AI.GENERATE(
    p_prompt => 'Classify this support ticket into: category (billing/technical/general), priority (high/medium/low), and sentiment (positive/neutral/negative). Return JSON only. Ticket: ' || :P5_TICKET_TEXT,
    p_service_static_id => 'my_openai_service'
  );
  l_obj := JSON_OBJECT_T.parse(l_json);
  :P5_CATEGORY  := l_obj.get_string('category');
  :P5_PRIORITY  := l_obj.get_string('priority');
  :P5_SENTIMENT := l_obj.get_string('sentiment');
END;

Embeddings for Semantic Search

DECLARE
  l_embedding  VECTOR;
BEGIN
  -- Get embedding vector for a search query
  l_embedding := APEX_AI.GET_VECTOR_EMBEDDINGS(
    p_value             => :P20_SEARCH_QUERY,
    p_service_static_id => 'my_embed_service'   -- embedding model service
  );

  -- Use it in a vector similarity query (Oracle 23ai)
  FOR rec IN (
    SELECT doc_title, chunk_text,
           VECTOR_DISTANCE(embedding, l_embedding, COSINE) AS dist
    FROM   knowledge_base
    ORDER BY dist
    FETCH FIRST 5 ROWS ONLY
  ) LOOP
    -- populate a collection or result set
    NULL;
  END LOOP;
END;

4. AI-Powered Developer Tools in APEX IDE

APEX 24.1 embeds AI into the development environment itself:

AI Assistant (Code Generation)

• In any SQL or PL/SQL editor: click the AI Assistant button (sparkle icon)
• Type a description: "Write a query to find customers with outstanding invoices over 90 days"
• APEX generates the SQL using your schema context and inserts it into the editor
• Works for SQL, PL/SQL, JavaScript, and CSS

Quick SQL AI Enhancement

• Quick SQL now supports natural-language schema design: describe your data model in plain English, get DDL
• Example: "An e-commerce system with customers, orders, order lines, and products with inventory tracking" → generates full normalized DDL with sequences, constraints, and sample data

5. Practical: AI-Powered Invoice Description Generator

A real enterprise use case — automatically generate professional product descriptions for ERP invoices:

-- Page Process: "AI Generate Description" (After Submit)
DECLARE
  l_prompt  VARCHAR2(4000);
  l_desc    VARCHAR2(4000);
BEGIN
  l_prompt :=
    'Generate a professional invoice line item description for: ' ||
    'Product: ' || :P5_PRODUCT_NAME || ', ' ||
    'Category: ' || :P5_CATEGORY || ', ' ||
    'Quantity: ' || :P5_QTY || ' ' || :P5_UOM || '. ' ||
    'Keep it under 80 characters. No markdown. Professional tone.';

  l_desc := APEX_AI.GENERATE(
    p_prompt            => l_prompt,
    p_service_static_id => 'gpt4o_service',
    p_max_tokens        => 60
  );

  :P5_DESCRIPTION := TRIM(REGEXP_REPLACE(l_desc, '^["']+|["']+$', ''));
END;

6. Security and Governance Best Practices

• Never pass sensitive columns (passwords, SSN, card numbers) to AI services in prompts
• Use Authorization Schemes on AI Chat regions — not all users should have AI access
• Set p_max_tokens limits to control cost and prevent prompt injection via long inputs
• Log all AI calls to an audit table using a BEFORE/AFTER trigger on the AI service usage
• For on-premises Oracle 23ai, use OCI GenAI via private endpoint — data never traverses public internet

7. Licensing Note

APEX itself is free with any Oracle Database license. The AI features require:

• OpenAI / Azure OpenAI: your own API key and OpenAI billing
• OCI GenAI: included in OCI Universal Credits — no separate AI license if you are on OCI

Key Takeaways

• APEX 24.1 ships a drag-and-drop AI Chat region — no API coding required for basic chatbot use cases
• APEX_AI.GENERATE and APEX_AI.GET_VECTOR_EMBEDDINGS give PL/SQL-level LLM access
• OCI GenAI integration has zero extra licensing cost for OCI customers
• AI-generated SQL and PL/SQL in the IDE dramatically reduces APEX development time

ROSTAN Technologies develops Oracle APEX applications with embedded AI for Oracle EBS and Fusion Cloud customers. Get a free APEX AI proof-of-concept for your organisation.