What is Oracle Multimedia? A Simple Guide to Storing and Processing Images

Databases aren't just for text and numbers anymore. In today's data-rich world, applications need to handle images, audio files, and videos. Oracle Multimedia is a feature built directly into the Oracle Database that gives you the power to store, manage, and even analyze this rich media content.

This guide will break down what Oracle Multimedia is and how you can use it, focusing on the most common use case: working with images.

📸 What Can You Store? Oracle's Media Types

Oracle provides different "object types" optimized for specific kinds of media. Think of these as special containers:

• OrdImage: This is the main type for storing images. It's powerful because it also lets you perform operations like resizing, rotating, scaling, and converting formats.

• SI_StillImage: A special image type based on the SQL/MM standard. Its main job isn't to be changed, but to be analyzed for content-based similarity searches.

• OrdAudio: The container for audio files.

• OrdVideo: The container for video files.

• OrdDoc: A general-purpose container for any other media type, like a PDF or Word document.

🧠 Beyond Storage: How Oracle Understands Images

This is where Oracle Multimedia gets really interesting. It doesn't just store an image as a blob of data; it can analyze its contents and extract key features.

This analysis, part of the SQL/MM standard, breaks an image down into:

• SI_AverageColor: The single, "average" color of the whole image.

• SI_ColorHistogram: A detailed record of all the colors in the image and how often each one appears.

• SI_PositionalColor: Tracks where certain colors are located in the image.

• SI_Texture: Describes the image's texture—is it coarse, smooth, or does it have a certain pattern?

Why do this? By storing these features, you can later ask your database powerful questions like, "Find all images that have a similar color mix to this one."

🛠️ How to Work with Images: A Practical Workflow

Let's walk through the basic steps of storing and managing images.

Step 1: Create Your Table

First, you need a table to hold your products and their images. Notice how we have columns for the image itself (photo) and for all its special features (photo_si, photo_ac, etc.).

CREATE TABLE products (
  id integer primary key,
  photo ORDSYS.ORDImage,
  photo_si ORDSYS.SI_StillImage,
  photo_ac ORDSYS.SI_AverageColor,
  photo_ch ORDSYS.SI_ColorHistogram,
  photo_pc ORDSYS.SI_PositionalColor,
  photo_tx ORDSYS.SI_Texture
);

Step 2: Automate Image Analysis with a Trigger

You don't want to manually calculate all those features every time you add a picture. We can use a database trigger to do it automatically.

This trigger will "fire" every time a new row is inserted or a photo is updated. It takes the new ORDImage, analyzes it, and automatically fills in all the SI_ feature columns.

CREATE OR REPLACE TRIGGER products_generateFeatures
  BEFORE INSERT OR UPDATE OF photo ON products
  FOR EACH ROW
DECLARE
  si ORDSYS.SI_StillImage;
BEGIN
  -- If the new photo isn't empty, generate its features
  IF :NEW.photo.height IS NOT NULL THEN
    si := new SI_StillImage(:NEW.photo.getContent());
    :NEW.photo_si := si;
    :NEW.photo_ac := SI_AverageColor(si);
    :NEW.photo_ch := SI_ColorHistogram(si);
    :NEW.photo_pc := SI_PositionalColor(si);
    :NEW.photo_tx := SI_Texture(si);
  END IF;
END;
/

Step 3: Inserting and Retrieving Images (Java Example)

To get an image from your application (like a Java app) into the database, you use a special "proxy" object (oracle.ord.im.OrdImage). This Java object acts as a local controller for the image data that lives in the database.

To insert an image:

1. Insert an empty OrdImage: First, you insert a new row with an empty image using ordsys.ordimage.init().

2. Get the proxy: You then select that empty image FOR UPDATE and get its Java proxy.

3. Load data: Use the proxy to load your local file (e.g., imgProxy.loadDataFromFile("./car1.gif")).

4. Update: Update the database row with the proxy, which now holds the image data.

5. Commit: When you commit, your trigger from Step 2 fires, analyzing the image and saving its features.

To retrieve an image:

You simply SELECT the photo column and get it as an OrdImage proxy. From there, you can get its properties (like imgProxy.getHeight()) or save it back to a file (imgProxy.getDataInFile(...)).

Step 4: Processing Images Directly in the Database

You don't need to pull an image out of the database just to resize it. You can tell Oracle to do the work for you.

For example, this code takes an existing image (srcImgProxy) and tells the database to create a new, scaled-down copy (dstImgProxy) as a 100x100 PNG file.

// perform conversion (processing occurs on the Oracle Database)
srcImgProxy.processCopy("maxscale=100 100 fileformat=png", dstImgProxy);

This is incredibly efficient, as the large image data never has to leave the database server.

🔍 The Best Part: Image Similarity Search

Now for the payoff. Since you've stored all those image features, you can search for images based on their content, not just their filename.

The SI_ScoreByFtrList function compares the features of two images and gives you a similarity score.

The score is a value between 0 and 100. A score of 0 means the images are identical. The lower the score, the more similar the images are.

Example Query

Let's find which images in our table are most similar to the image with id = 1. We'll tell the query to weigh the different features (color, texture, etc.) to get the best match.

SELECT
  src.id as source,
  dst.id as destination,
  SI_ScoreByFtrList(
    new SI_FeatureList(src.photo_ac, 0.3, -- 30% weight to average color
                       src.photo_ch, 0.3, -- 30% weight to color histogram
                       src.photo_pc, 0.1, -- 10% weight to positional color
                       src.photo_tx, 0.3),-- 30% weight to texture
    dst.photo_si
  ) as similarity
FROM products src, products dst
WHERE src.id <> dst.id AND src.id = 1
ORDER BY similarity ASC;

Conclusion:

The query results show that image #3 (score 8.02) is the closest visual match to image #1, followed by image #4.

By using Oracle Multimedia, you can build powerful applications that go beyond simple data storage and start to intelligently understand their own content.