{"id":1503,"date":"2025-04-16T01:18:42","date_gmt":"2025-04-16T01:18:42","guid":{"rendered":"https:\/\/adrianotanaka.com.br\/?p=1503"},"modified":"2025-04-16T01:32:45","modified_gmt":"2025-04-16T01:32:45","slug":"loading-an-onnx-model-for-oracle-ai-vector-search","status":"publish","type":"post","link":"https:\/\/adrianotanaka.com.br\/index.php\/2025\/04\/16\/loading-an-onnx-model-for-oracle-ai-vector-search\/","title":{"rendered":"Loading an ONNX Model for Oracle AI Vector Search"},"content":{"rendered":"\n<p>While preparing for the Oracle AI Vector Search Certified Professional Certification (1Z0-184-25), one of the most fascinating topics I encountered was Using Vector Embeddings, particularly with the new VECTOR DBMS\/Packages. Here, I\u2019ll share an example of how to load an ONNX model into your Oracle Database.&nbsp;<\/p>\n\n\n\n<p>For reference, I\u2019m running Oracle 23ai Free Edition and obtaining models from Hugging Face.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Converting an ONNX Model for Oracle Database&nbsp;<\/h2>\n\n\n\n<p>First, you cannot use ONNX models directly downloaded from Hugging Face. Before loading them into Oracle, you must convert them using OML4Py. Here\u2019s a helpful tutorial on how to download and configure it:&nbsp;<a href=\"https:\/\/blogs.oracle.com\/machinelearning\/post\/oml4py-leveraging-onnx-and-hugging-face-for-advanced-ai-vector-search\">OML4Py Tutorial &#8211; Leveraging ONNX and Hugging Face for AI Vector Search.&nbsp;<\/a><\/p>\n\n\n\n<p>Requirements for Conversion&nbsp;<\/p>\n\n\n\n<p>To use OML4Py, ensure you have:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Python and pip (version 3.12 or newer).&nbsp;<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.oracle.com\/database\/technologies\/oml4py-downloads.html\">The OML4Py client OML4Py <\/a><\/li>\n<\/ul>\n\n\n\n<p>This is crucial, because if you attempt to load an ONNX model directly using <strong>DBMS_VECTOR.LOAD_ONNX_MODEL<\/strong>, you&#8217;ll likely encounter the following error:&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ORA-54426: Tensor \"input_ids\" contains 2 batch dimensions.<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Converting and Exporting an ONNX Model in OML4Py&nbsp;<\/h2>\n\n\n\n<p>Within OML4Py, you can either:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use a preconfigured model from a provided list (<strong>ONNXPipelineConfig.show_preconfigured()<\/strong>).&nbsp;<\/li>\n\n\n\n<li>Download and customize a model manually (which I\u2019ll demonstrate below).&nbsp;<\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/vecse\/onnx-pipeline-models-text-embedding.html\">Here\u2019s the official Oracle documentation example, using ONNXPipeline and ONNXPipelineConfig <\/a>(available from OML4Py v2.1):&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>from oml.utils import ONNXPipeline, ONNXPipelineConfig\nconfig   = ONNXPipelineConfig.from_template(\"text\", max_seq_length=256,distance_metrics=&#91;\"COSINE\"], quantize_model=True)\npipeline = ONNXPipeline(model_name=\"thenlper\/gte-small\",config=config)\npipeline.export2file(\"testeModel22\", output_dir=\".\")\n<\/code><\/pre>\n\n\n\n<div class=\"wp-block-group is-layout-constrained wp-block-group-is-layout-constrained\">\n<p>To get the correct model_name, visit the Hugging Face model page and copy the name from the header.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"419\" height=\"63\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-14.png\" alt=\"\" class=\"wp-image-1509\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-14.png 419w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-14-300x45.png 300w\" sizes=\"auto, (max-width: 419px) 100vw, 419px\" \/><\/figure>\n<\/div>\n\n\n\n<p><\/p>\n\n\n\n<p>Checking Model Size<\/p>\n\n\n\n<p>Your ONNX model must be under 1GB, otherwise, the script will fail with a &#8220;Killed&#8221; status, to verify the size, check the Files tab on Hugging Face before downloading.&nbsp;<\/p>\n\n\n\n<p>After a successful export, you should find an .onnx file in the specified `output_dir`:&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-rwxrwxrwx 1 tanaka tanaka  87M  your_preconfig_file_name.onnx\n-rwxrwxrwx 1 tanaka tanaka 977K  tok_your_preconfig_model_name2.onnx\n-rwxrwxrwx 1 tanaka tanaka 977K  tok_testeModel.onnx\n-rwxrwxrwx 1 tanaka tanaka 4.9M  tok_testeModel2.onnx\n-rwxrwxrwx 1 tanaka tanaka  33M  testeModel22.onnx\n-rwxrwxrwx 1 tanaka tanaka  87M  all-MiniLM-L6-v2.onnx\n-rwxrwxrwx 1 tanaka tanaka 128M  all_MiniLM_L12_v2.onnx\n<\/code><\/pre>\n\n\n\n<p>Files starting with tok_ are broken and cannot be used.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Importing the ONNX Model into Oracle Database&nbsp;<\/h2>\n\n\n\n<p>Now that the model is ready, follow these steps to import it into your Oracle Database:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a user for model management.&nbsp;<\/li>\n\n\n\n<li>Create a Database Directory for model storage.&nbsp;<\/li>\n\n\n\n<li>Grant READ permission to the user for this directory.&nbsp;<\/li>\n\n\n\n<li>Import the model using DBMS_VECTOR.LOAD_ONNX_MODEL.&nbsp;<\/li>\n<\/ul>\n\n\n\n<p>Example:&nbsp;<\/p>\n\n\n\n<p><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL('DM_DUMP', 'testeModel22.onnx', 'doc_model23334');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Verifying Model Import with Oracle Tools&nbsp;<\/h2>\n\n\n\n<p>If you\u2019re curious about the background process, use oratop to monitor commands executed behind the scenes.<\/p>\n\n\n\n<p>The official Oracle documentation provides details on views created during this import process.<\/p>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"45\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041-1024x45.png\" alt=\"\" class=\"wp-image-1529\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041-1024x45.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041-300x13.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041-768x34.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041-1536x67.png 1536w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211041.png 1848w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"186\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108-1024x186.png\" alt=\"\" class=\"wp-image-1530\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108-1024x186.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108-300x54.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108-768x139.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108-1536x278.png 1536w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211108.png 1848w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"398\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140-1024x398.png\" alt=\"\" class=\"wp-image-1531\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140-1024x398.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140-300x117.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140-768x299.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140-1536x597.png 1536w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-211140.png 1851w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Using the Imported ONNX Model for Vector Embeddings\/Search&nbsp;<\/h2>\n\n\n\n<p>Now that our model is inside the Oracle Database, let\u2019s start embedding text vectors.&nbsp;<\/p>\n\n\n\n<p>Create a Table for Embeddings\u00a0\u00a0\u00a0<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>create table testVector (palavra varchar2(100), embed vector);\n\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"214\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-213618-1024x214.png\" alt=\"\" class=\"wp-image-1534\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-213618-1024x214.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-213618-300x63.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-213618-768x160.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-213618.png 1160w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Inserting Data Using the Imported ONNX Model&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>insert into TESTVECTOR values ('hello',VECTOR_EMBEDDING(doc_model2333 USING 'hello' as DATA));\n\ninsert into TESTVECTOR values ('house',VECTOR_EMBEDDING(doc_model2333 USING 'house' as DATA));\n\ninsert into TESTVECTOR values ('blue',VECTOR_EMBEDDING(doc_model2333 USING 'blue' as DATA));\n\ninsert into TESTVECTOR values ('car',VECTOR_EMBEDDING(doc_model2333 USING 'car' as DATA));\n\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"446\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-214816-1024x446.png\" alt=\"\" class=\"wp-image-1535\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-214816-1024x446.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-214816-300x131.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-214816-768x335.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-214816.png 1306w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Retrieving Data Based on Vector Similarity&nbsp;<\/h2>\n\n\n\n<p>To query similar embeddings, use VECTOR_DISTANCE:&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select  palavra,vector_distance(embed, (vector_embedding(doc_model2333 using 'PUT_YOUR_WORD_HERE' as data))) as distance\nfrom testVector\norder by distance;\n<\/code><\/pre>\n\n\n\n<p>Lower distance = Higher similarity&nbsp;<\/p>\n\n\n\n<p>For example, embedding &#8220;blue&#8221; may return results where &#8220;red,&#8221; &#8220;orange,&#8221; and &#8220;green&#8221; are more closely associated colors rather than random words.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"635\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215120-1024x635.png\" alt=\"\" class=\"wp-image-1537\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215120-1024x635.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215120-300x186.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215120-768x476.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215120.png 1089w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"636\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215243-1024x636.png\" alt=\"\" class=\"wp-image-1538\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215243-1024x636.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215243-300x186.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215243-768x477.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215243.png 1069w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"609\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215313-1024x609.png\" alt=\"\" class=\"wp-image-1539\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215313-1024x609.png 1024w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215313-300x178.png 300w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215313-768x457.png 768w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/Screenshot-2025-04-15-215313.png 1118w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>It&#8217;s crucial to consistently use the same model for both embedding and retrieving data to ensure accurate and meaningful results. This is the true power of embeddings\u2014they grasp the meaning and semantics of your query, allowing your data to be efficiently structured and retrieved based on relevance rather than just exact matches.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Bonus Tip: Limiting Query Results&nbsp;<\/h2>\n\n\n\n<p>Use FETCH EXACT FIRST XXX ROWS to limit rows in your query output:&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"294\" src=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-22.png\" alt=\"\" class=\"wp-image-1522\" srcset=\"https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-22.png 624w, https:\/\/adrianotanaka.com.br\/wp-content\/uploads\/2025\/04\/image-22-300x141.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p>Hope this post will help you and if you have any question, feel free to get in touch or <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/vecse\/sql-functions-generate-embeddings.html\">read the doc<\/a>.<\/p>\n\n\n\n<p>&#8212;<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>While preparing for the Oracle AI Vector Search Certified Professional Certification (1Z0-184-25), one of the most fascinating topics I encountered was Using Vector Embeddings, particularly with the new VECTOR DBMS\/Packages. Here, I\u2019ll share an example of how to load an ONNX model into your Oracle Database.&nbsp; For reference, I\u2019m running Oracle 23ai Free Edition and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1542,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"material-hide-sections":[],"footnotes":""},"categories":[52,6,59],"tags":[62,4,60,61],"class_list":["post-1503","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-certifications","category-oracle","category-vector","tag-23ai","tag-database","tag-ora-54426","tag-vector"],"_links":{"self":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1503","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=1503"}],"version-history":[{"count":20,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1503\/revisions"}],"predecessor-version":[{"id":1541,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1503\/revisions\/1541"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/media\/1542"}],"wp:attachment":[{"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=1503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=1503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adrianotanaka.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=1503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}