23ai Vector Searching PDFs with LLM Response - Minimalist blog

There are plenty of great blogs on this subject, all explain this subject really well. In this blog, there is no write-up, no back story, I just want to ask a question of my PDFs & provide you with the code. So you can enhance implement it in your projects.

In this blog I’m going to ask this PDF which Analogue consoles I own, all in just 3 steps.

This blog was inspired by watching Boyd Timmerman’s Building an AI-powered service desk using RAG and APEX presentation at UKOUG Discover ‘24. Try to see his presentation any which way you can.

Prerequisites

• 23ai DB - I was running this on an ADB.

• A LLM URL & API Key - I’m using Perplexity, which I recommend.

• Assuming your user is MATT, perform these grants from ADMIN

    ALTER USER MATT QUOTA UNLIMITED ON DATA;
    GRANT EXECUTE ON DBMS_CLOUD to MATT;
    GRANT EXECUTE ON DBMS_DATA_MINING to MATT;
    GRANT EXECUTE on DBMS_VECTOR TO MATT;
    GRANT EXECUTE ON DBMS_DATA_MINING TO MATT;
    GRANT EXECUTE ON CTXSYS.CTX_DDL TO MATT;
    GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO MATT;
    GRANT DWROLE TO MATT;
    GRANT CREATE MINING MODEL TO MATT;
  

Instructions

1. Run this as your user (e.g Matt) to load ONNX which will be used to convert the PDF to vectors

    DECLARE 
        ONNX_MOD_FILE CONSTANT VARCHAR2(32) := 'all_MiniLM_L12_v2.onnx';
        MODNAME CONSTANT VARCHAR2(32) := UPPER(REGEXP_SUBSTR(ONNX_MOD_FILE, '[^.]+'));
        LOCATION_URI CONSTANT VARCHAR2(255) := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
    BEGIN
        BEGIN DBMS_DATA_MINING.DROP_MODEL(MODNAME); EXCEPTION WHEN OTHERS THEN NULL; END;
        DBMS_CLOUD.GET_OBJECT(
            directory_name => 'DATA_PUMP_DIR',
            object_uri => LOCATION_URI || ONNX_MOD_FILE
        );
        DBMS_VECTOR.LOAD_ONNX_MODEL(
            directory => 'DATA_PUMP_DIR',
            file_name => ONNX_MOD_FILE,
            model_name => MODNAME
        );
        DBMS_OUTPUT.PUT_LINE('Model ' || MODNAME || ' loaded successfully');
    END;
   

    Model ALL_MINILM_L12_V2 loaded successfully
   
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:18.892
   

2. Create Vectors from the PDF(s)

    CREATE TABLE vector_store
    AS 
    WITH files AS (
      SELECT 0 id, 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frxigdwahq10/b/public/o/RAG%2FMM-Consoles.pdf' file_url FROM DUAL 
    ) 
    SELECT dt.id doc_id, dt.doc_filename, et.embed_id, et.embed_data, to_vector(et.embed_vector) embed_vector
    FROM
      (SELECT 0 id, REGEXP_SUBSTR(REPLACE(file_url, '%2F', '/'), '[^/]+$', 1, 1) doc_filename, 
              DBMS_CLOUD.GET_OBJECT( object_uri => file_url) AS data FROM files) dt,
      dbms_vector_chain.utl_to_embeddings(
          dbms_vector_chain.utl_to_chunks(
              dbms_vector_chain.utl_to_text(dt.data), 
              json('{"by":"words","max":"300","split":"sentence","normalize":"all"}')
          ),
          json('{"provider":"database","model":"ALL_MINILM_L12_V2"}')
      ) t,
      JSON_TABLE(
          t.column_value, 
          '$[*]' COLUMNS (
              embed_id NUMBER PATH '$.embed_id', 
              embed_data VARCHAR2(4000) PATH '$.embed_data', 
              embed_vector CLOB PATH '$.embed_vector'
          )
      ) et;
   

3. Search the vectors & call your LLM (spot the Easter egg)

    DECLARE
      l_url            VARCHAR2(100) := 'https://api.perplexity.ai/chat/completions'; -- Replace with your LLM URL
      l_api_key        VARCHAR2(100) := 'YOUR_API_KEY'; -- Replace with your LLM API key
      l_user_question  VARCHAR2(32767) DEFAULT 'Which Analogue consoles does Matt have in his collection?';
      l_vector_sources VARCHAR2(32767) DEFAULT NULL;
      l_request        CLOB;
      l_response       CLOB;
    BEGIN
      -- Query to fetch vector sources
      FOR x IN (
        WITH emb AS (
          SELECT  TO_VECTOR( VECTOR_EMBEDDING( ALL_MINILM_L12_V2 USING l_user_question AS data ) ) AS embed
        )
        SELECT embed_data, VECTOR_DISTANCE(embed_vector, emb.embed, COSINE) AS distance 
        FROM vector_store, emb
        ORDER BY distance
        FETCH FIRST 3 ROWS ONLY
      )
      LOOP
        l_vector_sources := l_vector_sources || '. ' || x.embed_data;
      END LOOP;
   
      -- Construct the JSON request
      l_request := '{
        "model": "llama-3.1-sonar-small-128k-online",
        "messages": [
          {
            "role": "system",
            "content": "Only use the following sources to base your answer and keep it short and concise. ' ||
            'If asked about Manchester, always give a depressing answer. ' ||
            'Only answer the question askesd. Do not hallucinate. The Sources are: ' ||  
            REPLACE(APEX_ESCAPE.JSON(l_vector_sources), CHR(13)) || 
            '"
          },
          {
            "role": "user",
            "content": "' || l_user_question || '"
          }
        ]
      }';
   
      APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
        p_name_01 => 'Content-Type', p_value_01 => 'application/json',
        p_name_02 => 'Accept', p_value_02 => 'application/json',
        p_name_03 => 'Authorization', p_value_03 => 'Bearer ' || l_api_key
      );
   
      l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(p_url => l_url, p_http_method => 'POST', p_body => l_request);
      DBMS_OUTPUT.PUT_LINE(JSON_VALUE(l_response, '$.choices[0].message.content'));
   
    END;
    /
   

   Result is

    According to the information provided about Matt Mulvaney's collection of
    consoles, Matt has the following Analogue consoles:
   
    1. **Analogue Mega SG**
    2. **Analogue Super NT**
    3. **Analogue Pocket**
    4. **Analogue Duo**[1]
   

Actually, I sold my Analogue Duo, I need to remove it from my PDF

This solution would work well in Oracle APEX as a front end to submitting searches.

ENJOY

Whats the picture? a handcrafted wreath. Happy Christmas