Supercharging Oracle APEX with RAG: Building an Intelligent HR Self-Service Portal

Organisations are increasingly seeking smarter ways to deliver employee services, reduce administrative overhead, and provide instant access to information. Traditional HR portals, while functional, often require staff to navigate through multiple forms, screens, and documents. By integrating Retrieval-Augmented Generation (RAG) with Oracle APEX and OCI Generative AI Agents, businesses can create intelligent HR self-service portals that allow employees to query both structured data (such as employee records) and unstructured data (such as company policy documents) in natural language.

The solution demonstrated in this blog outlines how Oracle APEX, OCI AI Services, and Generative AI Agents can be combined to deliver a chatbot that not only understands everyday employee queries but also provides accurate, context-aware responses. This approach represents a step forward in building secure, scalable, and intelligent enterprise applications.

What is RAG?

Retrieval-augmented generation (RAG) is an AI technique that combines large language models with real-time information retrieval to create more accurate and up-to-date responses. Instead of relying solely on training data, RAG systems first search through external knowledge bases (documents, databases, or web content) to find relevant information, then use this retrieved context along with the AI's inherent knowledge to generate responses. This approach significantly reduces AI hallucinations, keeps information current, and allows AI systems to work with specialised or private knowledge that wasn't part of their original training, making it essential for building reliable AI applications in business, research, and customer service where accuracy matters most.

The Challenge: Beyond Traditional Self-Service

Traditional HR self-service portals are functional but limited. Employees typically navigate through multiple screens, forms, and reports to find basic information like their leave balance, salary details, or company policies. What if employees could simply ask, "What are my remaining leave days?" or "What's the remote working policy?" and get an instant, accurate response?

That’s what the solution set out to achieve - an intelligent HR chatbot that combines the power of Oracle APEX with cutting-edge AI Agent capabilities.

Approach 1 – Native APEX Assistant

Pre-requisite: Connect a Gen AI service to APEX

However, when applied in the context of an HR chatbot, employees would naturally ask questions in the first person, which presented a challenge.

The issue is that the Assistant cannot provide context for pronouns such as “I” or “my.”  Under the hood, the Assistant uses Select AI. So, for example, when asked “What is the hire date for Lee Burgess?”, the query generated would be:

“select hire_date from employees where first_name = ‘Lee’ and surname = ‘Burgess’”

However, when asked “What is my hire date?”, the Assistant had no way of identifying the employee's name required to formulate the query.

Attempts were made to amend the system prompt to train it how to deal with this:

But it made no difference.

Another key requirement was the ability to augment the data with information from HR policy documents stored in an object bucket, and to show the citation with clickable links.

The options available to the create RAG source have a function body returning CLOB. 

There does seem to be a way to process the CLOB response, which might work once the Agent is set up and returning the data.

However, there is currently no way to pre-process prompts to correctly interpret pronouns such as “my” or “I”, nor is there functionality to return a citation with a downloadable link. As a result, a more advanced approach was required.

Approach 2 – Build a custom chatbot using a Gen AI RAG Agent

Pre-requisites:

A paid OCI tenancy in a region that supports AI Services:

• A database. (Always Free Autonomous DB was used in this demonstration).
• An APEX instance.
• Admin rights for tenancy/group policy management 
• Access to an LLM Provider via REST API

Architecture

This was the architecture for the solution:

Core Application Layer

Oracle APEX: Provides the user interface and orchestrates the entire AI integration. Users interact with the chatbot in APEX to ask questions and receive AI-powered responses.

Oracle Autonomous Database: Stores employee data, policies, and application logic. A simplified data model with test data was implemented for demonstration purposes.

OCI AI Services

AI RAG Agent: The central AI orchestrator that makes contextual decisions about how to respond to user queries. It determines whether to use SQL tools for database queries or RAG tools for document-based responses and diverts the query to the correct tool.

Config:

A basic routing instruction statement was set to inform the Agent how best to route the queries:

An endpoint was created by default, allowing access to the Agent via API.

Multiple tools can be added to an Agent. For this POC (Proof of Concept), only two were needed.

SQL Tool: An AI-powered component that generates and executes SQL queries against the database using Select AI under the hood. It translates natural language questions like "What's my salary?" into appropriate database queries and returns either the SQL Query or the executed result, depending on how it is configured.

Config:

To ensure the correct queries are formulated, the full database schema must be supplied. For this example, the DDL was exported directly from APEX and pasted here:

Next, some in-context learning examples are given to the tool. If data is available on the most common queries in the tool, it is a good idea to give as many examples as possible to improve the output. This serves as additional training for the tool: provide plain English and clear instructions, and these will be taken into account whenever a task is performed.

A database connection must be created and then selected from here:

Select this:

To have the tool execute the query and return the result, include it. To receive only the query, leave it off.

Note: Results from the SQL tool are not returned in natural language, they return JSON in this format:

{"generatedQuery":"SELECT HIRE_DATE FROM WKSP_AI.EMPLOYEES WHERE UPPER(FIRST_NAME) = 'KAREN' AND UPPER(LAST_NAME) = 'JOHNSON'","executionResult":[{"hire_date":"2015-07-20T00:00:00Z"}]}

This and the original prompt are then passed to a Large Language Model (LLM) to tidy this up and provide a natural language response.

Descriptions of tables and columns can also be passed in here to improve the tool’s understanding of the data model if it is more complex. (In this example, AI was asked to generate based on the model.):

RAG Tool: The Retrieval-Augmented Generation (RAG) tool that searches through vectorised company documents and policies to answer questions. It finds relevant information from stored documents and generates contextual, accurate responses.

Config:

The RAG tool must be linked to a knowledge base. A maximum of five knowledge bases can be connected:

(See below for more information on the Knowledge base.)

Custom instructions and example responses can be added to stop the tool from hallucinating:

Knowledge Base: A structured repository that indexes and organises company documents for efficient AI retrieval. It enables semantic search across policy documents, handbooks, and procedures.

Within the KB, at least one connected data source is needed. For this example, an object bucket containing policy documents was created:

Note: To make documents available to the RAG system, an ingestion job must be run each time documents are added or removed. The ingestion process uses an embedding model to convert unstructured document content into vector representations, which are then stored in a vector database. This vectorisation enables the knowledge base to effectively search and retrieve relevant information from the documents.

The job is triggered using the Agent Management APIs.

Alternative Data Stores: 

•  OCI OpenSearch:
  Documents must be chunked into files with fewer than 512 tokens each, and ingested and indexed in OpenSearch prior to use. See RAG Tool OCI Search with OpenSearch Guidelines for Generative AI Agents.
  
  
• Oracle AI Vector Search:
  This option is for data in Oracle Database 23ai. See RAG Tool Oracle Database Guidelines for Generative AI Agents for the required setup.

Object Storage

Cloud storage bucket containing company policy documents. Documents can be viewed, added, and removed from the APEX app using the OCI Storage Service APIs.

In the real world, an automation can be created to run the ingestion job (via API) at regular intervals. For this POC, an on-demand trigger was used. 

AI Processing

LLM (Large Language Model): The underlying AI model that provides natural language understanding and generation capabilities. It formats responses, processes (and pre-processes), prompts, and performs security analysis.

Walkthrough

SQL Path

1. In the chatbot, a user asks: ‘What was my hire date?’, or ‘Is Karen Johnson on probation?’

2. The prompt is passed (along with the user name and chat context) to an LLM, which checks for personal pronouns:

3. This amended prompt is passed (‘What is my hire date?’ is now ‘What is Lee Burgess’s hire date?’).

4. It is passed to the check_user_access_permission proc to check if the question is regarding the user/the user is an admin user (Allow) or someone else (Block).

If it is blocked at this point, a message needs to be returned to the user saying they do not have permission, etc:

If allowed, continue on…

5. The processed prompt and chat context are passed to the OCI Agent that is trained to evaluate the prompt and direct the query to the relevant tool. In this case, the SQL Tool.

6. The SQL tool then takes the natural language prompt and converts it into a SQL query using Select AI. This is then executed by the tool and returns the result in JSON format. (It is also possible to return only the query, preventing the agent from executing it on the database.)

7. The original prompt, context and JSON containing the result are then passed to an LLM for processing (note: it does not return a natural language response). As it has the original question and now the result, it can formulate a natural language response that is returned to the end user in the chatbot.

8. A natural language response is displayed in the chatbot.

Policy Doc Path

1. The user asks a policy-related question:

2. This is passed to an LLM along with some context (the logged-in user and whether they are an admin user) for pre-processing.

3. The LLM establishes that this is not a query about specific user data and allows.

4. The prompt and chat context are passed to the OCI Agent, which is trained to evaluate the prompt and direct the query to the relevant tool. In this case, the RAG tool.

5. The RAG tool then performs a semantic search on the (already) vectorised policy documents stored in a data source (linked to a knowledge base).

6. It finds the most relevant document and returns the answer (in natural language) as well as a link to the cited document in the object bucket.

7. The result is displayed in the chatbot along with the citation, the relevant page numbers and a clickable link to download the citation doc. (A PAR URL needs to be injected into the link URL so the user can access it.)

In both paths, an Agent ‘session’ is first created, and in subsequent calls, this is passed to maintain the session and enable follow-up questions:

(Notice the admin user was enabled, which allowed a query about another employee.)

Cost

While pricing details can sometimes be complex to interpret, overall this seems cost-effective and is certainly a cheaper option compared to Oracle Digital Assistant.

Learnings

There is some configuration required for the setup of the AI services in the console, and it is necessary to ensure that all the correct policy statements are applied for the group.

Once Agents and tools are set up, the results are highly effective. There is extensive documentation for everything that is needed to set up and the various APIs needed to call for the Agent and Management of the system.

FGAC (Fine-Grained Access Control) can present challenges. In proof-of-concept testing, FGAC was achieved programmatically (essentially working as a Virtual Private Database) using an LLM and very specific security instructions. However, while workable for simple scenarios, this approach is not sufficient for enterprise-grade applications.

The correct approach would be to use RAS (Real Application Security).

Instead of traditional database users, RAS manages application users and application roles directly in the database, so the database knows who the actual end-user is (not just a shared connection pool user).

This would be the flow for this approach:

1. User: "What's my salary?"
2. AI Agent: Returns SQL "SELECT salary FROM employees WHERE first_name='John' AND last_name='Smith'"
3. APEX: Extracts SQL from AI response (no execution results in JSON)
4. APEX: Sets RAS context for john.smith@company.com
5. APEX: Executes SQL with automatic RAS security applied
6. Database: Returns only John's salary (policy-enforced)
7. APEX: Formats results using LLM if needed

The main difference in this model is that the Agent does not execute the SQL, it only returns the query, the user context needs to be set and the restricted result is returned.

Unfortunately, at the time of testing, RAS was not available in the “Always Free Autonomous Database” environment.

Conclusion: The Future of Enterprise Applications

OCI Generative AI Agents, when integrated with Oracle APEX, provide a powerful framework for building intelligent assistants that understand both structured enterprise data and unstructured policy documentation. The ability to integrate multiple tools, including SQL, RAG, as well as a ‘Function Calling’ tool that will allow for data to be pulled in from any external source, means that any RAG use case could be fulfilled with OCI Agents.

This architecture bridges the gap between unstructured documents and structured data, creating truly comprehensive AI assistants that understand both company policies and employee records.

As organisations continue their digital transformation journeys, solutions like this will increasingly set the standard for how enterprise applications are designed and delivered. The combination of Oracle APEX's development speed with OCI's AI capabilities provides a powerful foundation for building these next-generation applications.

Want to learn more about implementing AI-powered chatbots in Oracle APEX? Contact us today, and one of our expert developers will be in touch.

Technical Stack

- Frontend: Oracle APEX 24.2
- Backend: Oracle ‘Autonomous Always Free’ 23ai Database with PL/SQL
- AI Engine: OCI Generative AI Agents  
- Document Storage: OCI Object Storage
- LLM Service: Cohere  
- Security: Row-level security with AI-powered access control