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

RAG is a hot topic at the moment, so I wanted to create a proof of concept to put the OCI Agent Generative Agent functionality through its paces. My main goal is to create a chatbot to query both local data and policy documents stored in an object bucket and to receive natural language responses with downloadable citations if it is related to a policy doc.

In this blog, I will document my approach, learnings and thoughts on the AI services I am utilising.

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 instant, accurate answers?

That's exactly what I set out to build - 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

Before I went down the path of OCI (Oracle Cloud Infrastructure) RAG Agents, I thought I should try out the out-of-the-box AI Assistant chatbot that promises the ability to add RAG sources.

This approach didn’t get very far; there are some distinct limitations with this tool that meant I could go no further.

It is easy to add a local data RAG source. You just add the queries in the AI Configuration, and this works reasonably well.

If this is an HR chatbot, we need to ask it in the first person, which didn’t go well:

The issue is that we can’t provide context to ‘I’ or ‘my’ etc. Under the hood, the Assistant uses Select AI, so for ‘What is the hire date for Lee Burgess?’, it would translate into a query like:

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

However, when we asked what was my hire date, it had no employee name to formulate a query.

I tried amending the system prompt to train it how to deal with this:

But it made no difference.

The other main requirement was to augment the data with policy document information stored in an object bucket and have the ability to show the citation with clickable links.

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

There does seem to be a way to process the CLOB response.

This might work once we have the Agent set up and returning the data, however, as there is no way to pre-process the prompt (to make it work with ‘my’, ‘I’, etc or any way to return a citation with a downloadable link, I needed to go down another path.

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. (I used an Always Free Autonomous DB).

An APEX instance.

Admin rights for tenancy / the ability to add policy statements to your group.

Access to an LLM Provider via REST API.

Architecture

This was the architecture for my solution:

Core Application Layer

APEX - The web application development platform that provides the user interface and orchestrates the entire AI integration. Users interact with our chatbot in APEX to ask questions and receive AI-powered responses.

Oracle Autonomous Database - The Cloud database that stores employee data, policies, and application logic. I had a simple data model with some dummy data:

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:

I set a basic routing instruction statement to let the Agent know how best to route the queries:

An endpoint was created by default; this allows us to access our Agent via API.

You can add multiple tools to an Agent. We just needed two for this POC…

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 you configure it.

Config:

So the correct query can be formulated, you need to supply your full database schema. For this, I just exported the DDL directly from APEX and pasted it here:

Next, we give some in-context learning examples. If you have the data of the top things that might be asked in your tool, it is a good idea to give as many examples as possible to improve the output. This is some additional training for your tool, you give it plain English and clear instructions, and it will take these into account whenever it is performing a task.

You need to create a database connection and then select it from here.

Select this:

If you want the tool to execute the query and return the result, leave it off if you just want the query returned.

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"}]}

I pass this and the original prompt to an LLM to tidy this up and provide a natural language response.

You can pass the descriptions of tables and columns in here to improve the tool's understanding of the data model if it is more complex (I just asked AI to generate based on my model):

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

Config:

You need to link your RAG tool to a knowledge base (5 Max)

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

I had issues with my tool hallucinating, so I added some custom instructions and example responses, and this helped:

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 our KB, we need at least one connected Data Source. I created an object bucket and added my dummy policy document:

Note:  To make documents available to the RAG system, you must run an ingestion job each time documents are added or removed. This 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 your documents.

I trigger the job using the Agent Management APIs.

Alternatives you can use as the source of your data store:

•  OCI OpenSearch:
  You must have documents chunked into files with less than 512 tokens each, and you must have ingested and indexed those documents in OpenSearch before you continue. 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. We can view, add and remove docs from our APEX app using the OCI Storage Service APIs.

In the real world, I would probably create an automation to run the ingestion job (via API) at regular intervals. For my POC, I just have a button to trigger on demand.

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 my chatbot, a user asks: ‘What was my hire date?’, or ‘Is Karen Johnson on probation?’

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

3. We pass this amended prompt (‘What is my hire date?’ is now ‘What is Lee Burgess’s hire date?’).

4. We pass 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, we just return a message to the user saying they do not have permission etc:

If allowed, we 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. (You can also choose to return the query only, so the agent doesn’t execute the query 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 our 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 (You will need to inject a PAR URL into the link URL so the user can access it). 

In both paths, we first create an Agent ‘session’, and then in subsequent calls, we pass this so we can maintain the session and ask follow-up questions:

(Notice admin user was enabled, so I was able to ask about another emp).

Cost

I always find these quite difficult to digest, however, on the whole, this seems pretty reasonable and is certainly a cheaper option than the Oracle Digital Assistant.

Learnings

There is a fair bit of configuration required for the setup of the AI services in the console, and you need to ensure you have all the correct policy statements applied for your group.

Once the Agents and tools are set up though, the results are impressive. There is extensive documentation for everything you will need to set up and the various APIs you need to call for the Agent and Management of the system.

FGAC (Fine-Grained Access Control) has been a challenge. I have achieved it in my POC programmatically (essentially working as a Virtual Private Database) with the help of an LLM and very specific security instructions. However, this is a very basic use case, and for an enterprise application, this will not cut it.

I believe 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

So the main difference is, that the Agent does not execute the SQL, it just returns the query, we set the user context and get the restricted result back.

Unfortunately, as I was using the ‘Always free Autonomous’ DB, RAS was not available to test out at this time.

Conclusion: The Future of Enterprise Applications

These AI Agents are incredibly powerful, and I have found the results on this small use case very consistent.

The ability to add multiple tools as well as a ‘Function Calling’ Tool that will essentially allow you to pull in data 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, intelligent assistants like this will become the new standard. The combination of Oracle APEX's development speed with OCI's AI capabilities provides a powerful foundation for building these next-generation applications, and I look forward to being part of it.

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