Ai-powered Document Search with Oracle and Onnx Embeddings for Recruiting

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "srbBVbSANqsLzygm",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "hybridsearchDemo",
  "tags": [],
  "nodes": [
    {
      "id": "7f6e0486-a00b-459d-9bb4-19d05af06316",
      "name": "When clicking \u2018Execute workflow\u2019",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1488,
        -848
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "bf015c88-32d6-46ef-8301-50d2c32e46c5",
      "cid": "Ikx1Y2FzIFBleXJpbiI",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "notes": "\u00a9 2025 Lucas Peyrin",
      "creator": "Lucas Peyrin",
      "position": [
        -2768,
        -1488
      ],
      "parameters": {
        "width": 1004,
        "height": 1472,
        "content": "# Oracle AI Hybrid Vector Search\n\n- This workflow automates the vector search alongside keyword search using the ***DBMS_HYBRID_VECTOR.SEARCH*** PL/SQL query API.\n- It shows how to use hybrid search in an HR Recruitment scenario, where you want to hire employees with specific technical skills (keyword search for \"C, Python, and Database\") but who also display certain personality and leadership traits (semantic search for \"prioritize teamwork and leadership experience\"). \n\n## How it works\n1. Create a user for doing Hybrid Search.\n2. Clear Existing Data, if present.\n3. Add Documents into the table.\n4. Create a hybrid index.\n5. Run ***Semantic*** search on Documents table for ***\"prioritize teamwork and leadership experience\"***.\n6. Run ***Hybrid*** search for the text inputted in Chat interface.\n\n## Setup steps\n**Download the ONNX model**\n\n[all_MiniLM_L12_v2_augmented.zip](https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/VBRD9P8ZFWkKvnfhrWxkpPe8K03-JIoM5h_8EJyJcpE80c108fuUjg7R5L5O7mMZ/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2_augmented.zip)\n\nExtract the ZIP file on the database server into a directory, for example `/opt/oracle/onnx`.\nAfter extraction, the folder contents should look like:\n\n```bash\nbash-4.4$ pwd\n/opt/oracle/onnx\nbash-4.4$ ls\nall_MiniLM_L12_v2.onnx\n```\n\n**Connect as SYSDBA and create the DBA user**\n\n\n```sql\n-- Create DBA user\nCREATE USER app_admin IDENTIFIED BY \"StrongPassword123\"\n  DEFAULT TABLESPACE users \n  TEMPORARY TABLESPACE temp \n  QUOTA UNLIMITED ON users;\n\n\n-- Grant privileges\nGRANT DBA TO app_admin;\nGRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO app_admin;\n```\n\n**Create n8n Oracle DB credentials**\n- hybridsearchuser \u2192 for hybrid search operations\n- dbadocuser \u2192 for DBA setup (user and tablespace creation)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "af3ac78d-58c1-4e6c-9c7f-114346e3953a",
      "name": "When chat message received",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -1488,
        -320
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.3
    },
    {
      "id": "348a0e46-876f-46c6-935a-3454bb108638",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1040,
        -1488
      ],
      "parameters": {
        "color": 7,
        "width": 816,
        "height": 368,
        "content": "## Create a local user and grant privileges\n\n- This step creates a new database user named ***docuser*** and assigns the necessary privileges to enable hybrid search operations. \n- It also provides the directory location where the ***ONNX*** model is loaded in the database.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "07e4ac13-969f-4821-b3bb-49073e8e0ca7",
      "name": "Create a user",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        -912,
        -1312
      ],
      "parameters": {
        "query": "BEGIN\n  -- Drop user if exists\n  BEGIN\n    EXECUTE IMMEDIATE 'DROP USER docuser CASCADE';\n  EXCEPTION\n    WHEN OTHERS THEN\n      IF SQLCODE != -1918 THEN  -- ORA-01918: user does not exist\n        RAISE;\n      END IF;\n  END;\n\n  -- Drop tablespace if exists\n  BEGIN\n    EXECUTE IMMEDIATE 'DROP TABLESPACE tbs1 INCLUDING CONTENTS AND DATAFILES';\n  EXCEPTION\n    WHEN OTHERS THEN\n      IF SQLCODE != -959 THEN  -- ORA-00959: tablespace does not exist\n        RAISE;\n      END IF;\n  END;\n\n  -- Create tablespace\n  EXECUTE IMMEDIATE q'[\n    CREATE TABLESPACE tbs1\n      DATAFILE 'tbs1.dbf' SIZE 1G\n      AUTOEXTEND ON\n      EXTENT MANAGEMENT LOCAL\n      SEGMENT SPACE MANAGEMENT AUTO\n  ]';\n\n  -- Create user\n  EXECUTE IMMEDIATE q'[\n    CREATE USER docuser IDENTIFIED BY docuser\n    DEFAULT TABLESPACE tbs1\n    QUOTA UNLIMITED ON tbs1\n  ]';\nEND;\n",
        "options": {},
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "ea50be65-2334-4772-a5b8-8cdc60a3eb00",
      "name": "ONNX Model Directory path",
      "type": "n8n-nodes-base.code",
      "position": [
        -704,
        -1312
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      model_dir: \"/opt/oracle/onnx/\"\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "1897566f-531f-4e29-8c8a-3783e25b8e21",
      "name": "Grant Privileges to user",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        -496,
        -1312
      ],
      "parameters": {
        "query": "DECLARE\n  v_modelDir VARCHAR2(4000) := :modelDir; -- bind variable or value\nBEGIN\n  EXECUTE IMMEDIATE 'GRANT DB_DEVELOPER_ROLE, CREATE CREDENTIAL TO docuser';\n  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY DEMO_DIR AS ''' || v_modelDir || '''';\n  EXECUTE IMMEDIATE 'GRANT READ, WRITE ON DIRECTORY DEMO_DIR TO docuser';\nEND;\n",
        "options": {
          "params": {
            "values": [
              {
                "name": "modelDir",
                "valueString": "={{ $json.model_dir }}"
              }
            ]
          }
        },
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "2556d56f-d4c8-4b17-8dc8-8631ce32662f",
      "name": "Setup the table, Reload ONNX model and clear old data",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        -16,
        -1312
      ],
      "parameters": {
        "query": "BEGIN\n  -- Drop ONNX model if exists\n  BEGIN\n    dbms_vector.drop_onnx_model(model_name => 'doc_model', force => TRUE);\n  EXCEPTION\n    WHEN OTHERS THEN\n      NULL; -- Ignore if model doesn't exist\n  END;\n\n  -- Load ONNX model\n  BEGIN\n    DBMS_VECTOR.LOAD_ONNX_MODEL(\n      'DEMO_DIR',\n      'ALL_MINILM_L12_V2.onnx',\n      'doc_model',\n      JSON('{\n        \"function\": \"embedding\",\n        \"embeddingOutput\": \"embedding\",\n        \"input\": { \"input\": [\"DATA\"] }\n      }')\n    );\n  END;\n\n  -- Create table if not exists\n  BEGIN\n    EXECUTE IMMEDIATE '\n      CREATE TABLE doc_tab (\n        id NUMBER,\n        text VARCHAR2(500)\n      )\n    ';\n  EXCEPTION\n    WHEN OTHERS THEN\n      IF SQLCODE != -955 THEN -- ORA-00955: name is already used by an existing object\n        RAISE;\n      END IF;\n  END;\n\n  -- Drop index if exists\n  BEGIN\n    EXECUTE IMMEDIATE 'DROP INDEX my_hybrid_idx';\n  EXCEPTION\n    WHEN OTHERS THEN\n      IF SQLCODE NOT IN (-1418, -1419, -1418, -1419, -1418, -1419, -1418, -1419) THEN\n        NULL; -- Ignore missing index errors\n      END IF;\n  END;\n\nEND;\n",
        "options": {},
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "6102864a-83e1-400a-9bb2-b3af4b968086",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        -1488
      ],
      "parameters": {
        "color": 7,
        "width": 496,
        "height": 368,
        "content": "## Setup the table, Reload ONNX model and clear old data\n\n- This step reloads the ONNX model, creates table ***doc_tab*** for storing the document contents and clears old table data and vector index.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4a5c7ae4-15b0-4c8b-a46d-c1fe3afae49f",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        400,
        -1488
      ],
      "parameters": {
        "color": 7,
        "width": 512,
        "height": 368,
        "content": "## Add the Documents\n\n- This step loads all the documents into table, ***doc_tab***.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "a79d4bf0-6b04-4f64-960d-eaf7c1861867",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        992,
        -1488
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 368,
        "content": "## Create Hybrid Index\n\n- An index named ***my_hybrid_idx*** is created on the text column of the ***doc_tab*** table. \n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2e5884db-f50f-4707-b477-380335320009",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1024,
        -960
      ],
      "parameters": {
        "color": 7,
        "width": 816,
        "height": 368,
        "content": "## Pure semantic search  in document mode \n\nThis is a pure vector-based similarity query to fetch document-level search results. Here, the search_text string is vectorized into a query vector for a ***VECTOR_DISTANCE*** semantic query.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b49000a3-e0f3-4705-847a-0ad0e63347b1",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1024,
        -512
      ],
      "parameters": {
        "color": 7,
        "width": 816,
        "height": 496,
        "content": "## Hybrid Search\n\nThis search text t (C, Python, Database)  provided in Chat interface is transformed into a ***CONTAINS*** query for keyword search, and is ***vectorized*** for a VECTOR_DISTANCE query for semantic search.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "e7316c5e-c178-4a0d-8b1d-7706c114770b",
      "name": "Generate Documents data",
      "type": "n8n-nodes-base.code",
      "position": [
        448,
        -1312
      ],
      "parameters": {
        "jsCode": "return [\n  {ID: 1, TEXT: 'Candidate-1: C Master. Optimizes low-level system (i.e. Database) performance with C. Strong leadership skills in guiding teams to deliver complex projects.'\n  },\n  {ID: 2, TEXT: 'Candidate-2: Full-Stack Developer. Skilled in Database, C, HTML, JavaScript, and Python with experience in building responsive web applications. Thrives in collaborative team environments.'\n  },\n  {ID: 3, TEXT: 'Candidate-3: DevOps Engineer. Manages CI/CD pipelines (Jenkins, Gitlab) with expertise in cloud infrastructure (OCI, AWS, GCP). Proven track record of streamlining deployments and ensuring high availability.'\n  },\n  {ID: 4, TEXT: 'Candidate-4: Database Administrator (DBA). Maintains and secures enterprise database (Oracle, MySql, SQL Server). Passionate about data integrity and optimization. Strong mentor for junior DBA(s).'\n  },\n  {ID: 5, TEXT: 'Candidate-5: C, Java, Python, and Database (DBA) Guru. Develops scalable applications. Strong leadership, teamwork and collaborative.'\n  }\n    ];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "7efb09bd-7c35-41f6-ab65-b8680801203e",
      "name": "Add Documents in to table",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        688,
        -1312
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "DOC_TAB",
          "cachedResultName": "DOC_TAB"
        },
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "DOCUSER",
          "cachedResultName": "DOCUSER"
        },
        "columns": {
          "value": {
            "ID": 0
          },
          "schema": [
            {
              "id": "ID",
              "type": "number",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ID",
              "defaultMatch": true
            },
            {
              "id": "TEXT",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "TEXT",
              "defaultMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "ID",
            "TEXT"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {}
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "4c39f326-a43f-4ec3-a7b7-970b4fcdb9b2",
      "name": "Create Hybrid Index",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        1136,
        -1312
      ],
      "parameters": {
        "query": "    CREATE HYBRID VECTOR INDEX MY_HYBRID_IDX\n    ON doc_tab(text)\n    PARAMETERS('model doc_model')\n\n",
        "options": {},
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "70b09847-5cfe-49f0-9ab3-c97cc8f7672b",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1648,
        -512
      ],
      "parameters": {
        "color": 7,
        "width": 416,
        "height": 496,
        "content": "## Hybrid Search\n\nThis search text (C, Python, Database)  provided in Chat interface is provided to perform Hybrid Search.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "18c92702-3318-4a98-8ccd-bd0482099869",
      "name": "Sticky Note11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1648,
        -1072
      ],
      "parameters": {
        "color": 7,
        "width": 416,
        "height": 496,
        "content": "## Semantic Search\n\nIt triggers the workflow to perform semantic search on the documents loaded.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "0afb5bed-1be8-4643-842a-967ca805b113",
      "name": "Text Input",
      "type": "n8n-nodes-base.code",
      "position": [
        -752,
        -800
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      message: \"prioritize teamwork and leadership experience\"\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "dc54168a-429d-421b-8bb8-cc0a8b669748",
      "name": "Perform Semantic Search",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        -544,
        -800
      ],
      "parameters": {
        "query": "SELECT jt.rowid_col, jt.chunk_text, jt.score\nFROM (\n  SELECT JSON_SERIALIZE(\n           DBMS_HYBRID_VECTOR.SEARCH(\n             JSON('{\n               \"hybrid_index_name\" : \"my_hybrid_idx\",\n               \"vector\": {\n                 \"search_text\" : \"{{ $json.message}}\",\n                 \"search_mode\" : \"DOCUMENT\",\n                 \"aggregator\"  : \"MAX\"\n               },\n               \"return\": {\n                 \"values\" : [\"rowid\", \"chunk_text\", \"score\"],\n                 \"topN\"   : 3\n               }\n             }')\n           )\n           RETURNING CLOB PRETTY\n         ) AS result_json\n  FROM dual\n) r,\nJSON_TABLE(r.result_json, '$[*]'\n  COLUMNS (\n    rowid_col VARCHAR2(64) PATH '$.rowid',\n    chunk_text VARCHAR2(500) PATH '$.chunk_text',\n    score     NUMBER       PATH '$.score'\n  )\n) jt\n\n\n",
        "options": {},
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "3dc37f34-fa98-4a58-b90c-e03b8f199613",
      "name": "Perform Hybrid Search",
      "type": "n8n-nodes-base.oracleDatabase",
      "position": [
        -912,
        -320
      ],
      "parameters": {
        "query": "SELECT DBMS_HYBRID_VECTOR.SEARCH(\n  JSON('{\"hybrid_index_name\": \"my_hybrid_idx\", \"search_text\": \"{{ $json.chatInput }}\"}')\n) AS result FROM dual\n",
        "options": {},
        "operation": "execute"
      },
      "credentials": {
        "oracleDBApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "85729e6f-db83-4b74-b997-de9f3106995d",
      "name": "Format the result",
      "type": "n8n-nodes-base.code",
      "position": [
        -720,
        -320
      ],
      "parameters": {
        "jsCode": "// Input: Oracle node output where item.json.RESULT is a JSON array string\nconst results = [];\n\nfor (const item of items) {\n  const outer = item.json.RESULT;\n  if (!outer) continue;\n\n  let inner;\n  try {\n    inner = JSON.parse(outer);\n  } catch (err) {\n    throw new Error('Failed to parse inner JSON: ' + err.message);\n  }\n\n  for (const row of inner) {\n    // Extract the candidate prefix (e.g., \"Candidate-2:\") and the rest\n    const match = row.chunk_text.match(/^(Candidate-\\d+):\\s*(.+)$/);\n    let candidateLabel, text;\n\n    if (match) {\n      candidateLabel = match[1];\n      text = match[2];\n    } else {\n      candidateLabel = \"Candidate\";\n      text = row.chunk_text;\n    }\n\n    // Split first sentence as \"title\", rest as \"summary\"\n    const [firstSentence, ...rest] = text.split('.');\n    const title = firstSentence.trim();\n    const summary = rest.join('.').trim();\n\n    // Format for chat\n    results.push({\n      json: {\n        message: `\ud83d\udc64 **${candidateLabel} \u2014 ${title}**\\n${summary}`,\n      },\n    });\n  }\n}\n\nreturn results;\n"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "fde7b71a-35ee-4fa8-9e32-d0ac94e6b19f",
  "connections": {
    "Text Input": {
      "main": [
        [
          {
            "node": "Perform Semantic Search",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create a user": {
      "main": [
        [
          {
            "node": "ONNX Model Directory path",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Hybrid Index": {
      "main": [
        []
      ]
    },
    "Perform Hybrid Search": {
      "main": [
        [
          {
            "node": "Format the result",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Documents data": {
      "main": [
        [
          {
            "node": "Add Documents in to table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Perform Semantic Search": {
      "main": [
        []
      ]
    },
    "Grant Privileges to user": {
      "main": [
        [
          {
            "node": "Setup the table, Reload ONNX model and clear old data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add Documents in to table": {
      "main": [
        [
          {
            "node": "Create Hybrid Index",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ONNX Model Directory path": {
      "main": [
        [
          {
            "node": "Grant Privileges to user",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When chat message received": {
      "main": [
        [
          {
            "node": "Perform Hybrid Search",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking \u2018Execute workflow\u2019": {
      "main": [
        [
          {
            "node": "Text Input",
            "type": "main",
            "index": 0
          },
          {
            "node": "Create a user",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Setup the table, Reload ONNX model and clear old data": {
      "main": [
        [
          {
            "node": "Generate Documents data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• oracleDBApi

About this workflow

Create a user for doing Hybrid Search. Clear Existing Data, if present. Add Documents into the table. Create a hybrid index. Run Semantic search on the Documents table for "prioritize teamwork and leadership experience". Run Hybrid search for the text input in the Chat interface…

Source: https://n8n.io/workflows/10273/ — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.