Oracle APEX + AI: Automating Database Inserts from Natural Language (JSON + AJAX Tutorial)

Automating Database Inserts with AI in Oracle APEX

If you're building conversational or AI-powered apps in Oracle APEX, one powerful pattern is to let GPT generate well-structured JSON — and then insert records into the database automatically via an AJAX call. Imagine letting your users speak or type natural language like “Had a one-on-one with Tim yesterday, went great” and instantly converting that into a structured database record. No forms. No dropdowns. No manual date entry.

Database Insertions based on User Prompts

In this tutorial, we’ll build a fully working example of how to use Oracle APEX + AI so that natural language input automatically converts into JSON — and then into a real database insert via AJAX. With clean JSON validation, PL/SQL processing, dynamic date interpretation, and a bulletproof system prompt, this setup becomes the perfect blueprint for building AI-enabled APEX applications.

Whether you're building meeting logs, activity tracking, CRM notes, or automated audit trails, this approach will help you deliver next-level intelligence to your APEX applications.

In this post, I’ll walk you through a clean setup where:

• GPT outputs pure JSON
• JavaScript parses and validates it
• APEX Insert Process executes automatically
• You get a smooth, error-free workflow

This pattern is perfect for meeting logs, activity logging, surveys, or any structured-input scenario.

---

1. JavaScript – Parsing GPT Output & Calling APEX Process

Place this JavaScript in the Change event of item P10_RESPONSE.

// Get the response text (GPT output)
let rawJson = $v("P10_RESPONSE");

// Parse the JSON safely
let data;
try {
    data = JSON.parse(rawJson);
} catch (e) {
    apex.message.showErrors([{
        type: "error",
        location: "page",
        message: "Invalid JSON received from GPT.",
        unsafe: false
    }]);
    return;
}

// Validate function name 
if (data.function !== "insertMeeting") {
    apex.message.showErrors([{
        type: "error",
        location: "page",
        message: "Unexpected function returned.",
        unsafe: false
    }]);
    return;
}

// Extract parameters
let p = data.parameters;

// AJAX call to APEX Process
apex.server.process(
    "INSERT_MEETING",
    {
        x01: p.lead,
        x02: p.meetingType,
        x03: p.meetingDate,
        x04: p.emotionRating
    },
    {
        success: function(res) {
            if (!res) res = {};
            apex.message.clearErrors();
            apex.message.showPageSuccess("Meeting inserted successfully!");
        },
        error: function(err) {
            apex.message.showErrors([{
                type: "error",
                location: "page",
                message: "Error while inserting meeting.",
                unsafe: false
            }]);
        }
    }
);

---

2. PL/SQL Ajax Process (INSERT_MEETING)

This code goes inside your APEX process of type Ajax Callback.

DECLARE
    v_lead          VARCHAR2(50) := apex_application.g_x01;
    v_type          VARCHAR2(50) := apex_application.g_x02;
    v_meeting_date  VARCHAR2(20) := apex_application.g_x03;
    v_rating        NUMBER       := apex_application.g_x04;
BEGIN
    INSERT INTO MEETINGS (
        LEAD_NAME,
        MEETING_TYPE,
        MEETING_DATE,
        EMOTION_RATING
    )
    VALUES (
        v_lead,
        v_type,
        TO_DATE(v_meeting_date, 'DD-MON-YYYY'),
        v_rating
    );

    apex_json.open_object;
    apex_json.write('status', 'ok');
    apex_json.close_object;
END;

---

3. System Prompt for GPT (LLM Prompt in APEX)

This is the system prompt for your APEX LLM region. It ensures GPT always returns clean JSON.

You are a function dispatcher.

Your ONLY output must be a raw JSON object with no markdown, no backticks, and no text outside the JSON.
Your response must always be a valid JSON object.

Natural language dates must be converted into actual calendar dates in "DD-MMM-YYYY" format.

Date interpretation rules:
- today is &TODAY. Interpret natural language dates relative to this date.
- "yesterday" = one day before today.
- "tomorrow" = one day after today.
- Weekday references (e.g., "last Friday") must be resolved to the correct past date.
- If no date is mentioned, use today.
- Always compute based on the real current system date.

JSON structure:
{
  "function": "insertMeeting",
  "parameters": {
    "lead": one of ["Tim", "Jefrey", "Melody"],
    "meetingType": a classified meeting type derived from user input (e.g., "SKIP LEVEL", "APPRAISAL", "ONE-ON-ONE"),
    "meetingDate": computed date in DD-MMM-YYYY format,
    "emotionRating": integer from 1 to 5 based on sentiment (5 = most positive)
  }
}

Additional rules:
- Do NOT include explanations or descriptive text.
- Do NOT wrap output in code blocks.
- If user input is unclear, make the best assumption and still output valid JSON.

---

4. Setting Dynamic Date Context in APEX

Create Application Item

Name: TODAY

Create Application Computation

Type: After Authentication
Target: TODAY
Computation:

TO_CHAR(SYSDATE, 'DD-MON-YYYY')

This ensures GPT always knows the real current date and can compute natural language expressions like “yesterday”, “last Monday”, etc.

---

5. SQL Script to create table - MEETINGS

CREATE TABLE MEETINGS (
    MEETING_ID        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    LEAD_NAME         VARCHAR2(50)   NOT NULL,
    MEETING_TYPE      VARCHAR2(50)   NOT NULL,
    MEETING_DATE      DATE           NOT NULL,
    EMOTION_RATING    NUMBER(1)      CHECK (EMOTION_RATING BETWEEN 1 AND 5),
    CREATED_AT        TIMESTAMP DEFAULT SYSTIMESTAMP
);

---

Final Thoughts

With this setup, you get a clean and reliable workflow:

• User enters natural language
• GPT produces clean JSON
• JavaScript validates instantly
• APEX inserts the record automatically

This pattern can be extended for tasks, logs, reviews, time entries, and automation.

With just a few lines of JavaScript, a smart system prompt, and an AJAX callback, Oracle APEX becomes a powerful AI-driven platform capable of understanding natural language and transforming it into real database actions. This foundation can be extended to automate task creation, customer notes, QA logs, CRM updates, and dozens of other high-impact workflows.

If you found this guide helpful, consider sharing it with your APEX and backend developer community — and feel free to explore more experiments on bringing AI into Oracle APEX. I’ll continue publishing practical, real-world examples on Into The Oracle Verse and YouTube. Stay tuned for more AI-powered APEX tutorials!