Building an AI-Powered HR Policy Assistant Using Oracle APEX and Google Gemini

How I built an HR document management application with an AI chatbot that helps employees find answers from company policies.

Introduction

I have been exploring how Generative AI can be used in Oracle APEX applications, especially in business scenarios where employees need information quickly without having to search through multiple documents.

One idea I wanted to work on was an AI-powered HR Policy Assistant.

In most organizations, HR policies are maintained in PDF, Word, and text documents. Employees may have questions about leave policies, attendance, working hours, employee referrals, or company rules. Usually, they need to find the relevant document, open it, and search for the information manually.

I wanted to make this process simpler.

So, I developed an HR Policy Management application in Oracle APEX and integrated a chatbot powered by Google Gemini. The idea was to allow HR users to upload policy documents and let employees ask questions through a chat interface.

Instead of searching through documents manually, employees can ask questions in normal language and receive answers based on the available policy information.

In this article, I’ll walk through the approach I followed, the database structure, PL/SQL procedures, Gemini configuration, and chatbot integration.

1. Application Overview

I developed the application using Oracle APEX 26.2.1.

The application has two main parts:

HR Policy Management

This section allows users to:

• Upload HR policy documents.
• Provide a document name.
• Store the uploaded file in the database.
• View uploaded documents.
• Maintain document status.
• Search the available policy documents.

XTG HR AI Assistant

This is the chatbot interface available within the application.

Employees can ask questions related to:

• Leave Policy
• Attendance
• Working Hours
• Company Rules

The chatbot provides responses using the policy information available in the application.

HR Policy Management page showing the document upload section and the list of available HR policies.

2. Database Design

Before implementing the chatbot, I created separate database tables to manage uploaded documents, extracted text, chunks, and chat conversations.

The main tables used in my implementation are:

The reason for separating these tables was to keep document management, text extraction, chunk processing, and chatbot conversations organized.

The document-processing flow looks like this:

Upload Document → Extract Text → Store Extracted Text → Split into Chunks → Prepare Document Data for AI Retrieval

Database tables created for document processing and chatbot functionality.

3. Uploading Documents and Extracting Text Using PL/SQL

Once a document is uploaded, the next step is to extract its content.

For this, I created a PL/SQL procedure named EXTRACT_POLICY_TEXT.

The procedure reads the uploaded file from the POLICY_DOCUMENTS table and uses Oracle's DBMS_VECTOR_CHAIN.UTL_TO_TEXT function to extract text from the document.

After extracting the content, it removes any previously extracted text for the same document and inserts the latest content into POLICY_TEXT.

Finally, it calls another procedure named TRAIN_DOCUMENT to split the extracted content into smaller chunks.

Procedure: EXTRACT_POLICY_TEXT

CREATE OR REPLACE PROCEDURE EXTRACT_POLICY_TEXT
(
    p_doc_id NUMBER
)
AS
    l_text CLOB;
BEGIN

    -- Extract text from the uploaded document
    SELECT DBMS_VECTOR_CHAIN.UTL_TO_TEXT(file_content)
      INTO l_text
      FROM POLICY_DOCUMENTS
     WHERE DOC_ID = p_doc_id;
    -- Remove previously extracted text
    DELETE FROM POLICY_TEXT
     WHERE DOC_ID = p_doc_id;
    -- Store the latest extracted text
    INSERT INTO POLICY_TEXT
    (
        DOC_ID,
        POLICY_TEXT,
        CREATED_DATE
    )
    VALUES
    (
        p_doc_id,
        l_text,
        SYSDATE
    );
    -- Split the extracted content into chunks
    TRAIN_DOCUMENT(p_doc_id);
END;
/

This procedure handles the initial document-processing stage.

Rather than passing the uploaded file directly into the chatbot, I wanted to prepare its contents in the database first.

4. Splitting Policy Documents into Smaller Chunks

One important part of working with document-based AI applications is deciding how to handle large documents.

For example, an HR policy document may contain several pages covering leave eligibility, working hours, attendance, holidays, and employee responsibilities.

Processing the entire document as one large text block is not always the best approach.

To organize the extracted content, I created another PL/SQL procedure called TRAIN_DOCUMENT.

In this implementation, I used a chunk size of 2,000 characters.

The procedure:

1. Retrieves the document name.
2. Deletes previously generated chunks for that document.
3. Reads the extracted text from POLICY_TEXT.
4. Divides the content into smaller sections.
5. Stores each section in POLICY_CHUNKS.
6. Updates the document status to ACTIVE.

Procedure: TRAIN_DOCUMENT

CREATE OR REPLACE PROCEDURE TRAIN_DOCUMENT
(
    p_doc_id NUMBER
)
AS
    l_text        CLOB;
    l_chunk_size  NUMBER := 2000;
    l_pos         NUMBER := 1;
    l_chunk_no    NUMBER := 1;
    l_chunk       CLOB;
    l_doc_name    POLICY_DOCUMENTS.DOC_NAME%TYPE;
BEGIN

    -- Get document name
    SELECT DOC_NAME
      INTO l_doc_name
      FROM POLICY_DOCUMENTS
     WHERE DOC_ID = p_doc_id;
    -- Remove old chunks
    DELETE FROM POLICY_CHUNKS
     WHERE DOC_ID = p_doc_id;
    -- Read extracted text
    SELECT POLICY_TEXT
      INTO l_text
      FROM POLICY_TEXT
     WHERE DOC_ID = p_doc_id;
    -- Create smaller chunks
    LOOP
        EXIT WHEN l_pos > DBMS_LOB.GETLENGTH(l_text);
        l_chunk :=
            DBMS_LOB.SUBSTR
            (
                l_text,
                l_chunk_size,
                l_pos
            );
        INSERT INTO POLICY_CHUNKS
        (
            DOC_ID,
            CHUNK_NO,
            SECTION_NAME,
            CHUNK_TEXT,
            CHUNK_LENGTH,
            CREATED_DATE
        )
        VALUES
        (
            p_doc_id,
            l_chunk_no,
            l_doc_name,
            l_chunk,
            LENGTH(l_chunk),
            SYSDATE
        );
        l_pos := l_pos + l_chunk_size;
        l_chunk_no := l_chunk_no + 1;
    END LOOP;
    -- Activate the document
    UPDATE POLICY_DOCUMENTS
       SET STATUS = 'ACTIVE'
     WHERE DOC_ID = p_doc_id;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

This gives me smaller, manageable sections of the original policy document.

One thing worth mentioning is that this implementation uses fixed-length character chunking. It is a basic approach to preparing document content. In a production implementation, sentence-aware or semantic chunking can be considered to preserve the meaning of individual sections.

Also, creating these text chunks is not the same as training or fine-tuning a Gemini model. They are prepared document data that can be used in a retrieval-based AI architecture.

5. Configuring Google Gemini in Oracle APEX

After preparing the document-processing layer, I moved to the AI integration.

Oracle APEX provides Generative AI Services, which allow developers to configure supported AI providers and use them in applications.

For this implementation, I configured Google Gemini.

The configuration includes:

• AI Provider: Google Gemini
• Base URL: Google Generative Language API
• AI Model: Gemini Flash Lite model
• Credentials: Configured Google Gemini credentials
• Provider API: Interactions

I also used the Test Connection option to validate the connection.

Get Apandem’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This configuration allows the application to communicate with the Gemini service.

Caption: Configuring Google Gemini as a Generative AI Service in Oracle APEX.

6. Building the Chatbot Interface in Oracle APEX

For the chatbot interface, I wanted something simple that employees could access without navigating away from the HR Policy Management page.

I created two Static Content regions on Page 0 (Global Page).

Using the Global Page was useful because the chatbot interface could be made available across the application’s pages.

I created:

• XTG Chatbot — The floating chatbot icon.
• XTG Chat Window — The chatbot conversation window.

The chatbot window includes:

• A header with the assistant name.
• A New Chat option.
• A Close option.
• A message display area.
• A text area for entering questions.
• A Send button.

Chatbot HTML structure

I used HTML to create the chatbot interface.

Here is the main structure:

<div id="chat-window">

    <div class="chat-header">
        <span>🤖 HR AI Assistant</span>
        <div class="chat-actions">
            <span id="refresh-chat"
                  title="New Chat">🔄</span>
            <span id="close-chat"
                  title="Close">✖</span>
        </div>
    </div>
    <div id="chat-messages">
        <div class="bot-message">
            👋 Hello!
            <br><br>
            I am your HR AI Assistant.
            Ask me anything about:
            <ul>
                <li>Leave Policy</li>
                <li>Attendance</li>
                <li>Working Hours</li>
                <li>Company Rules</li>
            </ul>
        </div>
    </div>
    <div class="chat-footer">
        <textarea
            id="chat-question"
            placeholder="Ask your question..."
            rows="2"></textarea>
        <button
            type="button"
            id="send-btn">
            Send
        </button>
    </div>
</div>

I kept the interface separate from the main application page so that it could be maintained independently.

Floating chatbot interface with a welcome message and suggested HR topics.

7. Testing the HR AI Assistant

After completing the interface and integration, I tested the chatbot using questions related to the uploaded HR policies.

For example, I asked:

“Explain Working Hours.”

The assistant returned a response with the working hours information and identified the source policy.

This is an important part of the implementation.

I wanted the assistant to provide policy-related information rather than simply generating generic answers.

Showing the source document also helps users understand where the information came from.

The assistant answering a working-hours question using the available HR policy information.

8. Overall Implementation Flow

The overall implementation can be understood in three stages.

Stage 1: Document Management

HR users upload policy documents through the APEX application. The document details and file content are stored in the database.

Stage 2: Document Processing

PL/SQL procedures extract the text and divide it into smaller chunks. The document status is updated after processing.

Stage 3: AI Interaction

Employees enter questions through the chatbot. The AI integration uses the relevant policy content to generate a response.

The intended architecture is:

HR User
   |
   v
Oracle APEX Application
   |
   v
Upload HR Policy Document
   |
   v
POLICY_DOCUMENTS
   |
   v
EXTRACT_POLICY_TEXT
   |
   v
POLICY_TEXT
   |
   v
TRAIN_DOCUMENT
   |
   v
POLICY_CHUNKS
   |
   v
Document Retrieval / AI Integration
   |
   v
Google Gemini
   |
   v
XTG HR AI Assistant
   |
   v
Response to Employee

The document retrieval and AI integration layer is what connects the prepared policy content to the chatbot. The extraction and chunking procedures shown above implement the document preparation part of that architecture.

9. Where Can This Approach Be Used?

This approach is not limited to HR policies.

The same concept can be extended to different business applications.

Some possible use cases include:

• HR: Leave policies, attendance guidelines, employee handbooks, and company rules.
• Finance: Finance procedures, expense policies, and approval guidelines.
• Procurement: Procurement policies, supplier guidelines, and purchasing procedures.
• IT Support: Technical documentation, troubleshooting guides, and internal knowledge bases.
• Compliance: Company procedures, compliance documents, and operational guidelines.

For organizations that maintain a large number of documents, this type of application can make information easier to access.

10. What I Learned

While building this application, I got the opportunity to work across different areas of Oracle APEX and database development.

Some of the important areas I explored were:

• Creating an HR document management application using Oracle APEX.
• Working with BLOB and CLOB data.
• Extracting text from uploaded documents using Oracle Database functionality.
• Developing PL/SQL procedures for document processing.
• Splitting large documents into smaller chunks.
• Configuring Google Gemini as an AI provider.
• Creating a floating chatbot interface using HTML and CSS.
• Exploring how document-based AI assistants can be used in business applications.

What I found particularly interesting was how a traditional Oracle APEX application can be extended with Generative AI capabilities to create a more interactive experience.

Conclusion

This project helped me explore a practical use case for combining Oracle APEX, Oracle Database, PL/SQL, and Generative AI.

The main idea was simple: instead of asking employees to search through multiple policy documents, provide them with an interface where they can ask questions and find relevant information.

There is still room to improve the solution, especially around semantic chunking, vector embeddings, retrieval accuracy, access control, and document-level security.

But this was a useful starting point for understanding how AI-powered assistants can be developed within Oracle APEX applications.

I’m looking forward to exploring more business use cases where Oracle APEX and Generative AI can work together.

Thanks for reading!