Build Your First AI Agent in Oracle APEX 26.1 – Resort Booking Assistant with AI Tools

Oracle APEX 26.1 introduces one of the most exciting features in the low-code world: AI Agents and AI Tools. Instead of building traditional chatbots that simply answer questions, you can now build intelligent assistants that can reason, invoke PL/SQL procedures, query your database, and even perform transactions on behalf of your users.

In this tutorial, we will build a fully functional Resort Booking AI Assistant that can:

• Search for available rooms
• Provide room recommendations
• Show room pricing and amenities
• Book rooms
• Cancel bookings
• Display booking details
• List resort activities

By the end of this article, you will have a working AI Agent powered entirely by Oracle APEX 26.1 and PL/SQL.

---

What is an Agentic Application?

An Agentic Application is an application that can understand a goal, reason about it, decide which actions to take, and execute those actions autonomously using a set of tools.

Unlike a traditional chatbot that simply answers questions based on its training data, an AI Agent can actively interact with your application and perform tasks on behalf of the user.

Think of it as the difference between:

• Traditional Chatbot: "Here is information about available rooms."
• AI Agent: "I found three available rooms, booked the Family Suite for you, and sent the confirmation email."

---

How an Agent Thinks

An AI Agent generally follows four steps:

1. Understand the user's goal.
2. Determine which tools are required.
3. Execute one or more tools.
4. Present the final response to the user.

User Request
      ↓
Reasoning
      ↓
Tool Selection
      ↓
Tool Execution
      ↓
Final Response

---

Traditional Application vs Agentic Application

Traditional Application	Agentic Application
User follows predefined screens.	User interacts using natural language.
Developer controls every step.	Agent decides which actions to perform.
Single operation per screen.	Can perform multiple operations in one request.
Requires navigation through pages.	Works like a digital assistant.
Static workflows.	Dynamic and context-aware workflows.

---

When Should You Build an Agentic Application?

Agentic applications shine when users have to perform multiple steps or frequently search for information and execute actions.

Some excellent use cases are:

• Resort and Hotel Booking Systems
• Customer Support Portals
• Helpdesk Applications
• Employee Self-Service Systems
• HR Management Systems
• Project Management Applications
• Healthcare Portals
• Inventory and Warehouse Systems
• ERP and CRM Applications
• Knowledge Management Systems

---

When Should You Avoid Using an AI Agent?

Not every application needs an AI Agent.

Avoid using Agentic AI when:

• The workflow is extremely simple.
• The process is heavily regulated and requires strict approvals.
• Every step must be deterministic.
• Natural language does not provide any usability advantage.
• The application only performs basic CRUD operations.

For example, creating a simple employee master screen with four fields does not require an AI Agent. A traditional Oracle APEX form would be faster and easier to maintain.

---

Examples of Agentic Thinking

Consider the following user request:

I need a room for four people next weekend with a private pool and I also want to know the activities available.

A traditional application would require the user to:

1. Open the search page.
2. Enter dates.
3. Select number of guests.
4. View room details.
5. Navigate to the activities page.

An Agentic Application can perform all of these actions automatically:

1. Search available rooms.
2. Filter rooms by capacity.
3. Identify rooms with private pools.
4. Retrieve available activities.
5. Generate a consolidated response.

This ability to reason and orchestrate multiple actions is what makes an application truly Agentic.

---

Why Oracle APEX 26.1 is Exciting

Oracle APEX 26.1 brings Agentic AI directly into the low-code platform by introducing:

• AI Agents
• AI Tools
• Built-in support for multiple LLM providers
• Native tool invocation
• PL/SQL integration
• Multi-step reasoning capabilities

This means Oracle APEX developers can now build applications that not only display data but can also understand user intent and take meaningful actions on behalf of users.

1. Solution Architecture

The following architecture demonstrates how AI Agents work internally in Oracle APEX:

User
 ↓
AI Assistant
 ↓
AI Agent
 ↓
AI Tools
 ↓
PL/SQL APIs
 ↓
Oracle Database

The AI model decides which tool to invoke based on the user's prompt. Each tool is simply a PL/SQL function that returns a response back to the model.

---

2. Create the Database Objects

2.1 Create Resort Room Table

CREATE TABLE resort_room (
    room_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    room_name VARCHAR2(100),
    room_type VARCHAR2(50),
    capacity NUMBER,
    price_per_night NUMBER,
    amenities VARCHAR2(4000),
    active_yn CHAR(1) DEFAULT 'Y',
    CONSTRAINT resort_room_pk
        PRIMARY KEY (room_id)
);

---

2.2 Create Booking Table

CREATE TABLE resort_booking (
    booking_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    guest_name VARCHAR2(200),
    guest_email VARCHAR2(200),
    room_id NUMBER,
    checkin_date DATE,
    checkout_date DATE,
    no_of_guests NUMBER,
    booking_status VARCHAR2(30),
    total_amount NUMBER,
    created_on DATE DEFAULT SYSDATE,
    CONSTRAINT resort_booking_pk
        PRIMARY KEY (booking_id),
    CONSTRAINT resort_booking_fk
        FOREIGN KEY (room_id)
        REFERENCES resort_room(room_id)
);

---

2.3 Create Activities Table

CREATE TABLE resort_activity (
    activity_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    activity_name VARCHAR2(100),
    price NUMBER,
    description VARCHAR2(4000),
    CONSTRAINT resort_activity_pk
        PRIMARY KEY (activity_id)
);

---

3. Insert Sample Data

3.1 Insert Rooms

INSERT INTO resort_room
(room_name, room_type, capacity,
 price_per_night, amenities)
VALUES
(
'Garden Deluxe',
'DELUXE',
2,
4500,
'WiFi, TV, Pool Access'
);

INSERT INTO resort_room
(room_name, room_type, capacity,
price_per_night, amenities)
VALUES
(
'Family Suite',
'SUITE',
4,
8500,
'WiFi, TV, Pool, Mini Bar'
);

INSERT INTO resort_room
(room_name, room_type, capacity,
price_per_night, amenities)
VALUES
(
'Royal Villa',
'VILLA',
8,
20000,
'Private Pool, Butler Service'
);

COMMIT;

---

3.2 Insert Activities

INSERT INTO resort_activity
(activity_name, price, description)
VALUES
(
'Kayaking',
1500,
'2 hour guided kayaking'
);

INSERT INTO resort_activity
(activity_name, price, description)
VALUES
(
'Camp Fire Dinner',
2500,
'Outdoor dinner experience'
);

COMMIT;

---

4. Create Date Parsing Function

Large Language Models usually pass dates as strings. Therefore, it is safer to accept dates as VARCHAR2 and convert them manually.

CREATE OR REPLACE FUNCTION fn_parse_ai_date
(
    p_date VARCHAR2
)
RETURN DATE
IS
BEGIN
    BEGIN
        RETURN TO_DATE(
            p_date,
            'YYYY-MM-DD'
        );
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

BEGIN
    RETURN TO_DATE(
        p_date,
        'DD-MON-YYYY',
        'NLS_DATE_LANGUAGE=ENGLISH'
    );
EXCEPTION
    WHEN OTHERS THEN NULL;
END;

BEGIN
    RETURN TO_DATE(
        p_date,
        'DD Month YYYY',
        'NLS_DATE_LANGUAGE=ENGLISH'
    );
EXCEPTION
    WHEN OTHERS THEN NULL;
END;

RAISE_APPLICATION_ERROR(
    -20001,
    'Unsupported date format'
);

END;
/

---

5. Create Search Room Function

CREATE OR REPLACE FUNCTION fn_search_rooms (
    p_checkin IN VARCHAR2,
    p_checkout IN VARCHAR2,
    p_guests IN NUMBER
)
RETURN CLOB
IS
    l_checkin DATE;
    l_checkout DATE;
    l_json CLOB;
BEGIN
    l_checkin :=
        fn_parse_ai_date(p_checkin);

l_checkout :=
    fn_parse_ai_date(p_checkout);

apex_json.initialize_clob_output;

apex_json.open_array;

FOR r IN
(
    SELECT room_id,
           room_name,
           room_type,
           capacity,
           price_per_night,
           amenities
    FROM resort_room
    WHERE active_yn = 'Y'
    AND capacity >= p_guests
    AND NOT EXISTS
    (
        SELECT 1
        FROM resort_booking b
        WHERE b.room_id = resort_room.room_id
        AND b.booking_status = 'BOOKED'
        AND l_checkin < b.checkout_date
        AND l_checkout > b.checkin_date
    )
)
LOOP
    apex_json.open_object;

    apex_json.write(
        'ROOM_ID',
        r.room_id
    );

    apex_json.write(
        'ROOM_NAME',
        r.room_name
    );

    apex_json.write(
        'ROOM_TYPE',
        r.room_type
    );

    apex_json.write(
        'CAPACITY',
        r.capacity
    );

    apex_json.write(
        'PRICE_PER_NIGHT',
        r.price_per_night
    );

    apex_json.write(
        'AMENITIES',
        r.amenities
    );

    apex_json.close_object;
END LOOP;

apex_json.close_array;

l_json :=
    apex_json.get_clob_output;

apex_json.free_output;

RETURN l_json;

END;
/

---

6. Create the AI Agent

Navigate to:

Shared Components
→ AI Agents
→ Create

Use the following configuration:

• Name: Resort Booking Assistant
• Static ID: RESORT_AGENT
• Temperature: 0.2

System Prompt

You are an AI assistant for a luxury resort.

You can:

1. Search available rooms.
2. Provide room recommendations.
3. Explain amenities.

Always use tools.
Never assume room availability.

When calling search_rooms:

* P_CHECKIN must be in YYYY-MM-DD format.
* P_CHECKOUT must be in YYYY-MM-DD format.
* P_GUESTS must be numeric.
* Always search before recommending rooms.

---

7. Configure AI Tool

Navigate to:

Shared Components
→ AI Tools
→ Create

Create a tool named:

search_rooms

Tool Parameters

Parameter	Type
P_CHECKIN	String
P_CHECKOUT	String
P_GUESTS	Number

PL/SQL Function Body

RETURN fn_search_rooms(
         :P_CHECKIN,
         :P_CHECKOUT,
         :P_GUESTS
       );

---

8. Sample Prompts and Expected Results

Prompt 1

Show available rooms for 2 guests from 2026-08-10 to 2026-08-12.

Expected Result:

• Garden Deluxe
• Family Suite
• Royal Villa

---

Prompt 2

I need a room for 4 people from 2026-08-10 to 2026-08-12.

Expected Result:

• Family Suite
• Royal Villa

---

Prompt 3

Show me rooms for 8 guests.

Expected Result:

• Royal Villa

---

Prompt 4

I need a luxurious room with a private pool.

Expected Result:

• Royal Villa
• Private Pool
• Butler Service
• 20,000 per night

---

Prompt 5

What activities are available at the resort?

Expected Result:

• Kayaking - 1,500
• Camp Fire Dinner - 2,500

---

9. Testing the AI Tool Directly

You can test the function manually before wiring it to the AI Agent.

SELECT fn_search_rooms(
           '2026-08-10',
           '2026-08-12',
           2
       )
FROM dual;

Expected JSON:

[
  {
    "ROOM_ID":1,
    "ROOM_NAME":"Garden Deluxe",
    "ROOM_TYPE":"DELUXE",
    "CAPACITY":2,
    "PRICE_PER_NIGHT":4500,
    "AMENITIES":"WiFi, TV, Pool Access"
  },
  {
    "ROOM_ID":2,
    "ROOM_NAME":"Family Suite",
    "ROOM_TYPE":"SUITE",
    "CAPACITY":4,
    "PRICE_PER_NIGHT":8500,
    "AMENITIES":"WiFi, TV, Pool, Mini Bar"
  },
  {
    "ROOM_ID":3,
    "ROOM_NAME":"Royal Villa",
    "ROOM_TYPE":"VILLA",
    "CAPACITY":8,
    "PRICE_PER_NIGHT":20000,
    "AMENITIES":"Private Pool, Butler Service"
  }
]

---

Conclusion

Oracle APEX 26.1 AI Agents open the door to an entirely new category of applications where natural language becomes the user interface. With just a few PL/SQL functions and AI Tools, we built a fully functional Resort Booking Assistant capable of searching rooms, understanding user intent, and interacting directly with Oracle Database.

This is only the beginning. You can further extend this solution by adding:

• Room booking functionality
• Booking cancellation
• Vector search recommendations
• REST API integrations
• Payment gateway integration
• Voice-enabled assistants
• WhatsApp booking notifications

The possibilities are endless, and Oracle APEX 26.1 makes building AI-powered applications easier than ever before.