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. 

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.