A self-service knowledge assistant is where most help desks start with AI: a page where customers ask questions in their own words and get answers from the knowledge base, with the sources. It is not finished when it answers, though. Every question it cannot answer, and every answer that did not help, says something about the knowledge base, so a good assistant records both.
This guide builds that assistant in Oracle APEX 26.1 on a RAG function in the database: a table that records every question with its answer, sources, outcome, and feedback, a function that answers and records, and an Ask page with the answer, its sources, and feedback buttons.
Code for This Guide
The examples are files 01 to 04 in the examples/ch23 folder of the Oracle AI code repository on GitHub, each with its output, and the finished page is in apex/f200.sql.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
The assistant answers with the ASK function and logs to RAG_LOG, both from how to build RAG with PL/SQL in Oracle Database.
Record Every Question
KA_QUESTIONS stores each question with its answer, sources, and an outcome of three kinds: Answered; Not found, a question on the subject that the knowledge base did not answer; and Off topic. HELPFUL holds the customer's feedback, and EMBEDDING the question's embedding, for finding what it is near later.
Example:
-- every question asked in the knowledge assistant, with its answer and the user's feedback
create table ka_questions (
question_id number generated always as identity constraint ka_questions_pk primary key,
asked_by varchar2(255) not null,
asked_at timestamp default systimestamp not null,
question varchar2(1000) not null,
answer clob,
sources json,
outcome varchar2(10) not null constraint ka_questions_outcome
check (outcome in ('Answered', 'Not found', 'Off topic')),
helpful varchar2(1) constraint ka_questions_helpful check (helpful in ('Y', 'N')),
embedding vector(384, float32)
);Output:
Table KA_QUESTIONS created.
Answer and Record in One Function
KA_ASK answers with ASK, reads from RAG_LOG the sources ASK gave the model, embeds the question, stores everything, and returns the new row's ID.
Example:
-- answers a question with ASK, and records it for the assistant's page and its review
create or replace function ka_ask (
p_question in varchar2,
p_user in varchar2
) return number
is
l_answer clob;
l_sources json;
l_embedding vector;
l_id ka_questions.question_id%type;
begin
l_answer := ask(p_question);
-- the sources that ASK gave the model, from its log
select sources into l_sources
from rag_log
where question = p_question
order by asked_at desc
fetch first 1 row only;
select vector_embedding(all_minilm_l12_v2 using p_question as data)
into l_embedding;
insert into ka_questions (asked_by, question, answer, sources, outcome, embedding)
values (p_user, p_question, l_answer, l_sources,
case when l_answer like 'I could not find this%' then 'Not found'
when l_answer like 'I can only answer%' then 'Off topic'
else 'Answered' end,
l_embedding)
returning question_id into l_id;
return l_id;
end;
/Output:
Function KA_ASK compiled
The outcome comes from the answer itself. ASK's instructions make the model say exactly "I could not find this in the Atlas knowledge base." when the sources do not answer, and ASK returns "I can only answer questions about Atlas products and services." without calling the model when nothing is near. Fixed phrases are what make the outcome something code can test.
Example:
-- asks three questions as a customer, and shows what was recorded
declare
l_id number;
begin
for q in (select column_value as question
from sys.odcivarchar2list(
'Why was my card charged twice this month?',
'Can I pay my invoices with PayPal?',
'What is the capital of France?')) loop
l_id := ka_ask(q.question, 'KESTREL');
end loop;
for r in (select * from ka_questions order by question_id) loop
dbms_output.put_line('Question ' || r.question_id || ': ' || r.question);
dbms_output.put_line('Outcome: ' || r.outcome);
dbms_output.put_line('Answer: ' || r.answer);
dbms_output.put_line('');
end loop;
end;
/Output:
Question 1: Why was my card charged twice this month?
Outcome: Answered
Answer: A duplicate charge usually happens when the payment gateway times out or does not answer
in time, causing the charge to be retried [1, 2]. In rare cases, both the original charge
and the retry succeed, resulting in you being charged twice [2].
Question 2: Can I pay my invoices with PayPal?
Outcome: Not found
Answer: I could not find this in the Atlas knowledge base.
Question 3: What is the capital of France?
Outcome: Off topic
Answer: I can only answer questions about Atlas products and services.
PL/SQL procedure successfully completed.One of each outcome: an answer with citations, a question the knowledge base does not answer, and a question about something else.
Build the Ask Page
Create a Blank Page, number 10, named Ask Atlas, with the icon fa-question-circle-o and a navigation menu entry.
In Page Designer, build the question:
- Create a region Your Question.
- Add a Textarea item P10_QUESTION, labeled Question, with Value Required on.
- Add a button ASK, labeled Ask, Hot, with the action Submit Page.
- Add a Hidden item P10_QUESTION_ID, to hold the ID of the recorded question.
Then create a process Answer the Question of type Execute Code, with When Button Pressed set to ASK. :APP_USER, the signed-in user, becomes ASKED_BY.
PL/SQL code of the process:
:P10_QUESTION_ID := ka_ask(:P10_QUESTION, :APP_USER);
Show the Answer Safely
Create a region Answer of type Dynamic Content, shown only when P10_QUESTION_ID is not null, with this PL/SQL Function Body returning a CLOB.
Region source of the Answer region:
-- the answer, escaped, as a paragraph for r in (select answer from ka_questions where question_id = :P10_QUESTION_ID) loop return '<p>' || apex_escape.html(r.answer) || '</p>'; end loop; return null;
APEX_ESCAPE.HTML matters. The answer is text a model wrote, which is untrusted input like everything a model returns, and it must never be able to put markup or script on the page.
Show the Sources
Create a Classic Report region Sources with the same condition, reading the sources from the question's JSON.
SQL query of the Sources region:
select s.n as "No.", s.source as "Source", round(s.distance, 3) as "Distance"
from ka_questions q,
json_table(q.sources, '$[*]'
columns (n number path '$.n',
source varchar2(200) path '$.source',
distance number path '$.distance')) s
where q.question_id = :P10_QUESTION_ID
order by s.nCollect Feedback Once per Answer
Add two buttons in the Answer region's Next slot: HELPFUL, labeled This helped, and NOT_HELPFUL, labeled This did not help. Give both a Server-side Condition of type Rows returned, so they appear only for an answer and only until the customer uses one.
Condition query of the feedback buttons:
select 1 from ka_questions where question_id = :P10_QUESTION_ID and outcome = 'Answered' and helpful is null
A process Record Feedback stores the choice, with the condition Request is contained in Value HELPFUL,NOT_HELPFUL and the success message "Thank you for your feedback." :REQUEST holds the name of the button that submitted the page.
PL/SQL code of the Record Feedback process:
update ka_questions set helpful = case :REQUEST when 'HELPFUL' then 'Y' else 'N' end where question_id = :P10_QUESTION_ID;
The Page at Work
Signed in as a customer, ask "Why don't the revenue totals in Analytics match my invoices?"
The answer explains the difference, Analytics counting paid invoices by payment date and Billing all invoices by issue date, and how to make them agree, citing source [1]. The Sources report shows that source 1 is article KB-503 at a distance of 0.243; the other three were further away and went unused. Choosing This helped records the feedback and removes the buttons.
Over the following days, more customers asked questions.
Example:
-- more questions from several customers, and how they ended
declare
l_id number;
begin
for q in (select * from json_table('[
["KESTREL", "How do I reset my password if the e-mail never arrives?"],
["WILLOW", "Is there a dark mode in Atlas Mobile?"],
["WILLOW", "Can I pay by bank transfer instead of a card?"],
["MERIDIAN", "How do I import contacts from an Excel file?"],
["MERIDIAN", "My scheduled report did not arrive this morning."],
["KITE", "Does Atlas CRM have an add-in for Outlook?"],
["KITE", "How many API calls can I make per minute?"],
["SAFFRON", "Can I pay with PayPal?"],
["SAFFRON", "Can our invoices show our purchase order number?"]]',
'$[*]' columns (asked_by varchar2(20) path '$[0]',
question varchar2(200) path '$[1]'))) loop
l_id := ka_ask(q.question, q.asked_by);
end loop;
end;
/
select question_id, asked_by, outcome, question
from ka_questions
where question_id > 3
order by question_id;Output:
PL/SQL procedure successfully completed.
QUESTION_ID ASKED_BY OUTCOME QUESTION
______________ ___________ ____________ __________________________________________________________
4 KESTREL Not found How do I reset my password if the e-mail never arrives?
5 WILLOW Not found Is there a dark mode in Atlas Mobile?
6 WILLOW Answered Can I pay by bank transfer instead of a card?
7 MERIDIAN Answered How do I import contacts from an Excel file?
8 MERIDIAN Answered My scheduled report did not arrive this morning.
9 KITE Not found Does Atlas CRM have an add-in for Outlook?
10 KITE Answered How many API calls can I make per minute?
11 SAFFRON Not found Can I pay with PayPal?
12 SAFFRON Answered Can our invoices show our purchase order number?
9 rows selected.Five of the nine were answered. Together with the questions asked on the page, the not-found and unhelpful ones are now material for finding the gaps in the knowledge base.
Conclusion
A knowledge assistant in Oracle APEX needs no AI component of its own: a process calls a function that answers with RAG and records the question, answer, sources, outcome, and embedding; a Dynamic Content region shows the escaped answer; a classic report reads the sources from JSON; and conditional buttons collect feedback once per answer. Fixed phrases in the RAG instructions turn each answer into a testable outcome.
