How to Find Knowledge Base Gaps with AI in Oracle APEX

The questions a knowledge assistant could not answer, and the answers customers marked as unhelpful, are a to-do list for the support team. But "not found" alone does not say what to do. The distance from each question to the nearest source does: a near source means the knowledge base has the subject and the article needs work, a far one means a new article is missing.

This guide turns the recorded questions of an APEX knowledge assistant into a Knowledge Gaps page in Oracle APEX 26.1: a query that ranks failed questions by their nearest source, an interactive report for the team, and a function that asks Gemini for proposed changes.

Code for This Guide

The examples are files 05 and 06 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.

They read the KA_QUESTIONS table recorded by the assistant in how to build a knowledge assistant in Oracle APEX, and search the KNOWLEDGE view of articles and document chunks.

Rank Failed Questions by Their Nearest Source

This query lists the questions that were not found or did not help, each with its nearest source in the knowledge and the distance, nearest first.

Example:

-- the questions the assistant could not answer, or whose answer did not help,
-- with the nearest source in the knowledge, nearest first
begin
  for r in (select case when q.helpful = 'N' then 'Not helpful' else q.outcome end reason,
                   n.distance, n.source, q.question
            from   ka_questions q
            cross  apply (select k.source,
                                 vector_distance(k.embedding, q.embedding, cosine) distance
                          from   knowledge k
                          order  by distance
                          fetch  first 1 row only) n
            where  q.outcome = 'Not found' or q.helpful = 'N'
            order  by n.distance) loop
    dbms_output.put_line(to_char(r.distance, '0.000') || '  '
                         || rpad(r.reason, 12) || r.source);
    dbms_output.put_line('        ' || r.question);
  end loop;
end;
/

Output:

 0.227  Not found   Article KB-701
        Atlas Sync has shown 'Syncing' for two days. What should I do?
 0.294  Not found   Article KB-102
        How do I reset my password if the e-mail never arrives?
 0.314  Not helpful Article KB-602
        What happens when we go over the API rate limit?
 0.356  Not found   Atlas CRM 8.4.1, part 1
        Does Atlas CRM have an add-in for Outlook?
 0.409  Not found   Article KB-204
        If I downgrade my plan in the middle of the year, do I get money back?
 0.451  Not found   Atlas Billing 5.2 User Guide, part 4
        Can I pay my invoices with PayPal?
 0.564  Not found   Atlas Mobile 3.9 Guide, part 1
        Is there a dark mode in Atlas Mobile?
 0.648  Not found   Atlas Billing 5.2 User Guide, part 4
        Can I pay with PayPal?

PL/SQL procedure successfully completed.

Read from the top, the list holds every kind of gap:

With this embedding model, distances below about 0.35 mean the same subject. A near source with "not found" means an article to check or extend; a far one, an article to write. A related semantic join over tickets is shown in how to join tables by meaning with vector search in Oracle.

Build the Knowledge Gaps Page

Create page 11, Knowledge Gaps, as a blank page with the icon fa-search-minus, and in Page Designer:

1. Create an Interactive Report region Unanswered Questions with the query above as its SQL source, adding the question's ID, date, and user to the select list.
2. Create a Dynamic Content region Suggested Changes, with a button SUGGEST labeled Suggest Changes in the Next slot, and a hidden item P11_SUGGESTIONS.
3. Create a process Suggest Changes, for the button SUGGEST, with the code :P11_SUGGESTIONS := ka_suggest_articles;
4. Give the region the function body below, which shows the proposals escaped, one per line.

Region source of the Suggested Changes region:

-- the model's proposals, escaped, one per line
return replace(apex_escape.html(:P11_SUGGESTIONS), chr(10), '<br>');

The page is for the support team, not customers: in production, give it an Authorization Scheme only the team passes.

The interactive report lists the failed questions, nearest source first, and the team can filter by reason or customer, sort, and save their own views.

Ask the Model for Proposals

Reading the list and deciding what to write is work a model can start. KA_SUGGEST_ARTICLES sends the gaps, each with its nearest source and distance, and asks for at most five changes, each extending an article or proposing a new one.

Example:

-- proposes changes to the knowledge base from the questions it could not answer
create or replace function ka_suggest_articles return clob
is
  l_gaps clob;
begin
  select listagg('- ' || q.question || ' (nearest source: ' || n.source
                 || ', distance ' || to_char(n.distance, 'fm0.000') || ')', chr(10))
           within group (order by n.distance)
  into   l_gaps
  from   ka_questions q
  cross  apply (select k.source,
                       vector_distance(k.embedding, q.embedding, cosine) as distance
                from   knowledge k
                order  by distance
                fetch  first 1 row only) n
  where  q.outcome = 'Not found' or q.helpful = 'N';

  if l_gaps is null then
    return 'There are no unanswered questions.';
  end if;
  return generate(
    'Customers asked the Atlas support assistant these questions, and its knowledge base '
    || 'did not answer them well. Each question shows the nearest source in the knowledge '
    || 'base; a distance below 0.35 means that source is about the same subject.' || chr(10)
    || l_gaps || chr(10) || chr(10)
    || 'Propose at most five changes to the knowledge base. For each, write one line that '
    || 'starts with "Extend" and the name of a nearby article, or with "New article" and a '
    || 'title, followed by the questions it would answer. Plain text, no Markdown.',
    'GEMINI',
    json('{"generationConfig": {"thinkingConfig": {"thinkingBudget": 0}}}'));
end;
/
select ka_suggest_articles as suggestions;

Output:

Function KA_SUGGEST_ARTICLES compiled

SUGGESTIONS
__________________________________________________________________________________________________
Extend Article KB-701: Atlas Sync has shown 'Syncing' for two days. What should I do?
Extend Article KB-102: How do I reset my password if the e-mail never arrives?
Extend Article KB-602: What happens when we go over the API rate limit?
New article: Atlas Integrations and Add-ins: Does Atlas CRM have an add-in for Outlook?
New article: Billing, Refunds, and Payment Methods: If I downgrade my plan in the middle of the
year, do I get money back? Can I pay my invoices with PayPal? Can I pay with PayPal?

A first version left the model's thinking on. Its calls took 53 to 60 seconds, and on the page two reached the 60-second HTTP limit and failed with ORA-29273: HTTP request failed. With thinkingBudget 0, the proposals took 4 seconds: sorting a short list does not need long reasoning.

The proposals follow the distances: extend three articles, and write new ones on integrations and on billing and payment methods. Read them as a draft from a colleague who has seen the questions but not the articles. The proposal for KB-102 is unnecessary, because the article already answers its question, and the downgrade question is a policy for the billing team to state first. A person decides what goes into the knowledge base, and reviews it before the assistant can use it.

Conclusion

To find knowledge base gaps, rank the questions an assistant could not answer, or whose answers did not help, by the distance to their nearest source: near means an article to extend or check, far means an article to write. Show them in an interactive report for the support team, let a model with thinking off draft at most five proposed changes, and keep a person in charge of what is actually written.