Support agents answer the same problems again and again, and the help desk already knows how each was solved: in the comments of resolved tickets and in the knowledge base. An AI help desk workspace brings that knowledge to the agent at the moment it is needed: beside each ticket, how the most similar tickets were resolved, which articles apply, and a reply drafted from both, which the agent checks, completes, and sends.
This guide builds that workspace in Oracle APEX 26.1 and Oracle AI Database 26ai: vector searches for similar resolved tickets and articles, a draft function whose instructions keep it from promising actions nobody took, a send procedure that measures how much of each draft was kept, the page itself, and a prompt improved from those measurements.
Code for This Guide
The examples are in the examples/ch24 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.
They use the ticket and article embeddings from how to generate embeddings in SQL with VECTOR_EMBEDDING, the AI classification columns from how to classify data with an LLM in Oracle SQL, and the GENERATE function from how to make LLM calls reliable in PL/SQL.
Find What Was Done Before
For an agent, the useful part of a similar ticket is how it ended: the last comment an agent wrote on it. This finds the three resolved or closed tickets nearest to ticket 9, "Duplicate charge on credit card", with that comment.
Example:
-- the three resolved tickets nearest to ticket 9, with the agent's last comment on each
begin
for r in (select t.ticket_id, t.subject,
vector_distance(t.embedding, x.embedding, cosine) as distance,
(select c.body
from ticket_comments c
where c.ticket_id = t.ticket_id and c.author_type = 'Agent'
order by c.created_at desc
fetch first 1 row only) as resolution
from tickets t, tickets x
where x.ticket_id = 9
and t.ticket_id <> x.ticket_id
and t.status in ('Resolved', 'Closed')
order by distance
fetch first 3 rows only) loop
dbms_output.put_line('Ticket ' || r.ticket_id || ' (' || to_char(r.distance, 'fm0.000')
|| '): ' || r.subject);
dbms_output.put_line('Resolution: ' || r.resolution);
end loop;
end;
/Output:
Ticket 154 (0.008): Duplicate charge on credit card
Resolution: The second charge was a retry after a timeout at the payment gateway. I have refunded
it; banks show the refund within 5 to 10 business days. See article KB-201.
Ticket 361 (0.045): Duplicate charge on credit card
Resolution: The second charge was a retry after a timeout at the payment gateway. I have refunded
it; banks show the refund within 5 to 10 business days. See article KB-201.
Ticket 15 (0.047): Duplicate charge on credit card
Resolution: The second charge was a retry after a timeout at the payment gateway. I have refunded
it; banks show the refund within 5 to 10 business days. See article KB-201.
PL/SQL procedure successfully completed.Three tickets about the same problem, all resolved the same way. The nearest articles complete the picture.
Example:
-- the two knowledge base articles nearest to ticket 9
select a.article_id,
round(vector_distance(a.embedding, t.embedding, cosine), 3) as distance,
a.title
from kb_articles a, tickets t
where t.ticket_id = 9
order by distance
fetch first 2 rows only;Output:
ARTICLE_ID DISTANCE TITLE _____________ ___________ __________________________________________ KB-201 0.237 Duplicate charges and refunds KB-503 0.589 Why Analytics and Billing totals differ
KB-201 is near; KB-503, at 0.589, is not about this subject. The agent has the answer before writing a word.
Draft a Reply
HD_REPLIES keeps each draft and, once sent, the text that was sent and how much of the draft the agent kept, from 0 to 100.
Example:
-- every reply drafted by the model, and what the agent sent create table hd_replies ( reply_id number generated always as identity constraint hd_replies_pk primary key, ticket_id number not null constraint hd_replies_ticket references tickets, agent varchar2(255) not null, drafted_at timestamp default systimestamp not null, draft clob not null, sent_at timestamp, sent clob, similarity number -- how much of the draft the agent kept, 0 to 100 );
Output:
Table HD_REPLIES created.
HD_DRAFT_REPLY builds a prompt from the agent's name, the customer, the ticket, the resolutions of the three most similar resolved tickets, and the two nearest articles, then stores the draft.
Example:
-- drafts a reply from the resolutions of similar tickets and the nearest articles
create or replace function hd_draft_reply (
p_ticket_id in number,
p_user in varchar2
) return number
is
c_instructions constant varchar2(1000) :=
'You draft replies for the support agents of Atlas Software. Write to the customer by '
|| 'first name. Use only the facts in the earlier resolutions and the articles. Never '
|| 'say that an action was taken; write each step the agent must take in square '
|| 'brackets, like [refund issued]. Mention an article by its ID when you refer to it. '
|| 'Plain text, at most 120 words, signed with the agent''s name.';
l_agent agents.name%type;
l_prompt clob;
l_draft clob;
l_id hd_replies.reply_id%type;
begin
-- the agent, from the user name: EMMA is emma.lindqvist@atlas.example
select max(name) into l_agent
from agents
where upper(substr(email, 1, instr(email, '.') - 1)) = upper(p_user);
select 'Agent: ' || nvl(l_agent, 'Atlas Support') || chr(10)
|| 'Customer: ' || c.contact_name || ', ' || c.company || chr(10)
|| 'Ticket: ' || t.subject || chr(10) || t.description || chr(10) || chr(10)
|| 'Resolutions of similar tickets:' || chr(10)
|| (select listagg('- ' || r.resolution, chr(10))
from (select (select c2.body
from ticket_comments c2
where c2.ticket_id = s.ticket_id and c2.author_type = 'Agent'
order by c2.created_at desc
fetch first 1 row only) as resolution
from tickets s
where s.ticket_id <> t.ticket_id
and s.status in ('Resolved', 'Closed')
order by vector_distance(s.embedding, t.embedding, cosine)
fetch first 3 rows only) r) || chr(10) || chr(10)
|| 'Articles:' || chr(10)
|| (select listagg(a.article_id || ' ' || a.title || ': ' || a.body, chr(10))
from (select article_id, title, body
from kb_articles
order by vector_distance(embedding, t.embedding, cosine)
fetch first 2 rows only) a)
into l_prompt
from tickets t join customers c on c.customer_id = t.customer_id
where t.ticket_id = p_ticket_id;
l_draft := generate(l_prompt, 'GEMINI', json_object(
'systemInstruction' value json_object('parts' value json_array(
json_object('text' value c_instructions))),
'generationConfig' value json('{"temperature": 0.3,
"thinkingConfig": {"thinkingBudget": 0}}')
returning json));
insert into hd_replies (ticket_id, agent, draft)
values (p_ticket_id, p_user, l_draft)
returning reply_id into l_id;
return l_id;
end;
/Output:
Function HD_DRAFT_REPLY compiled
One instruction does most of the work of keeping drafts safe. The resolutions it learns from say "I have refunded it", and a draft that copied them would tell the customer money is on its way before anyone refunded anything. The instructions forbid saying an action was taken and ask for each step the agent must take in square brackets, which the agent cannot overlook in the reply box.
Example:
-- drafts a reply to ticket 9 for Emma Lindqvist of the billing team
declare
l_id number;
begin
l_id := hd_draft_reply(9, 'EMMA');
for r in (select draft from hd_replies where reply_id = l_id) loop
dbms_output.put_line(r.draft);
end loop;
end;
/Output:
Hi Leo, The second charge was a retry after a timeout at the payment gateway. [refund duplicate charge] Refunds appear on card statements within 5 to 10 business days, depending on the bank. You can find more information in article KB-201. Emma Lindqvist PL/SQL procedure successfully completed.
The refund is a step in brackets, the timing comes from KB-201, and the reply is signed. One sentence needs judgment: the cause comes from the similar tickets, not this one. It is very likely the same, but the agent should check the payment before stating it as fact.
Send the Reply and Measure the Draft
HD_SEND_REPLY records the reply as the agent's comment, stores what was sent, measures how much of the draft was kept with UTL_MATCH.EDIT_DISTANCE_SIMILARITY, and sets the ticket to Waiting for the customer.
Example:
-- sends a reply: records it as the agent's comment, measures how much of the draft was
-- kept, and sets the ticket to Waiting for the customer
create sequence ticket_comments_seq start with 1001;
create or replace procedure hd_send_reply (
p_reply_id in number,
p_text in clob
)
is
l_reply hd_replies%rowtype;
l_agent agents.name%type;
begin
select * into l_reply from hd_replies where reply_id = p_reply_id for update;
select max(name) into l_agent
from agents
where upper(substr(email, 1, instr(email, '.') - 1)) = upper(l_reply.agent);
insert into ticket_comments (comment_id, ticket_id, author_type, author_name, body,
created_at)
values (ticket_comments_seq.nextval, l_reply.ticket_id, 'Agent',
nvl(l_agent, l_reply.agent), p_text, systimestamp);
update hd_replies
set sent = p_text,
sent_at = systimestamp,
similarity = utl_match.edit_distance_similarity(dbms_lob.substr(draft, 4000),
dbms_lob.substr(p_text, 4000))
where reply_id = p_reply_id;
update tickets
set status = 'Waiting'
where ticket_id = l_reply.ticket_id and status in ('Open', 'In Progress');
end;
/Output:
Sequence TICKET_COMMENTS_SEQ created. Procedure HD_SEND_REPLY compiled
EDIT_DISTANCE_SIMILARITY compares two texts character by character: 100 means the draft was sent unchanged, and lower values mean more editing.
Build the Ticket Workspace Page
Create a blank page, number 12, named Ticket Workspace, with the icon fa-briefcase. In Page Designer, create a region Ticket with a Select List item P12_TICKET_ID, labeled Ticket, with Display Extra Values off, the null display value "- Select a ticket -", and Page Action on Selection set to Submit Page.
List of values of P12_TICKET_ID:
-- the list of values of P12_TICKET_ID: the tickets still being worked on
select ticket_id || ' - ' || subject as d, ticket_id as r
from tickets
where status in ('Open', 'In Progress', 'Waiting')
order by ticket_idThen create four Classic Report regions, each shown only when P12_TICKET_ID is not null: Details, Similar Resolved Tickets, Suggested Articles, and Conversation. The middle two use the queries above with :P12_TICKET_ID in place of 9.
SQL query of the Details region:
-- Details: the customer, the ticket, and its AI classification and summary
select c.company as "Customer", c.contact_name as "Contact",
t.description as "Description",
t.ai_category || ' / ' || t.ai_priority || ' / ' || t.ai_sentiment
as "Classification",
t.ai_summary as "Summary"
from tickets t join customers c on c.customer_id = t.customer_id
where t.ticket_id = :P12_TICKET_IDSQL query of the Conversation region:
-- Conversation: the comments of the ticket, oldest first
select to_char(created_at, 'YYYY-MM-DD HH24:MI') as "Date", author_name as "From",
body as "Message"
from ticket_comments
where ticket_id = :P12_TICKET_ID
order by created_atThe Reply Region
Create a region Reply with the same condition, containing a Textarea P12_REPLY labeled "Reply to the customer" with a height of 10, a hidden item P12_REPLY_ID, and two buttons in the Next slot: DRAFT, labeled Draft Reply, and SEND, labeled Send Reply, Hot, shown only when P12_REPLY_ID is not null. Two processes do the work, the second with the success message "The reply was sent to the customer."
PL/SQL code of the Draft the Reply process:
-- Draft the Reply :P12_REPLY_ID := hd_draft_reply(:P12_TICKET_ID, :APP_USER); select draft into :P12_REPLY from hd_replies where reply_id = :P12_REPLY_ID;
PL/SQL code of the Send the Reply process:
-- Send the Reply: the agent's text, as edited in P12_REPLY hd_send_reply(:P12_REPLY_ID, :P12_REPLY); :P12_REPLY := null; :P12_REPLY_ID := null;
The Workspace at Work
An agent of the billing team opens the workspace and chooses ticket 9.
Everything is on one page: the customer's ticket with its AI classification, the three resolved tickets with their resolutions, and KB-201. The agent chooses Draft Reply.
The agent refunds the charge in the billing system, replaces the bracket with "I have refunded the second charge today.", and chooses Send Reply.
A second agent opens ticket 85, about an API returning 429 Too Many Requests. The draft comes mostly from the rate-limit article: it lists the limits of all three plans and ends with a bracket that is no action at all, [Send reply to customer]. The agent knows the customer is on the Enterprise plan and rewrites the reply around its limit.
Measure the Drafts
Example:
-- the drafts so far: which were sent, and how much of each the agent kept
select r.reply_id, r.ticket_id, r.agent,
case when r.sent_at is null then 'Not sent' else 'Sent' end as status,
r.similarity as kept_percent,
length(r.draft) as draft_chars,
length(r.sent) as sent_chars
from hd_replies r
order by r.reply_id;Output:
REPLY_ID TICKET_ID AGENT STATUS KEPT_PERCENT DRAFT_CHARS SENT_CHARS
___________ ____________ _________ ___________ _______________ ______________ _____________
1 9 EMMA Not sent 259
23 9 EMMA Sent 88 244 267
24 85 DANIEL Not sent 542
25 85 DANIEL Sent 37 502 500The first agent kept 88 percent of the draft; the second kept 37 percent, after drafting twice. Over weeks, the same query by agent, category, or product shows where drafts save work. A low number is a question, not an answer: read drafts and replies side by side. Here they say two things: the prompt lacked the customer's plan, and the model put a non-action in brackets.
Improve the Prompt
The second version adds the customer's plan to the prompt and reserves brackets for actions on the customer's account.
Example:
-- drafts a reply from the resolutions of similar tickets and the nearest articles
-- (second version: the customer's plan, and steps only for actions on the account)
create or replace function hd_draft_reply (
p_ticket_id in number,
p_user in varchar2
) return number
is
c_instructions constant varchar2(1000) :=
'You draft replies for the support agents of Atlas Software. Write to the customer by '
|| 'first name. Use only the facts in the earlier resolutions and the articles. Never '
|| 'say that an action on the account was taken; write each such action the agent must '
|| 'take in square brackets, like [refund issued], and nothing else in brackets. Use '
|| 'the customer''s plan where it matters. Mention an article by its ID when you '
|| 'use it. '
|| 'Plain text, at most 120 words, signed with the agent''s name.';
l_agent agents.name%type;
l_prompt clob;
l_draft clob;
l_id hd_replies.reply_id%type;
begin
-- the agent, from the user name: EMMA is emma.lindqvist@atlas.example
select max(name) into l_agent
from agents
where upper(substr(email, 1, instr(email, '.') - 1)) = upper(p_user);
select 'Agent: ' || nvl(l_agent, 'Atlas Support') || chr(10)
|| 'Customer: ' || c.contact_name || ', ' || c.company
|| ', plan ' || c.plan || chr(10)
|| 'Ticket: ' || t.subject || chr(10) || t.description || chr(10) || chr(10)
|| 'Resolutions of similar tickets:' || chr(10)
|| (select listagg('- ' || r.resolution, chr(10))
from (select (select c2.body
from ticket_comments c2
where c2.ticket_id = s.ticket_id and c2.author_type = 'Agent'
order by c2.created_at desc
fetch first 1 row only) as resolution
from tickets s
where s.ticket_id <> t.ticket_id
and s.status in ('Resolved', 'Closed')
order by vector_distance(s.embedding, t.embedding, cosine)
fetch first 3 rows only) r) || chr(10) || chr(10)
|| 'Articles:' || chr(10)
|| (select listagg(a.article_id || ' ' || a.title || ': ' || a.body, chr(10))
from (select article_id, title, body
from kb_articles
order by vector_distance(embedding, t.embedding, cosine)
fetch first 2 rows only) a)
into l_prompt
from tickets t join customers c on c.customer_id = t.customer_id
where t.ticket_id = p_ticket_id;
l_draft := generate(l_prompt, 'GEMINI', json_object(
'systemInstruction' value json_object('parts' value json_array(
json_object('text' value c_instructions))),
'generationConfig' value json('{"temperature": 0.3,
"thinkingConfig": {"thinkingBudget": 0}}')
returning json));
insert into hd_replies (ticket_id, agent, draft)
values (p_ticket_id, p_user, l_draft)
returning reply_id into l_id;
return l_id;
end;
/Output:
Function HD_DRAFT_REPLY compiled
Drafting ticket 85 again and comparing with what the agent sent:
Example:
-- drafts ticket 85 again with the second version, and compares the draft with what
-- Daniel Okafor sent
declare
l_id number;
l_draft clob;
l_sent clob;
begin
l_id := hd_draft_reply(85, 'DANIEL');
select draft into l_draft from hd_replies where reply_id = l_id;
select sent into l_sent from hd_replies where ticket_id = 85 and sent is not null;
dbms_output.put_line(l_draft);
dbms_output.put_line('');
dbms_output.put_line('Kept: ' || utl_match.edit_distance_similarity(
dbms_lob.substr(l_draft, 4000),
dbms_lob.substr(l_sent, 4000)) || '%');
end;
/Output:
Hello Omar, On your Enterprise plan, the REST API limit is 3,000 requests per minute. When this limit is exceeded, the API returns a 429 status code along with a Retry-After header that specifies how many seconds to wait before retrying. To help prevent hitting this limit, you can use our bulk endpoints, which accept up to 100 records per call and count as a single request, and make sure your sync honors the Retry-After header. You can find more details in article KB-602. Please let us know if you have any other questions. Daniel Okafor Kept: 50% PL/SQL procedure successfully completed.
The new draft answers for the Enterprise plan, as the agent did, has no bracket because no account action is needed, and is 50 percent similar to the sent reply, up from 37. Edit distance measures wording, not truth: two correct replies can share few characters. Use the number to find the drafts worth reading, and the reading to decide what to change.
Conclusion
An AI help desk workspace in Oracle APEX puts the nearest resolved tickets with their resolutions and the nearest articles beside each ticket, all from vector search, and drafts a reply from them. Forbid the draft from claiming actions and mark each step the agent must take in brackets, let the agent edit every draft before sending, measure how much of each draft is kept, and improve the prompt from what the measurements point to.
