Oracle APEX - Using user input text in an AI RAG Source - APEX$AI_LAST_USER_PROMPT

With APEX we can create a chatbot and connect it to AI data source. There are lots of good materials about it, but I was struggling with the problem how to pass the user input text as part of the RAG code. My colleague Yohay Asraf gave me the tip of using APEX$AI_LAST_USER_PROMPT that solved my problem. Thanks, Yohay!

In APEX we can now use AI options and configure RAG sources, Oracle 23ai Database features like SelectAI and OCI AI Agents. See links to few Labs at the end of the post.

In an APEX application under "Shared Components" - "Generative AI Configuration" I created a RAG query that uses the Vector option of the 23ai but how can we make it dynamic?

In my case, I loaded various car guides into a table and created chunks of the books and vectors from those chunks. Next, I'm looking for chunks of text that are similar to a specific text (Bla Bla Bla in the next example). 

In the example 

• I'm using existing Encoder named doc_model to convert the input string to vector and name it "embedding". 
• I compare the input vector to existing text chunks looking for minimal distance and limiting it to the selected Vehicle Make and model. 
• I return 4 best results.

 WITH query_vector AS (

            SELECT VECTOR_EMBEDDING(doc_model USING 'Bla Bla Bla' AS data) as embedding)

SELECT embed_id, TEXT_CHUNK

FROM WKSP_APEX.VECTOR_STORE, query_vector

Where VECTOR_DISTANCE(EMBED_VECTOR, query_vector.embedding, COSINE)<0.6

AND VEHICLE_MAKE=:P2_MAKE  AND VEHICLE_MODEL=:P2_MODEL

ORDER BY VECTOR_DISTANCE(EMBED_VECTOR, query_vector.embedding, COSINE)

FETCH APPROX FIRST 4 ROWS ONLY

How do I make the input string dynamic? Here we have 2 options 

• APEX$AI_LAST_USER_PROMPT - Retrieves the most recent user-entered prompt.
• APEX$AI_ALL_USER_PROMPTS - Retrieves all user prompts, concatenated into a single string.

 https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/managing-ai-configurations-and-rag-sources.html#GUID-D1274017-381C-4988-8E6E-36095BA06655

Here is the new code:

WITH query_vector AS (

            SELECT VECTOR_EMBEDDING(doc_model USING :APEX$AI_LAST_USER_PROMPT AS data) as embedding)

SELECT embed_id, TEXT_CHUNK

FROM WKSP_APEX.VECTOR_STORE, query_vector

Where VECTOR_DISTANCE(EMBED_VECTOR, query_vector.embedding, COSINE)<0.6

AND VEHICLE_MAKE=:P2_MAKE AND VEHICLE_MODEL=:P2_MODEL

ORDER BY VECTOR_DISTANCE(EMBED_VECTOR, query_vector.embedding, COSINE)

FETCH APPROX FIRST 4 ROWS ONLY

As you can see, we are using 2 RAG sources Car Problems table and a Books table.

There is a nice blog by Harry Snart that uses it - RAG based Chatbots made easy 

If you want to start using AI or RAG and APEX, there are many Blogs and great labs in Oracle LiveLabs. 

• This is a very basic one: Build an Innovative Q&A Interface Powered by Generative AI with Oracle APEX
• A RAG oriented one: AI Vector Search - RAG Application using AI Vector Search in Oracle Autonomous Database and APEX
• Or OCI AI Agents and APEX: Enhance your Oracle APEX App with Document-Aware Generative AI Agents