2
Using Image Object Types

This chapter provides examples for the common uses of the Image BLOB and Image BFILE types, including:

adding types to a new or existing table
inserting a row using BLOB or BFILE images
populating a row using BLOB or BFILE images
querying a row
copying an image from BFILE to BLOB
copying an image from BLOB to BLOB
converting an image's format either temporarily or permanently

Prior to updating a BLOB value, you must lock the row containing the BLOB locator. This is usually done using a SELECT FOR UPDATE statement in SQL and PL/SQL programs, or using an OCI pin or lock function in OCI programs.

2.1 Adding Image Types to an Existing Table

Suppose you have an existing table named 'emp' with the following columns:

ename      VARCHAR2(50) 
salary     NUMBER 
job        VARCHAR2(50) 
department INTEGER

To add a new column to the 'emp' table called 'photo_id' using the Image BLOB type, issue the following statement:

ALTER TABLE emp 
ADD (photo_id ORDSYS.ORDIMGB);

To add a new column to the 'emp' table called 'large_photo' using the Image BFILE type, issue the following statement:

ALTER TABLE emp  
ADD (large_photo ORDSYS.ORDIMGF);

2.2 Adding Image Types to a New Table

Suppose you are creating a new table called 'emp' with the following columns:

ename         VARCHAR2(50) 
salary        NUMBER 
job           VARCHAR2(50) 
department    INTEGER 
photo_id      ORDIMGB 
large_photo   ORDIMGF

The column 'photo_id' would use the Image BLOB type, while the column 'large_photo' would use the Image BFILE type. The following statement would create the table:

CREATE TABLE emp (
ename VARCHAR2(50), 
salary NUMBER, 
job VARCHAR2(50), 
department INTEGER, 
photo_id ORDSYS.ORDIMGB, 
large_photo ORDSYS.ORDIMGF);

2.3 Inserting a Row Using BLOB Images

To insert a row into a table that has storage for image content using the Image Cartridge BLOB type (ORDImgB), you must populate the type with an initializer. Note that this is different from NULL.

The following examples describe how to insert rows into the table using the Image BLOB type. Assume you have a table 'emp' with the following columns:

ename      VARCHAR2(50) 
salary     NUMBER 
job        VARCHAR2(50) 
department INTEGER 
photo_id   ORDIMGB

To insert a row into the table with no data in the 'photo_id' column, issue the following statement:

INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123,NULL);

Attempting to use the Image Cartridge types with a NULL value results in an error. If you are going to use the image type's content attribute, you must populate the content attribute with a value and initialize storage for the content attribute with an empty_blob( ) constructor. To insert a row into the table with empty data in the 'photo_id' column, issue the following statement:

INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123, 
ORDSYS.ORDIMGB(empty_blob(),NULL,NULL,NULL,NULL,NULL,NULL));

2.4 Populating a Row Using BLOB Images

The following is an example of populating the row with Image BLOB data:

DECLARE 
    -- application variables
    Image  ORDSYS.ORDIMGB; 
BEGIN 
    insert into emp values('John Doe',24000,'Technical Writer',123, 
    ORDSYS.ORDIMGB(empty_blob(), NULL,NULL,NULL,NULL,NULL,NULL)); 
    --select the newly inserted row for update 
    SELECT photo_id into Image from emp  
    where ename = 'John Doe' for UPDATE; 
    
    BEGIN
    -- populate the data with dbms lob calls or write an OCI
    -- program to fill in the content attribute
    END; 
 
    -- set property attributes for the image data 
    Image.setProperties; 
 
    UPDATE emp set photo_id = Image where ename = 'John Doe'; 
 
    -- continue processing 
    END;

An UPDATE statement is required to update the property attributes. If you do not perform the setProperties( ) function and UPDATE statement now, you can still commit and the change to the image will be reflected in the content attribute, but not in the properties. See the Oracle8 Server Object Extensions manual for more information on BLOBs.

2.5 Inserting a Row Using BFILE Images

To insert a row into a table that has storage for image content using the Image BFILE type (ORDImgF), you must populate the type with an initializer. Note that this is different from NULL.

The following examples describe how to insert rows into the table using the Image BFILE type. Assume you have a table 'emp' with the following columns:

ename    VARCHAR2(50) 
salary   NUMBER 
job      VARCHAR2(50) 
department  INTEGER
large_photo ORDIMGF

To insert a row into the table with no data in the 'large_photo' column, issue the following statement:

INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123,NULL);

Attempting to use the Image BFILE type with a NULL value results in an error. If you are going to use the Image BFILE type column, you must first populate the column with a value. To populate the value of the Image BFILE type column, you must populate the row with a file constructor.

The following example inserts a row into the table with an image called 'jdoe.gif' from the ORDIMGDIR directory:

insert into emp values ('John Doe',24000,'Technical Writer',123, 
ORDSYS.ORDIMGF(bfilename('ORDIMGDIR','jdoe.gif'), 
NULL,NULL,NULL,NULL,NULL,NULL));

Note:

In release 8.0.3 of the Server, the content of the Image BFILE type is read-only.

The 'bfilename' argument 'ORDIMGDIR' is a directory referring to a file system directory. The following sequence creates a directory named ORDIMGDIR:

connect internal 
        -- make a directory referring to a file system directory 
create directory ordimgdir as '<myimagedirectory>'; 
grant read on directory ordimgdir to <user-or-role>;

where <myimagedirectory> is the the file system directory, and <user-or-role> is the specific user to grant read access to.

2.6 Populating a Row Using BFILE Images

The following is an example of populating the row with Image BFILE data:

DECLARE  
    Image  ORDSYS.ORDIMGF; 
BEGIN 
    insert into emp values('John Doe',24000,'Technical Writer',123,
    ORDSYS.ORDIMGB(bfilename('ORDIMGDIR','jdoe.gif'),  
    NULL,NULL,NULL,NULL,NULL,NULL)); 
 
    --select the newly inserted row for update 
    SELECT large_photo into Image from emp 
    where ename = 'John Doe' for UPDATE; 
   
    -- set property attributes for the image data 
    Image.setProperties; 
 
    UPDATE emp set large_photo = Image where ename = 'John Doe'; 
 
    -- continue processing 
   
 END;

2.7 Querying a Row

For the following examples, assume you have this table:

create table emp (
ename VARCHAR2(50), 
salary NUMBER, 
job VARCHAR2(50), 
department INTEGER, 
photo_id ORDSYS.ORDIMGB, 
large_photo ORDSYS.ORDIMGF);

The following is an example of querying the row that has Image BFILE data. You must create a table alias (E in this example) when you refer to a type in a SELECT statement.

SELECT ename, E.photo_id.width 
 FROM emp E
WHERE ename = 'John Doe' and 
      E.photo_id.width > 32 and 
      E.photo_id.fileFormat='GIFF';

The following is an example of querying the row that has Image LOB data:

SELECT ename, E.large_photo.compressionFormat   
 FROM emp E
WHERE ename = 'John Doe' and 
     E.large_photo.width > 32 and 
     E.large_photo.fileFormat='GIFF' and 
     E.large_photo.compressionFormat='GIFLZW';

2.8 Copying an Image from a BFILE to a BLOB Type

To copy the data from an Image BFILE type to an Image BLOB type, you would use the ORDImgF.copyContent method. For example, the following program copies image data from an Image BFILE type to an Image BLOB type:

 DECLARE 
    BLOBImage ORDSYS.ORDIMGB; 
    BFILEImage ORDSYS.ORDIMGF; 
 BEGIN 
    SELECT photo_id,large_photo 
    INTO BLOBImage,FILEImage 
    FROM emp where ename = 'John Doe' for UPDATE; 
 
    -- Copy the BFILE image to the BLOB image 
    BFILEImage.copyContent(BLOBImage.content); 
 
    -- Set the BLOB image properties 
    BLOBImage.setProperties; 
     
    -- continue processing 
 
    -- update the row 
    UPDATE emp 
    SET photo_id = BLOBImage 
    where ename = 'John Doe'; 
 
 END

2.9 Copying an Image from a BLOB to a BLOB Type

To copy the data between two Image BLOB types, use the ORDImgB.copyContent method. For example, the following program copies image data from an Image BLOB type to another Image BLOB type:

DECLARE 
    Image_1 ORDSYS.ORDIMGB; 
    Image_2 ORDSYS.ORDIMGB; 
BEGIN 
    SELECT photo_id 
    INTO Image_1 
    FROM emp where ename = 'John Doe'; 
 
    SELECT photo_id 
    INTO Image_2 
    FROM emp where ename = 'Also John Doe' for UPDATE; 
 
    -- copy the data from Image_1 to Image_2 
    Image_1.copyContent(Image_2.content); 
     
    -- set the image properties for Image_2 
    Image_2.setProperties; 
 
    -- continue processing 
 
    UPDATE emp 
    SET photo_id = Image_2 
    WHERE ename = 'Also John Doe'; 
 
END

2.10 Converting an Image's Format

To convert the image data into a different format, use the Process method. For example, the following program converts the image data to the TIFF file format:

 DECLARE 
    Image ORDSYS.ORDIMGB; 
 BEGIN 
    SELECT photo_id 
    INTO Image 
    FROM emp 
    WHERE ename = 'John Doe' for UPDATE; 
 
    -- convert the image to TIFF in place 
    Image.process('fileFormat=TIFF'); 
 
 END

2.11 Copying and Converting in One Step

To make a copy of the image and convert it into one step, use the processCopy method. For example, the following program converts the image data to the TIFF image file format, but leaves the original image intact:

DECLARE 
    Image_1 ORDSYS.ORDIMGB; 
    Image_2 ORDSYS.ORDIMGB; 
BEGIN 
    SELECT photo_id 
    INTO Image_1 
    FROM emp 
    WHERE ename = 'John Doe' for UPDATE; 
 
    -- convert the image to tiff and store the result in Image_2 
    Image_2 := Image_1; 
    Image_1.processCopy('fileFormat=TIFF',Image_2.content); 
 
    -- continue processing 
 
END

Changes made by these methods can be rolled back. This technique may be useful for a temporary format conversion.

2 Using Image Object Types