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
extending the cartridge with new object types
using the cartridge with object views

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 Application Developer's Guide 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.ORDIMGF(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; 
 
    -- 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.12 Extend the Cartridge With a New Type

You can use the ORDImgF and ORDImgB types as the basis for a new type of your own creation. This task requires the Objects Option of Oracle8 Enterprise Edition.

create type AnnotatedImage as object 

   (  image ordsys.ordimgF,  

       description varchar2(2000),

       MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage),
       MEMBER PROCEDURE CopyContent(dest IN OUT BLOB),
       MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB)
    );
/

create type body AnnotatedImage as 
  MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage) IS
  BEGIN
    SELF.image.setProperties;
    SELF.description := 
 'This is an example of using Image Cartridge as a subtype';
  END SetProperties;

  MEMBER PROCEDURE CopyContent(dest IN OUT BLOB) IS
  BEGIN
    SELF.image.copyContent(dest);
  END CopyContent;

  MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB) IS
  BEGIN
    SELF.image.processCopy(command,dest);
  END ProcessCopy;
END;
/

After creating the new type, you can use it as you would any other type. For example:

create or replace directory TEST_DIR as 'C:\TESTS';

create table my_example (id number,an_image AnnotatedImage);

insert into my_example values ( 1, 
    AnnotatedImage(
      ordsys.ordimgf( 
        bfilename('TEST_DIR','test1.jpg'),
        NULL,NULL,NULL,NULL,NULL,NULL,NULL),
      NULL)
    );
commit;

declare
  myimage AnnotatedImage;
begin
  select an_image into myimage from my_example;

  myimage.setProperties;

  dbms_output.put_line('This image has a description of '); 
  dbms_output.put_line( myimage.description);

  update my_example set an_image=myimage;
end;

2.13 Use Image Types With Object Views

Just as a view is a virtual table, an object view is a virtual object table.

Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data-- of either built-in or user-defined types -- stored in the columns of relational or object tables in the database.

Object views provide the ability to offer specialized or restricted access to the data and objects in a database. For example, you might use an object view to provide a version of an employee object table that doesn't have attributes containing sensitivedata and doesn't have a deletion method. Object views also allow you to try object-oriented programming without permanently converting your tables. Using object views, you can convert data gradually and transparently from relational tables to object-relational tables.

Consider the following non-object image table:

create table flat (
   id            number,
   content       bfile,
   height        number,
   width         number,
   contentLength number,
   fileFormat    varchar2(64),
   contentFormat varchar2(64),
   compressionFormat varchar2(64)
                   );

You can create an object view on the flat table as follows:

create or replace view object_images_v as 
  select
      id,
      ordsys.ORDImgF(
         T.content,
         T.height,
         T.width,
         T.contentLength,
         T.compressionFormat
                     ) IMAGE
   from flat T;

Object views provide the flexibility of looking at the same relational or object data in more than one way. Thus you can use different in-memory object representations for different applications without changing the way you store the data in the database. See the Oracle8 Concepts manual for more information on defining, using, and updating object views.

2 Using Image Object Types