There are many ways to store images in Oracle DB. The most common manner is to store images as binary content in BLOB columns.
Starting from version 11g, Oracle introduced
Oracle Multimedia (previously known as Oracle interMedia), a feature for storing images, audio, video and other multimedia data.
Oracle Multimedia introduced
ORDAudio,
ORDDoc,
ORDImage,
ORDVideo, and
SI_StillImage data types and method for:
- extracting metadata and attributes from multimedia content
- embedding metadata generated for other application into images
- embedding multimedia data from Oracle Multimedia, Web servers, file systems, and other servers
- editing and transforming images
In this post, I will focus on the transformations you can apply to image data.
We start creating a table for storing a TIFF image.
CREATE TABLE AVT_IMG
(
ID NUMBER,
TIFF_IMG ORDSYS.ORDImage
);
We also create a database directory to the path where we store the images to be loaded into the DB.
CREATE OR REPLACE DIRECTORY avt_img_dir as 'C:\Imagenes_Blog';
We instanciate an object ORDImage and we load the TIFF image in the table AVT_IMG.
-- ORDImage.init()
INSERT INTO AVT_IMG VALUES(1,ORDImage.init());
COMMIT;
-- Load the image
DECLARE
obj ORDSYS.ORDImage;
ctx RAW(64) := NULL;
BEGIN
-- The import() method also sets the object properties by reading the image blob.
select TIFF_IMG into obj
from AVT_IMG
where ID = 1 for update;
obj.setSource('FILE', 'AVT_IMG_DIR', 'flowers.tif');
obj.import(ctx);
update AVT_IMG set TIFF_IMG = obj where id = 1;
commit;
END;
/
Using ORDSYS.ORDImage methods, we can read image properties:
DECLARE
image ORDSYS.ORDImage;
compression_format VARCHAR2(4000);
img_height NUMBER;
img_width NUMBER;
content_size NUMBER;
metav XMLSequenceType;
properties_match BOOLEAN;
BEGIN
-- Load the image into a variable
SELECT TIFF_IMG INTO image FROM AVT_IMG
WHERE ID=1;
-- Check if properties are readable
properties_match := image.checkProperties();
IF properties_match THEN
DBMS_OUTPUT.PUT_LINE('Check Properties succeeded');
ELSE
DBMS_OUTPUT.PUT_LINE('Check Properties failed');
END IF;
-- Read image properties
compression_format := image.getCompressionFormat();
img_height := image.getHeight();
img_width := image.getWidth();
content_size := image.getContentLength();
DBMS_OUTPUT.PUT_LINE('Compression Format: ' || compression_format);
DBMS_OUTPUT.PUT_LINE('Height: ' || img_height);
DBMS_OUTPUT.PUT_LINE('Width: ' || img_width);
DBMS_OUTPUT.PUT_LINE('Size: ' || content_size);
COMMIT;
END;
/
The result is:
and it matches with the image we are using as an example.
Let's alter the table as follows:
ALTER TABLE AVT_IMG ADD (JPEG_IMG ORDSYS.ORDImage, TIFF_BLOB BLOB, JPEG_BLOB BLOB);
for storing TIFF_IMG BLOB content (without metadata) and its copy in JPEG format.
The method
getContent() extracts the image BLOB content.
UPDATE AVT_IMG
SET TIFF_BLOB = ordsys.ordimage.getContent(TIFF_IMG);
COMMIT;
To change the image format, for instance from TIFF to JPEG, we can use the method
processCopy().
DECLARE
imgTiff blob;
imgJpeg blob;
BEGIN
UPDATE AVT_IMG SET JPEG_BLOB = empty_blob()
RETURNING TIFF_BLOB, JPEG_BLOB
INTO imgTiff, imgJpeg;
-- processCopy() method gets the following parameters:
-- * Source file
-- * Parameter string
-- * Destination file
ordsys.ordimage.processCopy(imgTiff, 'fileformat=jpeg', imgJpeg);
UPDATE AVT_IMG SET JPEG_BLOB = imgJpeg;
COMMIT;
END;
/
We can convert between the following formats: BMPF, CALS, GIFF, JFIF, PBMF, PGMF, PICT, PNGF, PNMF, PPMF, RASF, RPIX, TGAF, TIFF, WBMP.
If we want to create an ORDImage object from the JPEG_IMG BLO, this is the code:
UPDATE AVT_IMG
SET JPEG_IMG = ordsys.ordimage(
ordsys.ordsource(
JPEG_BLOB, null, null, null, sysdate, 1 ),
null, null, null, null, null, null, null );
The methods process() (that overwrites the source file) and
processCopy() can perform more complex operations, as adjusting image contrast, scale or rotating the image. The following code performs a 90 degrees rotation.
DECLARE
imgTiff blob;
imgJpeg blob;
BEGIN
UPDATE AVT_IMG set JPEG_BLOB=JPEG_BLOB
RETURNING JPEG_BLOB
INTO imgJpeg;
ordsys.ordimage.process(imgJpeg, 'rotate=90');
UPDATE AVT_IMG SET JPEG_BLOB = imgJpeg;
COMMIT;
END;
This is just a short demo of what Oracle Multimedia can do. For more info, see
http://www.oracle.com/pls/db112/portal.portal_db?selected=7&frame=#oracle_multimedia.
Basically I needed to specify the DB global name in each location that specified a connection to the DB. 
