A Picture Is Worth A Thousand Words: Dashboards To The Rescue

Building an application in the age of AI assisted development is only half the battle.

With an APEX application hosted on OCI, monitoring resource consumption is vital, because with the free tier you cannot exceed certain volumes of requests, whether they are REST requests or emails or notifications.

A well-crafted dashboard, beyond just eye-catching aesthetics, can help you spot problems that might otherwise go unnoticed until the application runs out of resources and stops working. 

The position of the big blue circle shows there is something wrong with the embedding calls 

Spotting anomalies at a glance:

A prime example of a dashboard's utility is how it highlights hidden inefficiencies and runaway processes.
In the API requests visualization, in the lower left portion of the image, a massive blue circle was dominating the chart, immediately drawing my attention.

• The big blue bubble instantly signals an anomaly in the system's traffic pattern. 
• The position of its centre on the Y axis compared to the position of other types of requests over the week reveals that the application is currently executing a disproportionate number of embedding requests to the LLM. 
• Looking at the chart I could immediately start investigating the rogue document processing procedure.

SELECT 
  -- Chronological X: 6 = 6 days ago, 0 = Today
  to_char(w.request_date,'Day') as dy,
  (trunc(w.request_date) - trunc(sysdate)) as x_value,
  nvl(regexp_substr(w.url,'(http(s)?:/)(/([^/]+))+',1,1,'c',4), 'UNKNOWN') as series,
  count(*)                                     as requests,    -- Y-axis
  sum(elapsed_sec)                             as elapsed,     -- Bubble radius
  to_char(w.request_date,'Day') || '
Requests: ' || count(*) || '
Time: ' || sum(elapsed_sec) as custom_tooltip
FROM apex_webservice_log w
WHERE w.request_date >= trunc(sysdate) - 6 and w.request_date < trunc(sysdate) + 1
GROUP BY to_char(w.request_date,'Day'),
         (trunc(w.request_date) - trunc(sysdate)), 
         nvl(regexp_substr(w.url,'(http(s)?:/)(/([^/]+))+',1,1,'c',4), 'UNKNOWN')
ORDER BY x_value desc, series;

Dashboards can bridge the gap between hundreds of log entries in an APEX internal view and the need to extract and present the significant information in an efficient and concise way. 

Last but not least, AI can suggest the critical resources to monitor and how to best represent the information with a chart if you are short of ideas.

Defending Your PL/SQL Code: Why Anchoring with %TYPE Saves the Day

You are loading an associative array and you are getting the following error: 

ORA-06502: PL/SQL: value or conversion error: 
associative array key violates its type constraints 

image created by Google Gemini

When I asked Gemini for this error message it said it had to do with a null value in the key but this is false, if the key value is null you'll get a different error message:

ORA-06502: PL/SQL: value or conversion error: NULL index table key value 

And there is also another interesting fact, if you explicitly assign an array element indexed by varchar2(n), in this fashion:

g_arr('') := 'something';

no error whatsoever, the whole array is wiped out, as if you executed g_arr.delete, and it's not clear to me if this is a bug or expected behavior.

So let's see the real reason for "associative array key violates its type constraints":

Create table config (config_key varchar2(50), config_value varchar2(500));

insert into config values('some_key', 'some value');
insert into config values('some_key_with_a_long_name', 'some other value');

declare
  type t_associative_array is table of varchar2(500) index by varchar2(10);
  g_arr      t_associative_array;
  k          varchar2(50);
begin
    FOR rec in (select config_key, config_value from config)
    LOOP
        g_arr(rec.config_key) := rec.config_value;
    END LOOP;

    k := g_arr.first;
    WHILE k IS NOT NULL LOOP
    DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
    k := g_arr.NEXT(k);
  END LOOP;
 
end;
/

And bum!

ORA-06502: PL/SQL: value or conversion error: 
associative array key violates its type constraints 

The problem is that you declared the associative array key to be INDEX BY VARCHAR2(10), but the value coming from table is longer.

This type of problems can easily happen when you add new values over time whose length can be longer than original ones and you were a bit lazy when you wrote the code, declaring some variables with explicit data types to save some typing.

To avoid surprises you can rewrite the script as follows:

declare
  type t_associative_array is table of config.config_value%type
                                       index by config.config_key%type;
  g_arr      t_associative_array;
  k          config.config_key%type;
begin
    FOR rec in (select config_key, config_value from config)
    LOOP
        g_arr(rec.config_key) := rec.config_value;
    END LOOP;

    k := g_arr.first;
    WHILE k IS NOT NULL LOOP
    DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
    k := g_arr.NEXT(k);
  END LOOP;
 
end;
/

This time it will output the values without errors.

'some_key':'some value'
'some_key_with_a_long_name':'some other value'

Statement processed.

Is this error specific for associative array key ?
Yes it is, if the problem is in the length of the value, you will get this error instead:

ORA-06502: PL/SQL: value or conversion error: character string buffer too small

This distinction is good because it pinpoints the root cause of the problem.

Last but not least: watch out for the column and variable length semantics!

If you store the keys containing some variable length characters and you defined the associative array using byte semantics using explicit data types, you could incur in the same errors and be confused as the length of the key or the value seem to be within the valid range.

The best solution, and best practice by the way, is to define the array and the variables as shown above, referring to the table columns definition.

RAG Time with APEX for #JoelKallmanDay

What is the best approach when it comes to execute a semantic search for RAG (Retrieval Augmented Generation) in OllAPEX ?

OllAPEX is an Oracle APEX workbench designed for local LLM integration via Ollama, with semantic search over documents (such as Oracle skills repositories or excerpts from Oracle documentation) amongst other functions.

The main purpose of semantic search report applied to the Oracle Skills or other Oracle documents is to test a search before implementing a RAG mechanism inside OllAPEX itself. 

The Real-World Context: Semantic Search in OllAPEX

In OllAPEX, semantic search allows users to query vector-embedded markdown files directly from an APEX interface.

               [ User Input Topic / Query ]
                           │
                           ▼
          ollapex.semantic_search(:TOPIC, ...)
                           │
                           ▼
          JSON_TABLE (Extract Top N Chunks)
                           │
                           ▼
    Join with OLLAPI_DOCUMENT_CHUNKS & UPLOADS
                           │
                           ▼
           [ Render Results in a report  ]  

The underlying SQL query calls a PL/SQL vector search function ollapex.semantic_search(), parses the returning JSON payload via JSON_TABLE, and joins the parsed rows with document chunk metadata tables (ollapi_document_chunks and ollapi_uploads_for_embedding). Here is the user-facing application displaying semantic search results for queries over chunks of Oracle skills in markdown format: 

picture #1, semantic search report

The natural execution:

The initial version of the query behind the report looked like this:

picture 2, semantic search query

And its execution plan:

picture 3, execution plan for the initial semantic search query

Then I thought: how can I ensure that the CTE is executed only once as I don't want that the prompt embedding function is called more times than necessary ?

The wannabe "optimized" query: forcing MATERIALIZE 

To ensure the vector search function in the CTE executes only once, I added a /*+ MATERIALIZE */ hint:

picture 4, query with MATERIALIZE hint added

And this is the execution plan that I got: 

picture 5, execution plan for the semantic search query with MATERIALIZE

As you see the cost is slightly increased and now there is a FULL scan on the temporary table created by the MATERIALIZE hint. No big deal but it doesn't look like an improvement to me.

But wait, is this the best way to tackle this problem?

The good old mantra: keep it easy!

In the end if I want to be sure that the embedding function is executed only once and there are no dependencies from what follows, then I am better off separating the embedding phase from the search phase:

dynamic action doing the embedding before running the report

So when the user clicks on the search button, a dynamic action performs the embedding on user's prompt and returns the results as a JSON array (CLOB).

The final query eventually uses the vector to perform the semantic search:

final semantic search query

 

Since there are no vector indexes defined (yet), the execution plan remains identical to that of picture 3, thus performing a so-called "exact" similarity search but now I am 100% sure that the embedding request will be performed just once.

Have a nice #JoelKallmanDay, remembering Joel, Scott and Carl.