LIKE Is Dead. We Now Speak in Vectors.

Vector Search is a way to find things that are similar—not by matching exact words like a typical SQL LIKE '%cat%' search—but by comparing meaning.

The basic idea is this:

You take your data (text, images, whatever) and turn it into numbers—a list of numbers, actually—called a vector. Each vector lives in a multi-dimensional space, and you can measure how close two items are by how close their vectors are.

So, what does “turning into vectors” mean?

A vector is just a way to represent something (like a word or a sentence) using numbers.

Think of it like this:

• “Cat” → [0.12, 0.88, -0.45, …]

• “Dog” → [0.10, 0.90, -0.40, …]

• “Spanish omelette” → [0.98, -0.01, 0.12, …]

The “cat” and “dog” vectors are close together—they’re both animals, pets, similar in meaning. But the omelette? That’s way off in a different part of the space. Nothing to do with the others.

Why use OpenAI embeddings (or something similar)?

Because creating these vectors yourself would take forever. You’d need to train a huge model, feed it tons of data, and have a supercomputer.

OpenAI already did that for you. Their models (like text-embedding-ada-002) are trained on massive amounts of text and know how to convert a piece of language into a vector that captures its meaning.

You send in a sentence, and it replies with something like:

“Here’s what that text means to me: [0.01, -0.02, 0.77, …]”.

That’s your vector. You can now compare it with others.

What about VECTOR_EMBEDDING?

You might be thinking: “Wait, doesn’t Oracle have a native function for this?” And yep — VECTOR_EMBEDDING is built into Oracle 23ai and can generate vector representations of text directly inside the database.

Sounds great, right? The catch is: it only works if your database has AI Vector Search enabled and if you’ve already registered a compatible embedding model. Depending on your setup (especially in shared or managed environments), those pieces might not be in place yet.

That’s why, for this example, I’m skipping VECTOR_EMBEDDING and going with an external embedding provider — in this case, OpenAI. It gives me more control and flexibility, especially when working in environments where I don’t have full DBA access.

Still, it’s worth showing what the native Oracle approach would look like if everything were set up:

Simple and elegant. No external APIs, no JSON handling — just pure SQL. Once your database is ready for it, VECTOR_EMBEDDING becomes a powerful piece of the stack.

Deploy the Ducks: We’re Embedding Now!

To begin integrating semantic search in Oracle APEX, we first need to connect to OpenAI’s embedding API. This allows us to transform any input text into a vector representation.

We do this by creating a Vector Provider in Workspace Utilities

Setting up OpenAI as a vector provider in Oracle APEX using the text-embedding-3-small model.

Refer to the documentation at https://api.openai.com/v1/embeddings to understand how to configure your credentials and specify the AI model you’ll be using.

Now that we’ve established a connection with OpenAI’s embedding API through our vector provider, the next step is to build the interface where users can input data and associate it with semantic vectors. For this, we’re going to create a modal form that allows users to submit entries with a title, description, and an image.

The idea is simple: the user fills in the title and description and uploads an image. Once the form is submitted, the description will be sent to OpenAI’s embedding API, and the returned vector will be stored in our database.

This will allow us to later compare, and search based on semantic similarity rather than exact matches.

To set this up, we’ll use the Create Page Wizard in Oracle APEX. This wizard simplifies the process by allowing us to generate both a report (or grid) and a form page in one go.

Artwork entry modal page in Oracle APEX with image upload, description, and embedding fields.

We’ve enhanced the modal form by adding a display-only field to show a preview of the uploaded image—but only if it exists. This improves usability by giving users immediate visual feedback when editing a record. If an image is present in the BLOB column, it’s rendered; otherwise, the field remains hidden or shows a placeholder message. This small but meaningful detail adds polish and clarity to the form interface.

Artwork modal page in Oracle APEX showing a server-side PL/SQL function that checks if image data is present before rendering the upload field.

We also decided to hide the MIME type field, since the file upload item in Oracle APEX automatically detects and stores the MIME type behind the scenes. There’s no need for the user to input or see this value, so keeping it hidden keeps the form cleaner and more focused.

Configuration screen in Oracle APEX where an image upload item is set to store binary data in a BLOB column. The MIME type is handled automatically, simplifying file management and ensuring consistency in uploads.

To complete the integration, we added a new process to the modal form called PR_GENERATE_VECTOR, which runs just before the dialog closes. This process is responsible for generating a semantic vector from the description and saving it directly into the database.

Oracle APEX processing configuration for semantic embedding generation. The PR_GENERATE_VECTOR process uses PL/SQL to call the OpenAI service and update the vector column with the embedding based on the user’s description.

Here’s how it works:

When the user submits the form, the process takes the content of the description field (:P110_DESCRIPTION) and sends it to the configured AI service using apex_ai.get_vector_embeddings. This function calls OpenAI behind the scenes, retrieves the vector, and updates the corresponding record in the table VS_ARTWORKS.

The process looks like this in PL/SQL:

We placed this process right after the standard form processing logic. It ensures that the vector is always generated (or updated) based on the latest version of the description, and it’s stored immediately after the record is saved.

This way, every artwork entry not only stores text and images, but also a rich semantic fingerprint ready for future vector-based search.

What’s Next?

Now that our application can store semantic vectors alongside user-submitted data, it’s time to explore how to search by meaning. In the second part of this article, we’ll dive into how to use Oracle’s native VECTOR_DISTANCE function to compare vectors and measure how semantically close two pieces of content are. We’ll build queries that rank results by similarity, enabling smart, context-aware searches—all from within APEX, using plain SQL.

It’s where the fun really begins: instead of searching by exact words, we’ll search by what things mean.

For more information, please visit our company website. Stay updated and feel free to reach out for support or contributions!