Building an AI-Powered Resume Matching System Using Oracle APEX and 23ai Vector Search😎

I am going to Develope an AI-powered Resume Match system using 23ai vector search in an Oracle APEX application to  to semantically compare project descriptions with personnel resumes. The system calculates and ranks the percentage of relevance between each project and candidate profile.

During this process you will learn about Oracle AI Vector Search and Similarity Search

 During this process, you'll gain hands-on experience with:

• Oracle AI Vector Search

• Semantic Similarity Search

• Integrating ONNX models

• Oracle APEX Interactive Reports and Charts

👀In the video below on my YouTube Channelhttps://www.youtube.com/@LidaGholizadeh_ORACLE_GIS, you can see the steps of Building an AI-Powered Resume Matching System Using Oracle APEX and 23ai Vector Search

🟎Overview of Steps

Before creating APEX app you need 2 steps as below :

1) Create an Oracle DB23ai on Oracle Cloud 

Use Oracle Cloud Infrastructure (OCI) to create a 23ai-compatible Oracle Database instance. For a step-by-step tutorial, refer to the guide below:

https://lidagholizadeh.blogspot.com/2025/06/create-database-in-oracle-cloud-and.html

2) Load the ONNX model in your Database

Upload and register your ONNX model in the database to enable vector embedding generation using vector_embedding().

Follow the setup guide here:

https://lidagholizadeh.blogspot.com/2025/06/vector-search.html

😎Now you are ready to create an APEX application AI-Powered Resume Matching System😎

Step 1)  Create a New Page 

        Page Name: Personal_Profile

Step 2)  Add the Prompt Input

• Component Type: Region with a Textarea item
• Item Name: P5_PROMPT
• Label: Prompt : Project Description
• Region Name: Prompt

 Step 3) Create Similarity Search Report Region

• Region Name: Report_result_prompt

• Subregion: table_similaritySearch

• Type: Interactive Report

• SQL Query:
  The query calculates cosine similarity between the prompt and resume vectors stored in the database. Top 5 matches are retrieved:

  SELECT  candidate_name,
          1 - COSINE_DISTANCE(
                  resume_vector,
                  vector_embedding(MY_MODEL USING :P5_PROMPT AS data)
          ) AS similarity_score,ROUND((1 - COSINE_DISTANCE(
                  resume_vector,
                  vector_embedding(MY_MODEL USING :P5_PROMPT AS data)
          )) * 100, 2) AS similarity_percentage
  FROM    personal_profile
  ORDER BY similarity_score DESC
  FETCH   FIRST 5 ROWS ONLY;

• Highlighting Rule:
  Rows with similarity_percentage >= 50 are highlighted with light blue (#7ec7dd).

Step 4)  Add a Chart to Visualize Results

• Region Name: chart_similaritySearch

• Chart Type: Bar Chart

• Data Source: Same query as above

• Bar Color:

• Blue (#7ec7dd) if similarity ≥ 50

• Gray (#e8e8e8) otherwise

SELECT  candidate_name,

        1 - COSINE_DISTANCE(

                resume_vector,

                vector_embedding(MY_MODEL USING :P5_PROMPT AS data)

        ) AS similarity_score,ROUND((1 - COSINE_DISTANCE(

    resume_vector,

                vector_embedding(MY_MODEL USING :P5_PROMPT AS data)

        )) * 100, 2) AS similarity_percentage

 ,CASE WHEN ROUND((1 - COSINE_DISTANCE(

                resume_vector,

                vector_embedding(MY_MODEL USING :P5_PROMPT AS data)

        )) * 100, 2)>=50 THEN '#7ec7dd' ELSE '#e8e8e8' END CASE AS COLOR

FROM    personal_profile

ORDER BY similarity_score DESC

FETCH   FIRST 5 ROWS ONLY;

• 

Step 5)  Show Full Resume Table

• Region Name: Personal_Profile

• Type: Interactive Report

• SQL Query: Displays detailed resume data, including:

  • CANDIDATE_NAME

  • RESUME (download link)

  • RESUME_TEXT, RESUME_VECTOR, DATE_CREATE, ...

• Highlighting Rule:
  If a resume matches the prompt (MATCHED_RESUME = 1), highlight the row.

---

Step 6)  Create Similarity Search Button and Logic

• Button Name: SimilaritySearch

• Trigger: On click

• Actions Performed:

1. Show Report_result_prompt region

2. Refresh the table and chart regions

3. Run a PL/SQL block that:

• Queries resumes with similarity ≥ 50%

• Concatenates matching candidate names into :P5_TEST

• Used to update the MATCHED_RESUME column in the report

Step 7) Automatically Process New Resumes

• Event Triggered: When Dialog (Page 6) is closed after editing/creating a resume

• PL/SQL Process:
  For any resume without resume_text, use CTX_DOC.FILTER to extract text and HTML from the resume, and compute the vector embedding:

  
  UPDATE personal_profile
  SET resume_text_html = v_text_html,
      resume_text = v_text,
      date_create = SYSDATE,
      resume_vector = vector_embedding(MY_MODEL USING v_text AS data)
  WHERE id = rec.id;
  

Thanks for your Reading I hope it was useful for you!