Applying Artificial Intelligence

Asking Questions of Your Data and Seeing Results Immediately

The Interactive Report region offers filtering, sorting, conditional highlighting, grouping, charting, pivoting, computed fields and aggregates, and more. Your end users can gradually learn its features as they need them, but there's a simpler way.

By enabling Natural Language support on any Interactive Report in your app, users can explain what data they want to see in their own words and carry on a conversation with the report. APEX uses your app’s configured AI Service to understand the request and apply Interactive Report features that produce the result. The features applied are always visible, inspectable chips identical to the functionality an Interactive Report power user could have done manually.

The figure below shows an Interactive Report in a Movies app with Natural Language Support enabled. The developer configured Search with AI as the default search experience in the search field. The usual Row Search is also available from the dropdown at the start of the search field, but the Search with AI is smart enough to handle both row search and natural language prompts.

The report updates to show films in those requested genres, with an appropriate report filter applied. As shown below, you can see the filter "chip" for the Genre Name field above the results area.

This visibility is fundamental to confidence in the results. The Assistant does not return a standalone answer with no evidence of how it was produced. It maps Lucy’s request to ordinary Interactive Report features she can see, inspect, change, or remove. The chips above the report show the working: which filters, sorts, highlights, charts, breaks, and other settings produced the current result.

Clicking on the Assistant button in the toolbar, a chat window opens at the end of the report. It shows Lucy's first request, and the Assistant's reply:

I reset the report and filtered it to show only Drama, Action, and Crime films. [Assistant]

In the Assistant chat area, Lucy continues the conversation to refine her search results:

Refine to show the films released in the last 5 years, sort on release date, showing the most recent first and only R rated movies. [Lucy]

The Assistant updates the report automatically to apply additional filters with the requested sort applied.

Next Lucy asks:

Highlight movies from Paramount in light blue and Warner in gold. [Lucy]

The Assistant adds multiple conditional highlight features to the report and the results appear.

To better understand the filtered results, Lucy asks to:

Create a pie chart of movie count by studio [Lucy]

A chart view appears, and the usual table and chart toolbar buttons appear to let Lucy toggle between the two views of her data.

Since this is an Interactive Report, Lucy could save the current report with a meaningful name and then revisit this view of the data at any time in the future by selecting it from her saved reports list.

Lucy changes her mind, and now asks:

Can you reset the report and include the franchise column, list the films that are part of a franchise and break on that column. [Lucy]

The Assistant removes the existing report features, shows the requested column, uses it to separate films that share the same franchise into groups.

Lucy proceeds:

Reset the report and show all movies from the 80s that have won an Oscar and include the award column after title. [Lucy]

The Assistant replaces previous report features with appropriate new filters and the requested results appear.

For a final refinement, Lucy asks:

Refine and show movies made in the Big Apple and show the location column after Title [Lucy]

If Assistant needs Lucy to clarify something, it prompts her for a clarification:

Which column should I use for "Big Apple" (city) – is it "Movie City Locations Json" (NYC/New York City) in your report? [Assistant]

Lucy responds "Yes" and the report updates to show the subset of existing films that were filmed in New York City.

At any time while chatting with the Assistant, Lucy can expand or collapse the feature "chips" area of the report to inspect what Interactive Report features her prompts have produced. This makes the result transparent rather than opaque. She can continue to use the report in the normal way, adding or removing any features with the Actions menu. The Assistant is always aware of the report’s current settings. If the user adds, removes, or changes features, the Assistant stays in sync when interpreting the next request.

Understanding What Is Sent to the AI Service

When the Assistant uses the AI service to understand the user's prompt, it also sends the current report settings, column names and labels, and additional context you can add in Page Designer. The extra report and column context descriptions can help the AI Service better understand the kinds of questions users may ask about the report.

The Assistant never sends application data to the AI Service. Its only job is to understand the prompt and available columns names, and additional context, then translate the request into one or more existing Interactive Report features to apply. It produces report settings, not untraceable answers.

As a result, users can only see data they are already allowed to see, using features they could have applied manually. Natural language just gives them an easier way to do it.

Enabling Natural Language Support and Configuring Additional Context

To enable Search with AI functionality on an Interactive Report, as shown below, set its Natural Language Support switch on. The Default Search Mode property controls the report search field's initial behavior. If set to Row Search, then users access Search with AI in the search field dropdown. Conversely, when Search with AI is the default users see AI-powered search by default. Users can always toggle to the other mode using the dropdown, as well as search on a specific field from there, too.

Add Report Context information to help the Search with AI Assistant handle the user’s prompt. As shown below, the Movies region configures the following report context description:

This Interactive Report displays comprehensive movie data from a film industry database spanning multiple decades. The report includes box office performance, production details, cast information, and financial metrics. Users commonly analyze trends by genre, studio performance, director success rates, budget vs. revenue relationships, and temporal patterns in the film industry. The data supports filtering by release periods, financial thresholds, geographic markets, and categorical attributes. Key analytical use cases include identifying blockbuster patterns, ROI analysis, franchise performance tracking, and market trend analysis across different time periods and demographics.

Sometimes a column's name and label suffices for the AI service to understand a column's purpose. If your testing indicates additional context is needed on a particular column, as shown below, you can add that Column Context as needed. For example, the DIRECTOR_NAME column has the following information added:

Context: Primary director of the film. Common Use: Filmmaker analysis, director performance tracking, auteur studios. AI Hints: Users may refer to famous directors by last name only ("Spielberg", "Scorsese"). Support partial name matching, Handle "directed by" phrases naturally.

You can also optionally define additional Reference Data to send to the AI service. For example, the Movies Interactive Report below adds a SQL Query to identify up to 100 director names.

select director_name d, director_name r
  from mve_directors
 where rownum < 100

Defining an Agent's Goal

After defining an AI Service, create an AI Agent to handle an app-specific goal using artificial intelligence. It collaborates automatically with the AI service you configure to process a request and produce a response. An end user or your business logic supplies the input.

You define your agent's mission with a system prompt in natural language. For example, if the goal is providing an HR Assistant that can answer questions for HR representatives, a simple prompt might be:

You are a friendly Woods HR assistant.
You only answer questions about Woods HR employees and no others.

You configure the unique aspects of meeting the goal, and the APEX engine handles interacting with the AI Service to answer requests.

Adding Tools for App Data and Allowed Actions

AI Tools let your AI Agent expose additional capabilities that help the AI Service to achieve a goal. When responding to a user prompt, the AI Service can call your agent's tools to retrieve additional data or perform tasks.

Each tool has a name, and may have a description and parameters. Your agent runs any Augment System Prompt tools on the initial exchange with the AI Service, and gives the service a list of On Demand tool descriptions. You implement tools using SQL, PL/SQL, or JavaScript. Any tool you configure can have a Server-side condition to be included only when relevant.

Exploring the HR Assistant Agent's Two AI Tools

For example, consider the on-demand tool get_employee_info shown below. It lets the AI service retrieve employee information. As shown below, you include a description of the tool in natural language and define the parameters the AI Service can supply. Notice this tool includes two optional parameters ENAME and JOB, both of type VARCHAR2. The tool Type is set to Retrieve Data, so you can implement the tool using a SQL query.

On the Settings tab, as shown below, you describe the data the tool returns in natural language and provide the SQL query that can reference the parameters as bind variables. Notice that since the parameters are optional, the query uses them if they have a value to filter on employee name and job. The query statement is:

SELECT e.ename, e.job, e.sal, e.hiredate, d.dname
FROM   emp e
JOIN   dept d ON d.deptno = e.deptno
WHERE  (:ENAME IS NULL OR e.ename = UPPER(:ENAME))
AND    (:JOB   IS NULL OR e.job   = UPPER(:JOB))
ORDER BY e.sal DESC

A second give_raise tool lets the AI service increase an employee's salary by a certain percent. This one sets Type to Execute Server-side Code, specifies a natural language description, and defines P_EMPNO and P_PCT required NUMBER parameters.

You implement an Execute Server-side Code tool using PL/SQL or server-side JavaScript. As shown below, the code appears on the Settings tab. It ensures the percent parameter supplied is within the range of 0 to 40, and if so attempts to update the salary of the employee identified by the P_EMPNO parameter value. If the update is successful, it sets the result message to inform the AI service of the new salary. If the percent value is out of range, or the employee is not found, it sets a helpful error message instead. It calls set_tool_result in the APEX_AI package to provide the message APEX returns to the AI service as the tool invocation result.

declare
    l_pct     number := nvl(:P_PCT,0);
    l_new_sal number;
    l_result  varchar2(255);
begin
    if     l_pct > 0
       and l_pct <= 40
    then
        update emp
           set sal = sal * (1 + to_number(nvl(:P_PCT,0)/100))
         where empno = :P_EMPNO
         returning sal into l_new_sal;
        l_result := case
                        when sql%rowcount > 0
                        then 'New salary is '||l_new_sal
                        else 'No employee '||:P_EMPNO||' found'
                    end;
    else
        l_result := 'Percent must be > 0 and <= 40';
    end if;
    apex_ai.set_tool_result(l_result);
end;    

Witnessing Tools in Action in an Agent-Driven Chatbot

The Woods HR application uses role-based authorization to show HR representatives like Susan an HR Assistant button in the Navigation Bar. Clicking it, an Assistant chatbot dialog opens where Susan gets help using natural language about the employees who work at Woods. As shown below, she asks:

Which clerk earns the most? [Susan]

The assistant replies:

The highest-paid clerk at Woods HR is MILLER, earning $1,300. [Assistant]

Susan continues:

Which employee earns the least? [Susan]

The assistant says:

The employee who earns the least is SMITH, with a salary of $800. [Assistant]

Susan asks the assistant to increase Smith's pay:

Raise smith's salary by 10 percent [Susan]

The assistant answers:

I couldn't apply the 10% raise because the system requires Smith's employee number, and I only have his name/salary – not his ID. Can you provide Smith's employee number, or should I look up Smith again to retrieve it? [Assistant]

The conversation illustrates the chatbot is using the HR Assistant AI Agent and calling the get_employee_info and considering calling the give_raise tools as needed. When HR reps complain about manually typing in the employee number to perform salary changes, you identify the problem.

The get_employee_info tool's SQL query does not include the EMPNO column in the returned data. You change the tool's query to select EMPNO as well, and update its Data Description to mention employee id. Then, the chatbot can complete the task with no further input. In the example conversation below, if Susan asks the HR assistant to try again, this time the chatbot replies:

Done – SMITH (EmpNo 7369) received a 10% raise. Salary is now $880. [Assistant]

Understanding the Lifecycle of an Agent-Driven Chatbot Request

The diagram below illustrates the flow of control from the time an end user asks a question in the browser like "Which clerk earns the most?" to when they see the answer. The APEX engine runs any Augment System Prompt tools before it sends the request to the AI Service. Their results accompany the AI Agent's system prompt in the first message sent to the service's Large Language Model (LLM).

If the AI Service has everything it needs, it may respond directly. If not, it may call one or more of your agent's On Demand tools. For example, the LLM may request to invoke the get_employee_info tool, passing in "clerk" for the value of the tool's JOB parameter.

The APEX engine runs any tools the LLM requests to invoke, passing in parameters the service provides. APEX responds with your tools' results. This "agent loop" conversation continues until the AI Service decides to respond with the final answer. In this example, the service returns the response "MILLER earns the most at 1300", and APEX returns it to the browser where the user sees it appear as the answer to their question.

As Susan carries on a conversation of multiple messages and responses, APEX accumulates the conversation history. Since the LLM is stateless, APEX sends the full history with every request to the LLM so it has the full context to reason over. If later in the conversation Susan asks to raise Smith's salary by 10%, the same request lifecycle occurs. The only difference in that case is that the LLM requests to invoke the give_raise tool instead.

Deciding Between Execution Points

Your AI Agent tool's Execution Point determines when it runs: On Demand or Augment System Prompt.

The APEX engine runs your agent's On Demand tools only when the AI Service requests them. In contrast, Augment System Prompt tools run before your agent sends the initial request to the AI Service. Any results become additional system prompts that precede user input.

You can configure a Server-side condition on both kinds of tools. When an On-Demand tool's condition is false, it is omitted from the list of tool descriptions sent to the AI service. When an Augment System Prompt tool's condition is false, that tool is skipped and adds no data to the initial request.

Any tool can perform Retrieval Augmented Generation (RAG). If your agent's goal benefits from up front information that will always be useful to prepare an answer, then use an Augment System Prompt tool. Otherwise, create a well-described On Demand tool instead. Define appropriate parameters and return just the information the LLM needs, when it decides it is useful to have it.

Understanding Native Tool Types

APEX offers the three native tool types:

• Retrieve Data

  • Returns data based on a SQL Query, Function Body returning a CLOB, or static text.

• Execute Server-side Code

  • Performs server-side logic using PL/SQL or JavaScript (MLE) code. Override the implicit "success" result with set_tool_result in the APEX_AI package.

• Execute Client-side Code

  • Request end user-input or call browser APIs in an async context. Override the implicit "success" return by returning another string.

You can use all tool types for an on-demand tool. To augment the system prompt, choose either Retrieve Data or Execute Client-side Code.

Specifying Tool Parameters

On demand tools can declare named parameters with optional description, and specify which if any is required. Supported data types include VARCHAR2, CLOB, NUMBER and BOOLEAN. Tools using SQL or PL/SQL reference parameter values as bind variables (:PERSON_ID). In JavaScript, they are available via this.data (this.data.PERSON_ID).

Ensuring a Human is "In the Loop" Before Invoking a Tool

Each AI Tool can insist that the end user is aware of and approves its invocation. If you set the Requires Confirmation switch on in the User Approval section of your tool definition, then the end user is alerted if the LLM requests the tool, and the tool is only executed if the end-user approves.

Using an Agent Declaratively and Programmatically

Your Show AI Assistant and Generate Text With AI dynamic actions can reference an AI Agent, as can your Generate Text with AI page process or workflow activity. For programmatic use, pass the AI Agent's static ID to APEX_AI package APIs. If the AI Service calls your agent's tools, APEX automatically runs them. Client-side tools are allowed when dynamic actions like Show AI Assistant, or Generate Text With AI initiate the request from the browser.

Extending Native AI Tool Types with Plug-ins

The "Generative AI Tool" plug-in type lets you create your own reusable tools to extend the built-in set.

Understanding the Potential for User Frustration When Searching

Users know what they are looking for, but might search for it using words that are different than the ones in your tables. When their search finds no results, that can be frustrating. For example, suppose Lucy brings her family to Las Vegas while she attends a conference during the day. She wants to find family-friendly activities to do with the kids in the evenings, so she uses your Vegas Family Nights app to search for ideas.

After reading Unified Search Results Across Sources, you created a page with a Search region to give user's like Lucy an internet-search experience to find fun things to do together. As shown below, your Activities Search Configuration relates to your AIW_ACTIVITIES table with all activities Las Vegas has to offer that are appropriate for children. You identify TITLE, DESCRIPTION, IMAGE_DESCRIPTION, and TAGS as good columns to search over.

However, when Lucy types in her search for "place with sea life swimming around us", as shown below she sees that no activities match her search.

Lucy tries another search for "location where we can see illuminated old hotel signs", but again her search produces no activities. She leaves your application feeling frustrated.

Capturing the Meaning of Words Using Vectors

An AI service provider trains a large language model on an enormous set of text or image inputs. Then they fine-tune the model to excel at recognizing and classifying new inputs based on its training data. For some new input, the model applies its training to "understand" it. In the process, it evaluates potentially hundreds or thousands of different traits and assigns the input a "score" for each trait it has been trained to recognize.

For example, imagine training an AI model on millions of family friendly activities from around the world. Fine-tuning can incorporate professional activity planner expertise, helping the model suggest activities from natural language phrased the way an average parent might ask. For a family activity, traits might include what the experience is like and who it suits, such as activity type, age range, indoor or outdoor setting, energy level, educational value, time commitment, cost, distance from the Strip, accessibility, noise level, food availability, physical effort required, and weather sensitivity.

The model assigns a number between 0 and 1 for each of these traits, capturing how strongly a particular activity expresses that quality. For instance, a food availability score of 0 means no food available, while a score of 1 would indicate the activity is absolutely food-centric. You can write down the sequence of an activity's trait scores as an ordered list of numbers called a vector. The Oracle AI Database 26ai database supports a VECTOR data type to store these "scorecards" in a column. It also indexes vectors efficiently to quickly identify ones that are closest in meaning to a vector representing the intent of a user's search.

Incorporating a Vector into Your Existing Table

Your database contains an AIW_ACTIVITIES table. You can alter the table to add a SEMANTIC_CONTENT column of type VECTOR to store the list of trait scores the AI model infers for each activity. That vector encodes the model’s understanding of each activity's complete profile: a compact numeric summary of all the trait scores the AI recognized about it.

Installing a Model in Your Database

begin
    dbms_vector.load_onnx_model(
        model_name => 'DOC_MODEL',
        model_data => apex_web_service.make_rest_request_b(
                          p_http_method => 'GET',
                          p_url => 'TODO')),
                                   -- ^ URL from Blog Article Here
        metadata => json(q'~
                    {
                       "function" : "embedding",
                       "embeddingOutput" : "embedding" ,
                       "input":{"input": ["DATA"]}
                    }~')));
end;

Defining a Vector Provider

A vector embedding represents a model’s understanding of the input as coordinates in a multi-dimensional space of traits. APEX simplifies working with an external or DB-resident model that provides vector embeddings to capture data semantics. You supply the info it needs to use the model by defining a vector provider in the APEX Builder under Workspace > All Workspace Utilities > Vector Providers. After defining a vector provider, you reference it in calls to the GET_VECTOR_EMBEDDINGS function in the APEX_AI package, or when you define search configurations that power a Search region in your page.

Generating the Vector Embeddings for Your Data

To generate a vector for text input, use the GET_VECTOR_EMBEDDINGS function in the APEX_AI package. Pass it the text to encode and the static ID (e.g. vegas-family-nights) of the vector provider to use.

For example, to encode the meaning of the activity's title, description, image description, and tags, use an UPDATE statement like the one below. It uses the indicated vector provider to compute the SEMANTIC_CONTENT vector for each row based on the contents of each activity's TITLE, DESCRIPTION, IMAGE_DESCRIPTION, and TAGS columns.

begin
   apex_util.set_workspace('COMPANION');
   update aiw_activities
      set semantic_content =
            apex_ai.get_vector_embeddings(
              p_value =>
                'Title: ' || title || chr(13) ||
                'Description: ' || description || chr(13) ||
                'Feature Image Description: ' || image_description ||
                'Tags: ' || tags,
              p_service_static_id =>'vegas-family-nights');
end;

Enabling Searching by Meaning in a Page

To let end users search by meaning using Vector Search, start by defining an Activities Vector search configuration under Shared Components. Choose the type: Oracle AI Vector Search. You configure the vector column as shown below. For AIW_ACTIVITIES, it's the SEMANTIC_CONTENT vector column you added and populated with vector embeddings for your existing activity data.

Then, reference the Activities Vector search configuration as the source of a Search region. Your existing search page already has one, but it currently uses a standard search configuration that searches over regular column data. As shown below, just switch the Search Source to use the Activities Vector search configuration instead.

With this minimal effort, your page now offers Vegas visitors an internet-style "search by meaning" experience over family-friendly activities. At runtime, APEX uses the GET_VECTOR_EMBEDDINGS function in the APEX_AI package to convert the user's search text into a vector that encodes the meaning of their search. Then it runs a query to find the activities whose vector is closest in meaning to the user's search. As shown below, now Lucy finds what she's looking for. The Shark Reef Aquarium activity is the first hit.

Understanding Vector Distance

When a user enters a search, APEX first computes the vector that encodes the meaning of their search terms. Then it queries the AIW_ACTIVITIES table, with an ORDER BY clause to sort results by the vector distance between the user's search vector and the activity vectors in your table. Oracle 26ai's VECTOR_DISTANCE() function computes how close in meaning an activity is to the search vector. By also using FETCH FIRST N ROWS ONLY in the query, APEX finds only the "top N" results most similar in meaning to the user's search.

By temporarily enabling the Custom Layout switch in the Appearance section of the Search region's Attributes tab, you can add a reference to &DISTANCE. to display the vector distance computation for the results shown. Trying Lucy's other search, the results with distance displaying next to the title appear below. Notice the first result is spot on. When Lucy's searches for "location where we can see illuminated old hotel signs", the Neon Museum comes up first with a semantic distance of 0.464.

The results also show two other activities that are semantically father away from the meaning of her search. The Sphere is a giant spherical projection screen inside and out, so it technically is a kind of illuminated sign. And the Twilight Zone Mini Golf description mentions "glow-in-the-dark" and "backlight-lit", so it also seems somewhat illumination-related. Sometimes the semantic relevance of a result may be less obvious to the end user, to the point of possibly being confusing: Hmmmm. Why mini golf?

To find only the most relevant results with the strongest semantic connection, you can refine your vector search strategy. First, find the result closest in meaning to the user's search. Then, only include other results whose semantic distance from the search vector falls within a reasonable threshold around the best result. Think of this approach as a "best-score proximity cutoff".

Applying a Best-Score Proximity Cutoff Approach

To implement a best-score proximity cutoff, you first need to find the best score: the closest result's distance from the search vector. This is the minimum vector distance between the user's search vector and the activity rows' SEMANTIC_CONTENT vectors. Assuming a 10% cutoff from the best result and that p_search_text contains the user's search text, the query looks like this. It computes 10% more than the minimum vector distance between the user's search vector and activities' SEMANTIC_CONTENT vector. Notice doc_model is the name of the ONNX model installed in the database, used to produce the

select min(
        vector_distance(
          apex_ai.get_vector_embeddings(
            p_value             => p_search_text,
            p_service_static_id => 'vegas-family-nights'),
          a.semantic_content)) * 1.10 /* 10% more than minimum */
  from aiw_activities a

To make this best activity distance calculation more maintainable, you can create a scalar SQL macro like the BEST_ACTIVITY_DISTANCE one below. It lets you define a reusable fragment of SQL syntax that produces a scalar result.

Like a PL/SQL function, a macro can accept parameters with default values. The macro returns text the database includes into a SQL statement at runtime, wherever a scalar value is allowed. For example, in a WHERE clause predicate. The macro text result can include verbatim references to the macro parameter names, which the database handles as bind variables at query execution time.

create or replace function best_activity_distance(
    p_search_text in varchar2,
    p_factor      in number default 1.10
) return varchar2
    sql_macro(scalar)
is
begin
    return q'[
        (select min(
                  vector_distance(
                    apex_ai.get_vector_embeddings(
                      p_value             => p_search_text,
                      p_service_static_id => 'vegas-family-nights'),
                    a.semantic_content)) * p_factor
            from aiw_activities a)
    ]';
end;

With the BEST_ACTIVITY_DISTANCE macro defined, you can apply it to vector search results. The APEX_SEARCH package contains a SEARCH function that returns the results of a Search Configuration for a given user's search text. To limit the results to those within 10% of the "most similar in meaning" one, use a query like the one below.

Notice it includes the search configuration's vector DISTANCE score column, then limits results with a WHERE clause to rows whose semantic distance falls within the best-score proximity cutoff. Since it omits the second parameter to best_activity_distance, that defaults to a 10% range. A Content Row region's Order By Clause gets configured as a separate property highlighted below.

select primary_key_1, title, description, icon_blob, icon_mimetype, distance
  from table(
         apex_search.search(
             p_search_expression => :P5_SEARCH,
             p_search_static_ids => apex_t_varchar2('activities-vector')))
 where distance <= best_activity_distance(:P5_SEARCH)

The figure shows this query in use in a Results Content Row region. Users type their search into a P5_SEARCH field in the Search Field slot of the Breadcrumb bar. A When Enter Pressed dynamic action refreshs the Results region when the user pressed the [Enter] key. The page uses the technique explained in Including BLOB Images in Content Row to size the avatar images in the results.

Notice that in addition to configuring the query, Page Items to Submit is set to P5_SEARCH because the query references it as bind variable. Also observe the Order By Clause is the static distance column to sort on vector search distance, closest results first.

The result shows how Lucy now sees only the most relevant results for her search.

When she searches for any activities where she would "dangle from a wire flying between buildings high in the air", your app now presents the obvious winner for the family activity on Saturday: Fly LINQ Zipline.

The result is not necessarily just a single activity. If Lucy wants to "swing around on a giant mechanical arm high above the city" two very relevant results appear. The kids decide to start slow with the view from atop the Observation Wheel, then graduate to something even more thrilling.

For more information about using the Search region and search configurations, see Unified Search Results Across Sources.