No SQL. No Filters. Just Ask: AI Powered Natural Language Reports in Oracle APEX
A small experiment that turned into a full working feature on my APEX app
I have been building APEX apps for a while now, and one thing that always bugged me is how much effort normal users have to put in just to find one record. They open the app, they see 200 rows, and then they get stuck trying to figure out which filter icon does what. Most of my end users are not technical people. They do not know what an Interactive Report filter is, and honestly they should not need to know.
So I asked myself, what if a person could just type what they want in plain English and the report configures itself. No SQL. No filters. Just ask. That line actually became the title of this whole thing because it is literally what I wanted to build.
The First Problem, Understanding What The User Actually Wants
The hard part was never the report. APEX Interactive Reports already support filtering, sorting and searching out of the box. The hard part was converting a sentence typed by a random user into something the report can actually use.
I tried a few things before landing on something that worked properly.
Attempt 1, Keyword Matching with DECODE and INSTR
My very first attempt was silly if I am honest. I thought I could just check the sentence for keywords like "gmail" or "alphabetical" using INSTR and build the where clause manually with a bunch of IF conditions in PL/SQL.
declare
l_text varchar2(4000) := :P15_USER_QUESTION;
l_where varchar2(4000);
begin
if instr(lower(l_text), 'gmail') > 0 then
l_where := 'EMAIL_ADDRESS LIKE ''%gmail%''';
elsif instr(lower(l_text), 'alphabetical') > 0 then
l_where := '1=1 ORDER BY FULL_NAME';
else
l_where := '1=1';
end if;
apex_util.set_session_state('P15_GENERATED_WHERE', l_where);
end;
Attempt 2, Oracle Text and CONTAINS
Next I thought maybe Oracle Text search with CONTAINS could help me score the sentence against column names. I even created a text index on a helper table mapping column names to synonyms.
select column_name, score(1) as relevance from column_synonyms where contains(synonym_text, :p_user_question, 1) > 0 order by relevance desc
This did not work either. Oracle Text is great for document search but it is not really built to understand intent like "customers created after last month" versus "customers whose name starts with A". I spent almost two days on this path before I accepted it was the wrong tool for the job.
Finally, The Approach That Worked
What actually worked was giving up on trying to parse the sentence myself using SQL tricks, and instead sending the user question to an AI model through a REST call, and asking the model to return a small JSON object describing the filter, not free text, just structured JSON with the column, operator and value. Once I had clean JSON, turning it into an APEX report filter became simple.
Step 1, The REST Call to the AI Model
I created a Web Credential and a REST Data Source pointing to the model API, then wrote a PL/SQL process that sends the user question along with a strict instruction on what format to return.
declare
l_body clob;
l_response clob;
l_question varchar2(4000) := :P15_USER_QUESTION;
l_column varchar2(100);
l_operator varchar2(20);
l_value varchar2(400);
begin
l_body := '{'
|| '"model":"claude-sonnet-4-6",'
|| '"max_tokens":300,'
|| '"messages":[{"role":"user","content":"'
|| 'Return only JSON with keys column, operator, value for this request on a customer table with columns CUSTOMER_ID, FULL_NAME, EMAIL_ADDRESS. Question: '
|| l_question || '"}]}';
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
l_response := apex_web_service.make_rest_request(
p_url => 'https://api.anthropic.com/v1/messages',
p_http_method => 'POST',
p_body => l_body,
p_credential_static_id => 'AI_MODEL_CRED'
);
apex_json.parse(l_response);
l_column := apex_json.get_varchar2(p_path => 'content[1].parsed.column');
l_operator := apex_json.get_varchar2(p_path => 'content[1].parsed.operator');
l_value := apex_json.get_varchar2(p_path => 'content[1].parsed.value');
apex_util.set_session_state('P15_AI_COLUMN', l_column);
apex_util.set_session_state('P15_AI_OPERATOR', l_operator);
apex_util.set_session_state('P15_AI_VALUE', l_value);
end;
Step 2, Building The Filter Safely Using Bind Variables
Once I had the column, operator and value coming back clean, I did not concatenate them straight into a query string. I used apex_ir apis and bind variables so nothing typed by the user or returned by the AI ever touches the SQL directly as raw text.
declare
l_column varchar2(100) := :P15_AI_COLUMN;
l_operator varchar2(20) := :P15_AI_OPERATOR;
l_value varchar2(400) := :P15_AI_VALUE;
l_allowed boolean := false;
begin
for c in (select column_name from user_tab_columns
where table_name = 'CUSTOMERS')
loop
if c.column_name = upper(l_column) then
l_allowed := true;
end if;
end loop;
if l_allowed then
apex_ir.reset_report(
p_page_id => 15,
p_region_id => (select region_id from apex_application_page_regions
where page_id = 15 and region_name = 'Customers Report')
);
apex_ir.add_filter(
p_page_id => 15,
p_region_id => (select region_id from apex_application_page_regions
where page_id = 15 and region_name = 'Customers Report'),
p_column => l_column,
p_operator_abbr => l_operator,
p_value => l_value
);
end if;
end;
Step 3, The Chat Style Panel On The Page
For the assistant panel itself I did not use a plugin, I just built a small region with a text field and a Dynamic Action on button click that calls the PL/SQL process above through Ajax and then refreshes the Interactive Report region.
apex.server.process("APPLY_NL_FILTER", {
pageItems: "#P15_USER_QUESTION"
}, {
dataType: "json",
success: function (data) {
apex.region("customersReport").refresh();
var msgBox = document.getElementById("nlrChatMessages");
var bubble = document.createElement("div");
bubble.className = "chat-message";
bubble.textContent = "Got it, updating your report now";
msgBox.appendChild(bubble);
},
error: function () {
apex.message.showErrors([{
type: "error",
location: "page",
message: "Something went wrong understanding that question, try rephrasing it"
}]);
}
});
Things I Would Tell Anyone Trying This
If you are thinking about building something similar on your own app, here are a few honest points from my experience.
Do not skip the validation step thinking your users will always type something normal. Someone typed "delete everyone" during my testing just to see what happens, and because of the column validation and because I never let the AI generate raw SQL, nothing bad happened, it simply could not match a real delete operation to my filter logic. I would not have been that lucky if I had gone with my very first idea of asking the AI to write SQL directly.
Also test with a lot of odd phrasing before you trust this in front of real users. I broke my own build about ten times before it felt stable enough to demo.
What Is Next For This
Right now it only handles filtering and simple sorting. Next I want to add support for questions like "how many customers signed up this month" which needs an aggregate response instead of a filtered report, so the assistant would need to sometimes reply with a plain sentence and sometimes reconfigure the report, depending on what is actually being asked.
That part is still in progress, but the filtering piece described above is fully working right now on my demo app.
Comments
Post a Comment