Beyond Text: Building an AI-Powered Color Matcher in Oracle FreeSQL

☁️ Pre-Flight Checklist

This is a connection flight. Before we taxi down the runway, here’s your flight plan. Keep this handy to navigate your flight path.

Welcome aboard the cloud! ☁️

🌥️ Takeoff

• The Core Idea: Colors are Vectors
• Step 1: Create Your Color Palette

⛅️ Cruising Altitude

• Step 2: The Next Level of PL/SQL - A Reusable Function
• A Quick Note: Where is the Embedding Model?
• Step 3: The Ultimate Color Search
• Step 4: Exploring Different Distance Metrics

🌤️ Landing & Taxi

• Conclusion: From Raw Data to a Practical Tool
• Next Steps & Going Further

Enjoy your flight! ☁️

---

In the first part of this series, we introduced the exciting world of AI Vector Search by finding similar cars using Oracle's new VECTOR data type. We learned that vectors act as "GPS coordinates for meaning," allowing us to find conceptually similar items.

But vector search isn't just for text and abstract concepts. It's a powerful tool for searching any data that you can represent with numbers.

In this tutorial, we'll build a genuinely useful tool for web developers and designers: a Color Matcher. We'll use Oracle AI Vector Search to find the closest standard CSS color name to any given hex code. And just like last time, we'll do it all for free. We're using the Oracle FreeSQL sandbox because it gives us instant, browser-based access to a full-featured, modern Oracle 23ai Database without any installation or setup required.

The Core Idea: Colors are Vectors

The magic of this project is that colors are already vectors in disguise. Every color on the screen can be represented by the amount of Red, Green, and Blue it contains. This RGB value is a natural 3-dimensional vector.

• A pure, vibrant red is rgb(255, 0, 0), which becomes the vector [255, 0, 0].
• A dark, moody blue like rgb(25, 25, 112) becomes [25, 25, 112].
• White is [255, 255, 255] and Black is [0, 0, 0].

The VECTOR_DISTANCE between two of these color vectors measures their perceptual similarity. A small distance means the colors look very similar to the human eye. This is the simple but powerful principle we'll use to build our tool.

Step 1: Create Your Color Palette

Create the Table

First, we need a dataset to search against. We will create a table containing all the standard named colors used in CSS.

--Create the table
CREATE TABLE css_colors (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  hex VARCHAR2(7),
  color_vector VECTOR
);

To run the statement after pasting use crtl + Enter or ⌘ + Enter

The new css_colors table will now appear in the navigator pane on the left. A great feature of FreeSQL is that it automatically persists your work, so this table will be available in future sessions.

Populate the Table

I've placed the complete SQL script, which inserts all 140+ colors, into a GitHub Gist.

The complete SQL script to insert all 140+ colors is available in this GitHub Gist.
Please copy the entire script from the Gist and run it once in a new FreeSQL worksheet to populate the table.

After running the script, the css_colors table will be created and fully populated. You can confirm the data is there with a quick query:

SELECT name, hex FROM css_colors WHERE name IN ('Red', 'Green', 'Blue');

Step 2: The Next Level of PL/SQL - A Reusable Function

Here is where we move beyond simple queries and begin to leverage the true power of FreeSQL as a development platform. Instead of performing the hex-to-vector conversion in an external application, we can build a smart, reusable function directly in the database. This is far more efficient and demonstrates a core capability of a professional database environment. This is the perfect opportunity to level up our PL/SQL skills by creating a reusable function.

Copy and paste this code into your FreeSQL worksheet and run it to create the function:

CREATE OR REPLACE FUNCTION hex_to_vector(p_hex_string VARCHAR2)
RETURN VECTOR AS
  v_hex VARCHAR2(7);
  v_r NUMBER;
  v_g NUMBER;
  v_b NUMBER;
BEGIN
  -- Remove the '#' prefix if it exists
  v_hex := LTRIM(p_hex_string, '#');

  -- Check for valid length
  IF LENGTH(v_hex) != 6 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid hex code: Must be 6 characters long.');
  END IF;

  -- Convert hex pairs to decimal numbers, with error handling
  BEGIN
    v_r := TO_NUMBER(SUBSTR(v_hex, 1, 2), 'XX');
    v_g := TO_NUMBER(SUBSTR(v_hex, 3, 2), 'XX');
    v_b := TO_NUMBER(SUBSTR(v_hex, 5, 2), 'XX');
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20002, 'Invalid hex code: Contains non-hexadecimal characters.');
  END;

  -- Return the final result using the string constructor for reliability
   RETURN VECTOR('[' || TO_CHAR(v_r) || ',' || TO_CHAR(v_g) || ',' || TO_CHAR(v_b) || ']');
END;
/

How it Works:

Lines 1-6: The Setup Area

This section defines our tool. It says we're creating a function named hex_to_vector that accepts one piece of text (the hex code) and promises to return a VECTOR. We also set up a few empty "boxes" (variables) to hold our values as we work.

Line 8: Cleaning the Input

The LTRIM function tidies up the input by trimming the # symbol from the left side. This lets us work with just the six important characters of the color code.

Lines 11-13: First Safety Check.

A valid hex color code must contain exactly six characters. This IF statement checks the length. If it's not six, the function stops immediately and uses RAISE_APPLICATION_ERROR to show a helpful, custom error message instead of just crashing.

Lines 16-23: The "Try-Catch" Safety Net.

This BEGIN...EXCEPTION...END block is the most important safety feature.

• Inside BEGIN, we try the riskiest part: converting the text characters into the numbers for our vector. The code grabs two characters at a time (e.g., 9B) and converts them from hexadecimal into a regular number (e.g., 155).
• The EXCEPTION block is our safety net. If a user enters something invalid like #FFG000, the TO_NUMBER function will fail on the character 'G'. Instead of crashing, the code immediately jumps to the EXCEPTION block, which catches the error and displays our second helpful message.

Line 26: Assembling the Final Vector.

If the input has passed all our safety checks, this final line builds the result. It converts the three numbers for red, green, and blue into text and carefully assembles them into a string with brackets and commas, like '[155,89,182]'. This string is then passed to the VECTOR function, which creates the final vector object. We use this string-building method because it's a very reliable way to create a vector that avoids potential database bugs.

Once you run the script, the function is created. You can see it listed under the Functions tab in the navigator pane on the left. A great feature of FreeSQL is that it automatically saves your functions and tables, so you can come back and use them in future sessions without having to create them again.

A Quick Note: Where is the Embedding Model?

If you're familiar with AI search, you might be wondering: where is the "embedding model"? It's a great question, and the answer reveals the different ways vectors can be created.

Whether you need an embedding model depends entirely on your source data. In practice, there are three main approaches to generating vectors:

1. Natural Vectors (The Color Example)

Our color data (#0000FF) is a direct, universally defined representation of a vector ([0, 0, 255]). The data is already a vector. Our hex_to_vector function isn't an AI model; it's just a decoder that translates one format to another. No model is needed.

2. Feature-Engineered Vectors (The Car Example)

In the first article, we used a vector like [9.5, 8] to represent a car's [Performance, FuelConsumption]. This is a "middle ground." If your source data is structured (e.g., a table with horsepower and mpg columns), you can manually create a vector by defining a formula. For example, Performance = horsepower / 50. This is a classic data science technique called feature engineering. No AI model is needed because a human is defining the logic.

3. AI-Generated Vectors (Text & Image Search)

If your source data is unstructured (e.g., a text description like "a sleek sports car with a roaring V8 engine"), you have no numbers to start with. This is where you must use an embedding model. The AI model reads the text and creates a vector that captures its semantic meaning.

Here’s a summary:

Data Source	Method	Model Needed?	Example
Natural Vector	Direct Decoding	No	Colors (#FF0000 -> [255,0,0])
Structured Data	Feature Engineering	No	Cars (using horsepower, mpg)
Unstructured Data	AI Embedding	Yes	Text ("a sleek sports car...")

Understanding this distinction is key to knowing which tools to use for your own vector search projects.

Step 3: The Ultimate Color Search

Now that we have our data table and our helper function, we can perform the search. This single, elegant query combines everything we've built.

Let's find the closest named colors to a custom purple, #9B59B6.

-- Find the 5 closest named colors to a given hex code
SELECT
  name,
  hex,
  ROUND(VECTOR_DISTANCE(hex_to_vector('#9B59B6'), color_vector, EUCLIDEAN), 2) AS distance
FROM css_colors
ORDER BY distance ASC
FETCH FIRST 5 ROWS ONLY;

When you run this, you'll get a result like:

Swatch	Color	Hex	Distance
	MediumOrchid	#BA55D3	42.64
	MediumPurple	#9370DB	44.29
	DarkOrchid	#9932CC	44.82
	SlateBlue	#6A5ACD	54.14
	BlueViolet	#8A2BE2	65.89

It works perfectly! The database calculated the "visual distance" between our custom purple and all 140+ colors in the table and returned the 5 closest matches.

🌩️ A Quick Clarification: The Swatch column is purely for visual presentation in this article. Our SQL query retrieves the raw data—the color name, hex code, and distance—from the database. The database itself simply stores and returns the data, not the visual swatch.

Step 4: Exploring Different Distance Metrics

So far, we've used EUCLIDEAN distance to find the most visually similar colors. But one of the most powerful features of Oracle AI Vector Search is its support for multiple distance algorithms. Each one measures "similarity" in a slightly different way, which can lead to different results.

Let's turn our query into a more powerful educational tool by making the distance metric itself interactive.

-- Find the 5 closest colors using a user-provided hex code AND distance metric
SELECT
  name,
  hex,
  ROUND(VECTOR_DISTANCE(hex_to_vector('&your_hex_code'), 
  color_vector, &distance_metric), 2) AS distance
FROM css_colors
ORDER BY distance ASC
FETCH FIRST 5 ROWS ONLY;

Now, when you run this, FreeSQL will prompt you for both the hex code and the distance metric (e.g., EUCLIDEAN, COSINE, DOT, HAMMING, or MANHATTAN).

A Critical Warning: The Default Metric and the 'Black' Sheep

What happens if you don't specify a metric in the prompt? It's crucial to know that COSINE is the default. If you try to run the query with COSINE (or leave the &distance_metric prompt blank), you'll hit an error:

ORA-03087: unable to convert BINARY_FLOAT or BINARY_DOUBLE NaN (Not a Number) value to NUMBER

This happens because your SELECT query must calculate the distance between your input color and every other color in the css_colors table to find the top 5 closest matches. When the query's process reaches the row for "Black", it attempts to calculate the distance between your color and the vector [0, 0, 0]. Since the "Black" vector has a length of 0, and the Cosine formula involves dividing by the vector's length, this specific comparison fails with a division-by-zero error, stopping the entire query. This is a fantastic practical lesson: always know your defaults and how they interact with your entire dataset.

Comparing The Results

Let's see how the top 5 closest colors to our custom purple (#9B59B6) change when we use different metrics. The original EUCLIDEAN search gave us a list of purples and blues. But other metrics see the world differently.

EUCLIDEAN Distance

This is the metric we used in Step 3, and it's the most intuitive way to measure distance. EUCLIDEAN distance calculates the straight-line, "as the crow flies" path between the two color vectors in 3D space. It's calculated using the Pythagorean formula: sqrt((R1-R2)^2 + (G1-G2)^2 + (B1-B2)^2). As the results from the previous step showed, this metric is excellent for finding colors that are perceptually very similar.

DOT Product Results

ROUND(VECTOR_DISTANCE(hex_to_vector('#9B59B6'), color_vector, DOT), 2) AS distance

The DOT product is a similarity score, not a true distance. It's calculated by multiplying the corresponding R, G, and B values of the input vector and the vector it's being compared to (which happens for every vector in the table): (R1*R2) + (G1*G2) + (B1*B2). A larger value means more similarity. Oracle returns the negative of the dot product so that smaller results are still "closer." This metric favors colors that are bright in the same channels as our input color. This is because a high value in one vector (e.g., a high Red value in your input) only contributes significantly to the total score if it's multiplied by a correspondingly high value in the other vector. The final score is the sum of these products, so it's maximized when brightness in the R, G, and B channels align. Notice how it returns bright, almost white colors, as they have high R, G, and B values that contribute to a large dot product.

Swatch	Color	Hex	Distance
	White	#FFFFFF	-108630
	Snow	#FFFAFA	-107275
	GhostWhite	#F8F8FF	-106922
	Azure	#F0FFFF	-106305
	MintCream	#F5FFFA	-106170

HAMMING Distance Results

ROUND(VECTOR_DISTANCE(hex_to_vector('#9B59B6'), color_vector, HAMMING), 2) AS distance

HAMMING distance simply counts how many components (R, G, or B) are different between two vectors. For [R1, G1, B1] and [R2, G2, B2], it checks if R1 != R2, G1 != G2, and B1 != B2 and sums the true results. The maximum possible distance is 3. This metric is not very nuanced for colors, as it only cares if the values are an exact match or not, which is rare. This is why you see a tie between several colors that all differ from our input purple in all three (R, G, and B) components.

Swatch	Color	Hex	Distance
	AliceBlue	#F0F8FF	3
	AntiqueWhite	#FAEBD7	3
	Aqua	#00FFFF	3
	Aquamarine	#7FFFD4	3
	Azure	#F0FFFF	3

MANHATTAN Distance Results

ROUND(VECTOR_DISTANCE(hex_to_vector('#9B59B6'), color_vector, MANHATTAN), 2) AS distance

The MANHATTAN distance (or "city block" distance) is calculated by summing the absolute differences of each component: |R1 - R2| + |G1 - G2| + |B1 - B2|. It's like traveling on a grid, one axis at a time. The results are very similar to EUCLIDEAN distance, as both measure perceptual difference, but MANHATTAN gives slightly more weight to the sum of individual channel differences than the direct "as the crow flies" distance.

Swatch	Color	Hex	Distance
	DarkOrchid	#9932CC	63
	MediumOrchid	#BA55D3	64
	MediumPurple	#9370DB	68
	SlateBlue	#6A5ACD	73
	MediumSlateBlue	#7B68EE	103

As you can see, the "best" match depends entirely on your definition of similarity. For finding visually similar colors, EUCLIDEAN or MANHATTAN are clearly the best choices. For other tasks, other metrics might be superior. Choosing the right one is a critical part of designing a successful vector search application.

Conclusion: From Raw Data to a Practical Tool

Congratulations! You've built a practical color-matching tool and experienced the power of a professional database environment firsthand.

In this tutorial, you have:

• Seen how vector search can be applied to non-text data like colors.
• Leveled up your PL/SQL skills by creating a powerful, reusable function.
• Experienced how FreeSQL provides a zero-install sandbox to build and test real, data-centric application logic.

This reinforces the power of keeping your logic close to your data. By creating a function directly in the database, we built a fast, efficient, and elegant solution.

Happy building! ☁️

Next Steps & Going Further

The tool you've built is complete, but the concepts can go even further. Here are a few ideas to keep in mind for future projects:

Indexing for Performance

Our table of 140 colors is tiny for an Oracle database. But if you were building a search engine for millions of items, a sequential scan would be too slow. For this, you would add a vector index.

-- Example of creating a vector index
CREATE VECTOR INDEX color_idx ON css_colors (color_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;

This command creates an Approximate Nearest Neighbor (ANN) index, which allows the database to find the "closest" vectors almost instantly without having to compare your query with every single row. This is the critical step for taking a vector search prototype to a production-level application.

Exploring Other Use Cases

This vector technique is powerful for internal resource management. Imagine a project manager needs to assign a critical task: "Build a data-driven backend prototype and present it to stakeholders." The required skills could be quantified as a vector, for example: [Python, SQL, PublicSpeaking, Design].

The ideal profile for this specific task might be [9, 8, 7, 1], representing a need for strong Python/SQL, good presentation skills, and no design experience.

Instead of manually searching through employee profiles or relying on memory, the manager could run a query to find the best fit instantly:

-- Find the best available employee for a specific task profile
SELECT
  employee_name,
  current_project_load
FROM employees
WHERE availability = 'Available'
ORDER BY VECTOR_DISTANCE(skill_vector, VECTOR('[9, 8, 7, 1]')) ASC
FETCH FIRST 3 ROWS ONLY;

This query would instantly surface the top 3 available employees whose skills most closely match the task's requirements. It's a data-driven approach that enables fast, effective, and unbiased task allocation within an organization.

Keep exploring what you can build! ☁️

---

Cover Photo by BoliviaInteligente on Unsplash