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. 

Capture VS Code requests to Ollama using MITM reverse proxy

Have you ever wondered what exactly your VS Code AI extensions are whispering to your local models? By setting up a Man-in-the-Middle (MITM) proxy, you can peek behind the curtain and capture every prompt generated by your editor.

 

Using mitmdump, we can create a reverse proxy in front of a local Ollama instance (which runs on port 11434) and record the incoming traffic:

Bash

mitmdump --mode reverse:http://localhost:11434 -p 11435 -w ~/Documents/ollama_requests.log

By pointing your VS Code extension to localhost:11435 instead of the default port, all HTTP traffic is silently intercepted and saved to your log file.

Next, we convert this binary log into a human-readable format, cranking up the detail level to capture the full request payloads:

Bash

mitmdump -r ollama_requests.log --set flow_detail=3 > today.txt

Now you have a raw text file containing every prompt and system instruction sent by VS Code. The final step is to feed today.txt back into an LLM. You can ask it to summarize your daily coding activity, analyze the hidden system prompts injected by your extensions, or extract recurring development patterns. It is a fantastic way to reverse-engineer how AI assistants structure their context.

Dealing with JSON serialization and how to convert JSON object strings back and forth

A simple reminder about how JSON PL/SQL methods deal with JSON values, it easy to get confused when you mix up JSON objects and their serialized counterparts, especially if some if these parts are coming from JSON SQL functions and you need to combine them with other parts generated by PL/SQL.

The code below should clarify the difference between a "real" JSON value and its textual representation, especially when you are assembling a JSON object with values containing other JSON objects or their serialized representation.

When you PUT a serialized JSON string into a new JSON, the method escapes all the special characters that otherwise would break the syntax (lines 10-16).
In order to reconstruct a valid JSON string, something that you can PARSE as JSON, you need to retrieve the value with GET_STRING or GET_CLOB if it is large (lines 18-20).

If you need to include a serialized JSON object into a new JSON object thus avoiding the automatic escaping, then you need to convert the serialized JSON string into a proper JSON object and then PUT it inside the new object (lines 26-29). 

declare
    c  varchar2(255) := '{"key": 1, "value": "X"}';
    d  varchar2(255);
    j  json_object_t;
    j1 json_object_t;
    j2 json_object_t;
begin
    
    if c is json then
        dbms_output.put_line('c is a string containing a valid JSON');
        j := json_object_t.parse(c);

        j1 := new json_object_t;
        j1.put('document', c);
        dbms_output.put_line('c is now escaped and becomes a string literal value');
        dbms_output.put_line(j1.to_string());
        dbms_output.new_line;
        d := j1.get_string('document');
        dbms_output.put_line('c is converted back into the original JSON object string');
        dbms_output.put_line(d);


        dbms_output.new_line;
        dbms_output.new_line;

        j2 := new json_object_t;
        j2.put('document', j);
        dbms_output.put_line('c is still a json object value, now embedded in a new JSON object');
        dbms_output.put_line(j2.to_string());
    else
        dbms_output.put_line('NOT JSON');
    end if;
end;
/

Watch out for NULL values because GET_STRING and GET_CLOB show two different behaviors in older releases of Oracle 19c.