Many enterprises expend tremendous resources on the acquisition, storage, and management of collections of images, including graphics, video, and audio. While acquiring such data can be time-consuming and expensive, it has tremendous business value and represents a large investment. Therefore, storage and processing this media is one of the fastest growing applications.

In this chapter we look at the options for storing media within a database. We also learn how to create a multimedia database using relational and object-relational concepts. We will introduce a simple information systems case study to illustrate the issues and see how the different approaches could work out in practice. To summarize, in this chapter we will cover how

This chapter focuses on the storage options for media data that have arisen through the recent developments of object-relational databases and media technologies. First, we will focus on the relational approach and the standard data types available for media storage based on LOBS and BFILES, since these data types are at the basis of the object types described later in this chapter, and it is important to have a sound grasp of their functions and capabilities. This way we will understand the limitations of this approach, particularly in terms of storage of metadata and retrieval before we look at the more complex alternatives.

We can use a relational or object-relational approach to developing databases for media-rich applications. What this means is that we can follow the relational approach based on the standard SQL data types and those implemented in Oracle, or we can use object types as specified in SQL:1999.

As a first option we can choose to restrict the database to the standard media data types specified in SQL:1999 for large object (LOB) data types. These LOB data types include binary large object (BLOB), which is used for image, video, and audio data. In addition there are CLOBs for character data, which we will not be particularly interested in but they can hold large documents and are manipulated by Oracle Text and BFILES, which can be used for any type of data but have certain limitations.

However, SQL:1999 provided a framework for creating object types (also called user-defined, UDT) types that can be used for media databases. There are several advantages to adopting this approach. In the next section we will first briefly explain how to use the SQL types BLOB and BFILE and their advantages and disadvantages. This is important to understand even if we decide to use object types because, from time to time in the development, we will need to know about the properties of BLOBS and BFILES and be able to manipulate them.

Large object (LOB) data types in Oracle10g have the advantage of being able to accommodate large size files and to support random access. In Oracle10g there is support for terabyte-size LOB values (maximum size limit for LOBS is 8–128 terabytes, depending on the database block size) in such a way that they can be stored in the database and manipulated in the following programmatic environments:

This means we can store and manipulate LOB (BLOB, CLOB, and NCLOB) data types larger than 4 GB, which was the limit in previous versions of the database, stored in the database tablespaces in a way that optimizes space and provides efficient access. A column of a table can be specified with any LOB data type in the normal way. Incidently Oracle interMedia uses LOB data types to create data types for use in multimedia applications such as interMedia-specialized object types ORDAudio, ORDDoc, ORDImage, and ORDVideo.

When a LOB data type is declared for a column in a table, the values stored are references, known as the LOB locators, that specify the location of the large stored object. If the LOB is small (up to 4 KB) it can be stored with its locator but otherwise it will be moved to a different physical location in the database tablespaces. This is referred to as inline storage but obviously large BLOBS may not be stored inline with other row data. In that case, a locator is stored in the row and the actual BLOB is stored in other tablespaces. The locator can be considered a pointer to the actual location of the BLOB value. When you select a BLOB, you are selecting the locator instead of the actual value, although this is done transparently. An advantage of this design is that multiple BLOB locators can exist in a single row. For example, you might want to store a short video clip of a training tape, an audio recording containing a brief description of its contents, a syllabus of the course, a picture of the instructor, and a set of maps and directions to each training center all in the same row.

LOB data types are restricted in terms of their storage options and standard SQL operations, as shown in Table 3.1. For example they are restricted from primary and foreign key constraints and from use in comparisons other than pure equality tests. However, there are several operations for BLOBs including concatenation, substring, overlay, and trim that are executed through the LOB locator.

The BFILE data type is a special external LOB data type. These are useful for data objects stored in operating system files, outside the database tablespaces. The database accesses external LOBS using the SQL data type, BFILE. BFILES are read-only data types. The database allows read-only byte stream access to data stored in BFILES and therefore we cannot write to a BFILE from within an application. This obviously makes their use very limited and we would not normally use them. However, we need to know about them as they provide a means of getting data from the external file system into a BLOB.

Any storage device accessed by your operating system can hold BFILE data, including hard-disk drives, CD-ROMs, PhotoCDs, and DVDs. The database can access BFILES provided that the operating system supports stream-mode access to the operating system files. A summary of characteristics of LOB data types are:

In Oracle10g, small LOBS (up to 4 KB) can be stored inline with the rest of your row data:

First, just using BLOB and BFILE data types, for example, we could specify the main table as follows:

CREATE TABLE photos (
      id           NUMBER PRIMARY KEY,
      description  VARCHAR2(40) NOT NULL,
      location     VARCHAR2(40),
      image        blob,
      thumb        blob
);

In this way we would store both the main image and the thumbnail image within the database as they would be used for random access, transactions, image-processing, and indexing purposes. This is basically a relational approach to the problem and the database would be manipulated mainly through SQL. Consider the following data for rows for the Picture Book photos table that identify the two image files (Figure 3.1) we want to insert into the database.

Figure 3.1. Storing media data.

The family will probably want to add single images and generate thumbnails to store in the database. This is quite complicated with the relational approach. It involves the following processes for adding a row for a single image:

This process is sufficiently complex to put many users off from developing a media database. Why is this necessary?

Before media data can be entered into the image column, we must ensure the LOB locator must be non-NULL. Instead of using NULL when the BLOB is missing from a row, the BLOB value is set to EMPTY by using the EMPTY_BLOB() function in the INSERT statement. A column containing a LOB value set to NULL has no locator. By contrast, an empty LOB is a LOB of zero length that has a LOB locator. So, if we select from an empty LOB column or attribute, we get back a locator, which we can use to fill the LOB with data using the OCI or DBMS_LOB routines or an appropriate import method. This initialization requirement will arise whenever a BLOB occurs, even with the interMedia data types described later in this chapter.

This can also be done for initializing the locator of the BFILE column by using the BFILENAME() function. Oracle provides a special function BFILENAME() to associate the external file with the database locator. To associate the BFILE objects with a directory in the external file system that will hold the actual data, we would need to create a DIRECTORY object. We must be connected as dba/system to do this unless, as users, we have been granted the create directory privilege, such as:

GRANT CREATE ANY DIRECTORY TO SCOTT;

This directory association is done by giving the subdirectory holding the image a logical name, using a statement, such as, for Windows,

CREATE DIRECTORY "PHOTO_DIR" AS 'C:PICTURES ';

or, for Unix or Linux,

CREATE DIRECTORY "PHOTO_DIR" AS 'home/images'

We must then grant the users access to the directory object. A directory object has thus been created to hold the external files, called “PHOTO_DIR,” and then access rights can be given to the users as:

GRANT READ ON DIRECTORY PHOTO_DIR TO SCOTT

As we have seen already it is difficult to manipulate LOBS in SQL because the standard functions do not exist and often SQL editors cannot cope with the display of media data. However, there are often reasons why using SQL to update LOBS or extract segments of data without necessarily displaying the data to the user would be an advantage. There are three approaches to manipulating a LOB in Oracle;

We can use the special package DBMS_LOB provided for manipulating LOBS and BFILES. DBMS_LOB is a package that is based on working with LOB locators. It consists of a number of routines for manipulating LOBS. The DBMS_LOB package routines would normally be used within a PL/SQL procedure but some of the DBMS_LOB functions that deal with LOBS can be used directly in SQL. More details are given in Chapters 8 and Chapters 11.

With an object-relational database (ORDBMS) it is possible to create object types that are more complex than the simple data types in SQL. User-defined data types make it easier for applications developers to work with complex data. This approach also gives the developer maximum flexibility in their design because both the relational and object properties can be exploited. This means that we would be able to create object types with more complex structures, for example if we stored a video object we could hold both media data and the related metadata. These object types can then be used in a number of ways. In Oracle an object type is equivalent in concept to a class in UML and would have three kinds of components (Figure 3.2): it must have a name that uniquely identifies it within the database schema; it can have attributes that are either built-in types or object types; and methods that describe the operations that can be applied to the object type. This facility has given the developer the ability to introduce the concepts of object-oriented development into databases, giving the basis for the development of object-relational databases.

Figure 3.2. An object type’s components.

In the next section we will look at the object types available in interMedia that integrate data types based on BLOBS and BFILES with other useful data and methods. We will illustrate their application by seeing how these could be used to develop the Picture Book database.

First, we will look at the way media data is stored in Oracle interMedia and what other attributes are included.

There are four object types available in interMedia for different kinds of media:

interMedia provides an object-relational type, known as ORDDoc, that allows a column to hold a mixture of image, audio, and video data, or other heterogeneous media data. All these types belong to the ORDSYS schema and store data source information in an object-relational type, known as ORDSource, that holds the actual digital media data. In addition to specific attributes for the different kind of media (Table 3.2), another advantage is that the object types include a number of data manipulation methods, for example, for image media using ORDImage:

Although interMedia includes object types for the different media (i.e., ORDAudio, ORDImage, ORDVideo, and ORDDoc), these all have a common data storage model. The media data could be stored as a BLOB under transaction control or outside the database as a BFILE. In that case a pointer is stored in the database and the media itself stored in a BFILE or HTTP server-based URL, or the source may be located on a special external media server. This data can be imported into a BLOB in the database when it is required for transaction processing. However, with new developments it would always be recommended to store the data as a BLOB. We can also process the interMedia data types by using the DBMS_LOB package in the same way as conventional BLOBS but this is usually unnecessary as more extensive methods are provided by interMedia.

Before we consider the separate object types, we will briefly look at the attributes of ORDSource because occasionally we may need to access its attributes. This base type can accommodate media data as a BLOB or a BFILE, in which case the location and name of the data file is stored. Although ORDSource has object methods, these should not be called directly. Instead, we should invoke a wrapper method of the media object corresponding to the ORDSource method. We can think of the wrapper as a software adapter or shell that isolates the ORDSource component from other components and provides a cleaner or more specific way of using complicated classes. All the interMedia object types provide wrapper methods to do the following:

The ORDSource object is embedded within the other interMedia object types and we do not deal with it directly since Oracle Corporation does not recommend that we use this object type. We would normally use the multimedia data types; for example, we could specify the photos table so that it contains interMedia objects. Recall that we defined a table

CREATE TABLE photos
(id           NUMBER PRIMARY KEY,
 description  VARCHAR2(40) NOT NULL,
 location     VARCHAR2(40),
 image        blob,
 thumb        blob);

that could be specified instead as

CREATE TABLE photos
(id          NUMBER PRIMARY KEY,
 description VARCHAR2(40) NOT NULL,
 location    VARCHAR2(40),
 image       ORDSYS.ORDIMAGE,
 thumb       ORDSYS.ORDIMAGE);

Since Oracle interMedia uses object types, an instance of these object types will consist of the media data itself, the methods associated with it, and metadata about the instance, which makes it much easier to develop media applications. We can insert an image into this table using INIT() methods for the interMedia type ORDIMAGE as follows:

INSERT INTO photos
VALUES
(1234, 'Outside Senate House - Graduation 2004', 'Cambridge',
ORDSYS.ORDIMAGE.INIT('FILE', 'PHOTO_DIR', 'graduation.jpg'),
ORDSYS.ORDIMAGE.INIT());

When this row is added to the table, the ORDImage INIT() method inserts a LOB locator into the image column to specify the external target file in the PHOTO_DIR directory and initializes the thumb column as empty rather than NULL. We discuss this method in more detail later.

In interMedia, Oracle has used object-relational concepts to create a set of multimedia object types to make it easier for the database designer to create multimedia applications. The object types have associated methods that cover common media requirements and image processing; for example, it provides methods getContent() and process(), which will already be available so we will not need to develop them specially for the application. This also has another advantage in that it allows databases to be developed in a generic style so that media data can be exchanged between applications. Another advantage is that the data types can still be accessed through a relational approach using PL/SQL and OCI or an object approach can be used based, for example, on C++ or JAVA. A database designer can also use interMedia to

By using these interMedia data types we can then use the methods for

When we deal with a conventional SQL data type, such as CHAR, we are not conscious that the CHAR data type has methods associated with it. In fact SQLPLUS provides about 15 methods that we know as built-in functions for this data type (UPPER, LOWER, SOUNDEX, SUBSTR, and so on). The interMedia object types also have a useful set of methods. In Chapter 8 we look at these methods in greater detail.

Recall that if using interMedia, we would treat the Picture Book table differently.

CREATE TABLE photos
(id          NUMBER PRIMARY KEY,
 description VARCHAR2(40) NOT NULL,
 location    VARCHAR2(40),
 image       ORDSYS.ORDIMAGE,
 thumb       ORDSYS.ORDIMAGE);

This structure would allow us to include data relevant to the application, such as the description and location of the images, separately from data about the images, such as height, width, and MIME type, which would be encapsulated in the ORDIMAGE data type.

INSERT INTO photos(id, description, image)
VALUES (1255, 'my image', ORDSYS.ORDImage.init());

CREATE OR REPLACE PROCEDURE init_image_type
AS
BEGIN
 INSERT INTO photos (id,description, image)
   VALUES (1235, 'graduates in Cambridge',
ORDSYS.ORDImage.INIT('FILE', 'PHOTO_DIR','graduation.jpg'));
 COMMIT;
END;

obj.importFrom(ctx,'file','PHOTO_DIR','Graduation.jpg'),

CREATE OR REPLACE PROCEDURE import_ext_image
AS
 obj ORDSYS.ORDImage;
 ctx RAW(64) :=NULL;
BEGIN
 SELECT p.image INTO obj FROM photos p
  WHERE p.id  = 1235 FOR UPDATE;
 -- set source to a file
 -- import data
 obj.importFrom(ctx,'file','PHOTO_DIR','Graduation.jpg'),
 -- check size
 DBMS_OUTPUT.PUT_LINE('Length is ' || obj.getContentLength);
 DBMS_OUTPUT.PUT_LINE('Source is ' || obj.getSource());
 UPDATE photos p SET p.image = obj WHERE p.id = 1235;
 COMMIT;
SELECT p.image INTO obj FROM photos p
  WHERE p.id  = 1235 FOR UPDATE;
IF (obj.isLocal()= True ) THEN DBMS_OUTPUT.PUT_LINE('Local is
set true'),
ELSE DBMS_OUTPUT.PUT_LINE('Local is set FALSE'),
END IF;
END;

ctx raw(64) := NULL;

SQL> EXECUTE import_ext_image;
Length is 73874
Source is file://PHOTO_DIR/Graduation.jpg
Local is set true

PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE image_thumb
 (img_id IN NUMBER)
 AS
   timage ORDSYS.ORDImage;
 BEGIN
  SELECT image INTO timage FROM photos
   WHERE id = img_id FOR UPDATE;
  timage.process('maxScale=32 32'),
  UPDATE photos p SET thumb = timage
   WHERE id = img_id;
  COMMIT;
  EXCEPTION
   WHEN ORDSYS.ORDImageExceptions.DATA_NOT_LOCAL THEN
    DBMS_OUTPUT.PUT_LINE('Data is not local'),
 END;

SQL> execute image_thumb(1235);

DBMS_OUTPUT.PUT_LINE('Data is not local'),

image width = 1407
image height = 1320
image size = 309128
image file type = JFIF
image type = 24BITRGB
image compression = JPEG-PROGRESSIVE
image mime type = image/jpeg

CREATE TABLE audio_ord
(id    NUMBER PRIMARY KEY,
 description VARCHAR2(40) NOT NULL,
 audio       ORDSYS.ORDAudio,);

BEGIN
INSERT INTO audio_ord(id,description, audio)
VALUES (1729, 'School String Festival Beethovens 9th ',
ORDSYS.ORDAudio.init());
 COMMIT;
END;

UPDATE audio_ord
 SET
audio=ORDSYS.ORDAUDIO.INIT('FILE','AUDIO_DIR','BEETHOVEN.WAV'
);

CREATE OR REPLACE PROCEDURE get_audio_props
AS
 obj ORDSYS.ORDAudio;
 ctx RAW(64) :=NULL;
BEGIN
 SELECT p.audio INTO obj FROM audio_ord p
  WHERE p.id = 1729 FOR UPDATE;
 obj.setProperties(ctx,TRUE);
 DBMS_OUTPUT.PUT_LINE('format: ' || obj.getformat);
 DBMS_OUTPUT.PUT_LINE('encoding: ' || obj.getEncoding);
 DBMS_OUTPUT.PUT_LINE(
                  'numberOfChannels: ' ||
TO_CHAR(obj.getNumberOfChannels));
 DBMS_OUTPUT.PUT_LINE('samplingRate: ' ||
TO_CHAR(obj.getSamplingRate));
 DBMS_OUTPUT.PUT_LINE('sampleSize: ' ||
TO_CHAR(obj.getSampleSize));
 UPDATE  audio_ord p set p.audio = obj
   WHERE  p.id = 1729;
 COMMIT;
 EXCEPTION
  WHEN ORDSYS.ORDAudioExceptions.METHOD_NOT_SUPPORTED THEN

DBMS_OUTPUT.PUT_LINE('ORDAudioExceptions.METHOD_NOT_SUPPORTED
caught'),
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('EXCEPTION caught'),
END;

CREATE OR REPLACE TYPE ORDDoc
AS OBJECT
(
  -- ATTRIBUTES
source               ORDSource,
format               VARCHAR(80),
mimeType             VARCHAR(80),
contentLength        INTEGER,
comments                 CLOB,

CREATE TABLE doc_ord
  (id               NUMBER PRIMARY KEY,
   description VARCHAR2(40) NOT NULL,
   my_doc      ORDSYS.ORDDoc)

INSERT INTO doc_ord VALUES
 ('1241','the grape in many wines such as Chablis',
ORDSYS.ORDdoc.INIT('FILE', 'PHOTO_DIR','CHARDONNAY.JPEG'));

INSERT INTO doc_ord VALUES
 ('1242','A recipe for Hungarian Goulash',
ORDSYS.ORDdoc.INIT('FILE', 'PHOTO_DIR','recipe.doc'))

create OR REPLACE PROCEDURE set_doc_prop
 AS
   obj ORDSYS.ORDDoc;
   ctx RAW(64) :=NULL;
 BEGIN
   SELECT my_doc INTO obj FROM doc_ord
     WHERE id = 1242 FOR UPDATE;
   obj.setProperties(ctx,FALSE);
   DBMS_OUTPUT.put_line('format: ' || obj.getformat());
   UPDATE  doc_ord SET my_doc = obj
     WHERE  id=1242;
   COMMIT;
   EXCEPTION
    WHEN ORDSYS.ORDDocExceptions.DOC_PLUGIN_EXCEPTION THEN
     DBMS_OUTPUT.put_line('DOC PLUGIN EXCEPTION caught'),
    WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('EXCEPTION caught'),
 END;

CREATE TABLE video_ord
(      id          NUMBER PRIMARY KEY,
       description VARCHAR2(40) NOT NULL,
       video       ORDSYS.ORDVideo);

INSERT INTO video_ord VALUES
 ('1234','some pond life', ORDSYS.ORDVIDEO.INIT('FILE',
'VIDEO_DIR','CYCLIDIUM_GLAUCOMA.MOV'))
COMMIT;

CREATE OR REPLACE PROCEDURE get_video_props
AS
 obj ORDSYS.ORDVideo;
 tempLob   CLOB;
 ctx RAW(64) :=NULL;
BEGIN
 SELECT p.video INTO obj FROM video_ord p
  WHERE p.id  = 1234;
 DBMS_OUTPUT.PUT_LINE('getting comma separated list of all
attributes'),
 DBMS_OUTPUT.PUT_LINE('--------------------------------------
------- '),
 DBMS_LOB.CREATETEMPORARY(tempLob, FALSE, DBMS_LOB.CALL);
 obj.getAllAttributes(ctx,tempLob);
 DBMS_OUTPUT.PUT_LINE(DBMS_LOB.substr(tempLob,
DBMS_LOB.getLength(tempLob),1));
 COMMIT;
 EXCEPTION
  WHEN ORDSYS.ORDVideoExceptions.METHOD_NOT_SUPPORTED THEN
   DBMS_OUTPUT.PUT_LINE('VIDEO METHOD_NOT_SUPPORTED EXCEPTION
caught'),
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT'),
END;

This ISO/IEC standard defines object-relational types for images and image characteristics. It was developed in response to requests to deal with multimedia applications and issues of interoperability that had emerged with the popularity of Web-based user interfaces and multimedia delivery systems. The proposed standard was initially known as SQL/MM (MM for multimedia). It was originally intended to cover a number of different application domains such as full-text data, spatial data, and image data (still and moving). Later, data mining was added to the standard.

In addition, it was intended that the media data should be accessed through ordinary SQL:1999 facilities or by expressions that invoke SQL stored routines. SQL/MM is a multipart standard, however, unlike SQL itself, the various parts of SQL/MM are quite independent from one another. There is one common part known as the Framework that is found in Part 1. This provides definitions of common concepts used in all the other parts and the relationship between them. In particular, it describes the manner in which the other parts use SQL’s structured user-defined types to define the types required by the subject matter of each part.

Since the parts of the standard that deal with full-text, spatial data and data mining do not concern us here, we will focus on the standard for images.

SQL/MM Still Image is the part of the SQL/MM standard that provides structured user-defined types that would allow you to store new images into a database, retrieve them, modify them in various ways, and—most importantly—locate them by applying various “visual” predicates to collections of images. In SQL/MM Still Image, images are represented using an SQL:1999 structured type called SI_StillImage. This type can be used to store collections of picture elements (pixels) representing two-dimensional images.

The images can be stored in several formats, depending on what the underlying implementation supports—for example, formats such as JPEG, TIFF, and GIF are commonly supported as input and output formats, as well as formats in which images are stored and manipulated. The SI_StillImage type also encapsulates metadata information about each image, such as its format, its dimensions (height and width in pixels), its color space, and so forth. Methods applied to SI_StillImage instances include routines to scale an image (change its size proportionally); to crop an image (remove pixels both horizontally and vertically); and to create a thumbnail image.

A related set of object types is included in the standard to describe various features of images, for example:

These object types are provided within Oracle interMedia for interoperability purposes. A developer needs to consider whether to use it or ORDImage in an application. Some features that are available through ORDImage are not specified by the SQL/MM Still Image standard, and therefore are not available for StillImage objects:

In addition, the following image-matching features are not specified by the SQL/MM Still Image standard, and therefore are not available for StillImage objects:

One of the ideas behind the SQL/MM standard was that SQL should be used to query the rich media data in as straightforward a way as possible. By combining several features of an image, it is possible to write queries that can retrieve from a very large image base a much smaller collection of images from which we can quickly select the exact image we want. It is also possible to screen collections of images to find images of potential interest for various reasons. For example, checking whether a new logo might conflict with other logos that have already been copyrighted, using an SQL statement like this one:

SELECT id
FROM photo_table
WHERE SI_findTexture(new_image).
SI_Score(image) > 1.2

Additional challenges are posed by moving images, such as digitized video. That sort of data is not addressed by SQL/MM Still Image, but it is possible that some future part of SQL/MM will be oriented toward moving images.

Oracle interMedia provides support for the first edition of the ISO/ IEC 13249-5:2001 SQL/MM Part 5: Still Image Standard through the SI_StillImage object type. This is a complex object type. The media data within the object type is held as the ORDSource object type that was described in detail earlier. Each object type includes attributes, methods, and associated SQL functions and procedures. In addition, Oracle has added some extra attributes. Although the SQL/MM standard does not provide support for all the features currently offered by the ORDImage object type in interMedia described next, the use of the SQL/MM standard interface may make some applications more portable across various vendor databases.

The SI_StillImage object type represents digital images with inherent image characteristics such as height, width, format, and so on. It is created in the ORDSYS schema with invoker rights and it is declared as INSTAN-TIABLE and NOT FINAL (see Chapter 8).

We can see from Table 3.5 that the SI_StillImage type is a complex type where a number of attributes are themselves object types known as collection types (e.g., colorsList). These are explained in greater detail in Chapter 8. You can obtain a full description by typing in SQL*PLUS, “DESCRIBE SI_StillImage.”

These attributes are listed to help understanding of the object type, however, it is recommended to access the properties of SI_StillImage type through its methods as the attributes are subject to change since a revised version of the SQL/MM standard is expected soon. To illustrate its use we could use the SI_StillImage object type to define the images in a Photo Store table, as follows.

CREATE TABLE photos_SI
(      id          NUMBER PRIMARY KEY,
       description VARCHAR2(40) NOT NULL,
       location    VARCHAR2(40),
       image       SI_StillImage,
       thumb       SI_StillImage)

The following example demonstrates how to insert a StillImage object into a database table. This example uses the PL/SQL package DBMS_LOB.LOADFROM FILE to load a BFILE into a temporary BLOB, which is then inserted into the column attribute. Alternatively, it would be possible to use SQL*Loader. Typically, you would use the PL/ SQL package if you were inserting objects one-by-one, and you would use SQL*Loader to insert objects in a batch job.

CREATE OR REPLACE PROCEDURE still_insert
AS
   lobd blob;
   fils BFILE := BFILENAME('PHOTO_DIR','cats.jpg'),
BEGIN
   DBMS_LOB.CREATETEMPORARY(lobd, TRUE);
   DBMS_LOB.fileopen(fils, DBMS_LOB.file_readonly);
   DBMS_LOB.LOADFROMFILE(lobd, fils,
   DBMS_LOB.GETLENGTH(fils));
   DBMS_LOB.FILECLOSE(fils);
   INSERT INTO photos_si (id,description,location, image)
       VALUES(1239, 'Two Siamese cats - Ben and Emma',
          'Buckinghamshire',new ORDSYS.SI_StillImage(lobd));
   DBMS_LOB.FREETEMPORARY(lobd);
   COMMIT;
END;

An object type is a simple definition of the class that will be stored in the data dictionary. For example, we could define a person object type. An example of a full specification of object and attributes would be:

CREATE TYPE person_t AS OBJECT
(       first_name     CHAR(20),
        second_name    CHAR(20),
        d_o_b          DATE);

The advantage of this approach would be that we could have a standard definition of a person that can be incorporated into any relational table of our database ensuring consistency throughout the enterprise. Alternatively, object types can be used to create object tables. Let us look again at the first table definition for the Photo Book:

CREATE TABLE photos
(      id          NUMBER PRIMARY KEY,
       description VARCHAR2(40) NOT NULL,
       location    VARCHAR2(40),
       image       BLOB,
       thumb       blob);

Instead we could create a simple object consisting of the same SQL data types:

CREATE TYPE photo_t AS OBJECT
 (      id          NUMBER,
        description VARCHAR2(40),
        image       BLOB,
        thumb       BLOB);

We can use object types in two ways: to specify an attribute in a relational table or to generate an object table of the same type. The object type can then be used to set up an object table for the photo_table type with a primary key based in the id column:

CREATE TABLE photo_table OF photo_t
(CONSTRAINT PK_photo_tab PRIMARY KEY(id))

This has the advantage of avoiding the storage and performance overhead of maintaining the 16-byte OID column and its index. Instead of using the system-generated OIDs, we used a CREATE TABLE statement to specify that the system use the primary key column(s) as the OIDs of the objects in the table. This can appear to be like a normal relational table so we can DESCRIBE, SELECT, INSERT, and UPDATE.

SELECT description FROM photo_table;

A system-defined constructor method is generated automatically for every object type. The name of the constructor method is the same as the name of the object type. We can INSERT into the photo_table using the constructor method:

INSERT INTO photo_table
VALUES
(photo_t(1234, 'Outside Senate House - Graduation 2004',
'Cambridge', EMPTY_BLOB(),EMPTY_BLOB()))

The advantage of this development approach is that it would give a standardized table for images that could be used to meet many requirements. This object type, just like a class, can be used to specify the structure of the data and the ways of operating on it. An instance of data that is structured according to the object type and stored in the database is an object. Since an object type is a concept or a template, when it is defined in the database it does not result in any allocation of storage. Since Oracle is following an object-relational paradigm instead of storing the objects in the database, we can create an object table that is defined for the purpose of holding object instances of a particular type.

CREATE TYPE dublin_core_t    AS OBJECT
(     identifier        CHAR(10),
      subject           VARCHAR2(80),
      title             VARCHAR2(80),
      creator           VARCHAR2(80),
      publisher         VARCHAR2(60),
      description       ORDSYS.ORDDOC,
      contributor       VARCHAR2(80),
      subject_date      DATE,
      resource          VARCHAR2(100),
      format            VARCHAR2(30),
      relation          VARCHAR2(100),
      source            VARCHAR2(100),
      language          CHAR(10),
      coverage          VARCHAR2(100),
      rights            VARCHAR2(100))

CREATE TABLE dublin_core_table OF dublin_core_t
(CONSTRAINT PK_dublin_core PRIMARY KEY(identifier))

This chapter has introduced the storage options for the developer of rich-media databases. We started by considering the SQL:1999 large binary object use in databases. However, when we deal with media it is essential to know the source of the data and who owns the data and issues, such as compression, file type, etc. This metadata needs to be stored in such a way that it is integrated as much as possible with the media data and cannot be separated from it. For these reasons an object-relational approach is preferable.

Therefore, the chapter introduced object types and looked at the implications of designing user-defined types and using SQL/MM data types such as SI_StillImage as well as the interMedia data types.