Oracle APEX and OCI Generative AI: Step-by-Step Setup

Oracle APEX can now call large language models with very little code. Paired with OCI Generative AI, an APEX app can summarise records, draft text, answer questions and search your own documents, while data stays within Oracle Cloud Infrastructure.

This guide walks through the setup Egisedge uses on APEX + AI projects, based on our APEX and OCI Generative AI playbook. It assumes a working knowledge of APEX and basic OCI administration. Screens and options change between APEX releases, so check the Oracle APEX documentation for the release you are on.

What you are building

There are three common patterns:

Start with the simplest pattern that meets the need. RAG adds value when answers must come from your own documents.

Prerequisites

• An OCI tenancy with access to OCI Generative AI in a supported region. Model availability differs by region; see the OCI Generative AI documentation.
• An APEX workspace on Autonomous Database, the APEX Application Development service, or another supported database that can reach OCI over HTTPS.
• A recent APEX release with Generative AI services in Workspace Utilities.
• Permission to create IAM policies, users or dynamic groups in the tenancy.

Setup steps

1. Provision the database and APEX workspace

Create the Autonomous Database or APEX service instance and a workspace for the application. Keep AI work in a separate compartment if your organisation uses compartments to control cost and access.

2. Enable access to OCI Generative AI

Pick a region where the models you want are offered. Create an IAM policy that allows the identity APEX will use to call the service. For a user-based setup, this is typically a policy that allows a group to use the Generative AI family of resources in a compartment. Keep the policy scoped to the compartment you need, not the whole tenancy.

3. Create the credential

There are two common options:

1. API key (OCI Native Authentication). Create a dedicated OCI user for the application, add it to the group in your policy and generate an API signing key. In APEX, create a web credential of type OCI Native Authentication with the user OCID, tenancy OCID, fingerprint and private key.
2. Resource principal (Autonomous Database). Enable the resource principal for the database and write the IAM policy against it. This avoids storing a private key.

Use a dedicated, least-privilege identity either way. Never reuse a personal administrator key.

4. Configure the Generative AI service in APEX

In Workspace Utilities, create a Generative AI service. Choose OCI Generative AI as the provider, select the credential, and set the compartment and model. Use the test option to confirm the connection before building anything on top of it.

5. Add AI features to the app

With the service in place you have two main routes:

• Declarative: add the AI Assistant or text generation features available as dynamic actions in page designer, with a system prompt that sets the role, tone and limits.
• PL/SQL: call the model with the APEX_AI package (for example to generate a summary of a record) or with APEX_WEB_SERVICE for direct REST calls when you need full control of the request.

Keep system prompts in a table rather than hard-coded, so they can be versioned and reviewed.

6. Add retrieval (RAG) with AI Vector Search

For answers grounded in your own content:

1. Split documents into chunks of a sensible size, keeping titles and section references.
2. Generate embeddings with an embedding model and store them in a VECTOR column, using Oracle AI Vector Search in Database 23ai.
3. At question time, embed the question and fetch the closest chunks with VECTOR_DISTANCE, filtered by what the user is allowed to see.
4. Pass the retrieved chunks to the model with an instruction to answer only from them and to cite the source.
5. Show the sources in the UI so users can check the answer.

Apply APEX authorisation and row-level rules to the retrieval query. The model must never see content the user could not open directly.

7. Log, limit and monitor

Before go-live:

• Log each prompt, retrieved sources, response, user and timestamp to a table.
• Add rate limits per user and per session to control cost.
• Add a fallback message for when the service is unavailable.
• Mask personal data in prompts where it is not needed.

Security and governance checklist

• IAM policy scoped to a compartment, not the tenancy
• Dedicated, least-privilege identity; no personal keys
• Web credential restricted to the OCI endpoint URL
• System prompts stored, versioned and reviewed
• Retrieval query respects APEX authorisation and data security
• Prompt-injection tests run (users trying to override instructions)
• Prompts and responses logged for audit
• Rate limits and cost alerts in place
• Human review for any output that posts to finance or HR data

Our responsible AI checklist covers the governance side in more detail.

Testing the feature

Treat the AI feature like any other code, with one addition: build a small golden set of real questions and expected answers. Run it whenever you change the prompt, the model or the retrieval logic, and track accuracy and groundedness. The AI test and evaluation plan gives a template.

Common pitfalls

• Region mismatch. The model you want may not be offered where your database sits. Check early.
• Over-long prompts. Sending whole documents raises cost and lowers quality. Retrieve less, retrieve better.
• No source display. Users trust answers they can check.
• Hard-coded prompts. They cannot be reviewed or rolled back.

How Egisedge helps

Egisedge Technologies is an Oracle Partner building Oracle APEX + AI low-code applications, OCI solutions and AI agents. We built the Instafinz Consolidation & Narration product with Python and APEX, and our AI Financial Compliance Reporting platform uses AI narration with a pluggable LLM. Our R&D centre in Mahendergarh, Haryana, works on AI products alongside client delivery.

Want to add generative AI to an APEX app, or start a new one? Email info@egisedge.com or book a demo with our team.

Frequently asked questions