3
interMedia Examples

This chapter provides examples that show common operations with Oracle interMedia. Examples are presented by audio, media, image, and video data groups followed by a section that describes how to extend interMedia to support a new data source.

3.1 Audio Data Examples

Audio data examples using interMedia include the following common operations:

Defining a song object named songObject
Creating an object table named SongsTable
Creating a list object named songList that contains a list of songs
Defining the implementation of the songList object
Creating a CD object and CdTable table
Inserting a song into the SongsTable table
Inserting a CD into the CdTable table
Loading a song into the SongsTable table
Inserting a reference to a song object into the songs list in the CdTable table
Adding a CD reference to a song
Retrieving audio data from a song in a CD
Extending interMedia to support a new audio data format
Extending interMedia with new object types
Using interMedia with object views
Using a set of scripts for creating and populating an audio table from a BFILE data source

Reference information on the methods used in these examples is presented in Chapter 6.

3.1.1 Defining a Song Object

Example 3-1 describes how to define a Song object.

Example 3-1 Define a Song Object

CREATE TYPE songObject as OBJECT (
  cdRef       REF CdObject,   -- REF into the cd table
  songId      VARCHAR2(20),
  title       VARCHAR2(4000),
  artist      VARCHAR2(4000),
  awards      VARCHAR2(4000),
  timePeriod  VARCHAR2(20),
  duration    INTEGER,        
  clipRef     REF clipObject, -- REF into the clips table (music video)
  txtcontent  CLOB,        
  audioSource ORDSYS.ORDAUDIO 
);

3.1.2 Creating an Object Table SongsTable

Example 3-2 describes how to create an object table named SongsTable.

Example 3-2 Create a Table Named SongsTable

CREATE TABLE SongsTable of songObject (UNIQUE (songId), songId NOT NULL);

3.1.3 Creating a List Object Containing a List of References to Songs

Example 3-3 describes how to create a list object containing a list of references to songs.

Example 3-3 Create a List Object Containing a List of References to Songs

CREATE TYPE songNstType AS TABLE of REF songObject;

CREATE TYPE songList AS OBJECT (songs songNstType,
     MEMBER PROCEDURE addSong(s IN REF songObject));

3.1.4 Defining the Implementation of the songList Object

Example 3-4 describes how to define the implementation of the songList object.

Example 3-4 Define the Implementation of the songList Object

CREATE TYPE BODY songList AS
  MEMBER PROCEDURE addSong(s IN REF songObject) 
  IS
    pos INTEGER := 0;
  BEGIN
    IF songs IS NULL THEN
      songs := songNstType(NULL);
      pos := 0;
    ELSE
      pos := songs.count;
    END IF;
    songs.EXTEND;
    songs(pos+1) := s;
  END;
END;

3.1.5 Creating a CD Object and a CD Table

This section describes how to create a CD object and a CD table of audio clips that includes, for each audio clip, the following information:

Item ID
CD DB ID
CD title
CD artist
CD category
Copyright
Name of producer
Awards
Time period
Rating
Duration
Text content
Cover image
Songs

Example 3-5 creates a CD object named CdObject, and a CD table named CdTable that contains the CD information.

Example 3-5 Create a CD Table Containing CD Information

CREATE TYPE CdObject as OBJECT (
  itemId           INTEGER,      
  cddbID        INTEGER,
  title         VARCHAR2(4000),
  artist           VARCHAR2(4000),
  category      VARCHAR2(20),
  copyright     VARCHAR2(4000),
  producer      VARCHAR2(4000),
  awards        VARCHAR2(4000),
  timePeriod    VARCHAR2(20),
  rating        VARCHAR2(256),
  duration      INTEGER,      
  txtcontent    CLOB,         
  coverImg      REF ORDSYS.ORDImage,
  songs         songList);

CREATE TABLE CdTable OF CdObject (UNIQUE(itemId), itemId NOT NULL)
        NESTED TABLE songs.songs STORE AS song_store_table;

3.1.6 Inserting a Song into the SongsTable Table

Example 3-6 describes how to insert a song into the SongsTable table.

Example 3-6 Insert a Song into the SongsTable Table

-- Insert a song into the songs table
INSERT INTO SongsTable VALUES (NULL,
                               '00',
                               'Under Pressure',
                               'Queen',
                               'no awards',
                               '80-90',
                               243,
                               NULL,
                               EMPTY_CLOB(),
                               ORDSYS.ORDAudio.init());

-- Check songs insertion
SELECT s.title 
FROM   SongsTable s 
WHERE songId = '00';

3.1.7 Inserting a CD into the CdTable Table

Example 3-7 describes how to insert a CD into the CdTable table.

Example 3-7 Insert a CD into the CdTable Table

-- Insert a cd into the cd table
INSERT INTO CdTable VALUES (1, 23232323, 
                            'Queen Classics', 
                            'Queen', 
                            'rock', 
                            'BMV Company',
                            'BMV',
                            'Grammy',
                            '80-90',
                            'no rating',
                            4000,             -- in seconds
                            EMPTY_CLOB(),
                            NULL,
                            songList(NULL));

-- Check cd insertion
SELECT cd.title 
FROM   Cdtable cd;

3.1.8 Loading a Song into the SongsTable Table

Example 3-8 describes how to load a song into the SongsTable table. This example requires an AUDDIR directory to be defined; see the comments in the example.

Example 3-8 Load a Song into the SongsTable Table

-- Load a Song into the SongsTable
-- Create your directory specification below 
-- CREATE OR REPLACE DIRECTORY AUDDIR AS '/audio/';
-- GRANT READ ON DIRECTORY AUDDIR TO PUBLIC WITH GRANT OPTION;
DECLARE
        audioObj ORDSYS.ORDAUDIO;
        ctx RAW(4000) := NULL;
BEGIN
        SELECT S.audioSource INTO audioObj 
        FROM   SongsTable S
        WHERE  S.songId = '00'
        FOR UPDATE;

        audioObj.setSource('file', 'AUDDIR', 'UnderPressure.au');
        audioObj.import(ctx);
        audioObj.setProperties(ctx);

        UPDATE SongsTable S 
        SET    S.audioSource = audioObj 
        WHERE  S.songId = '00';
        COMMIT;
END;

-- Check song insertion
DECLARE
     audioObj ORDSYS.ORDAUDIO;
     ctx RAW(4000) := NULL;
BEGIN
     SELECT S.audioSource INTO audioObj
     FROM   SongsTable S
     WHERE  S.songId = '00';

     dbms_output.put_line('Content Length: ' ||
                     audioObj.getContentLength(ctx));
     dbms_output.put_line('Content MimeType: ' ||
                     audioObj.getMimeType());
END;

3.1.9 Inserting a Reference to a Song Object into the Songs List in the CdTable Table

Example 3-9 describes how to insert a reference to a song object into the songs list in the CdTable table.

Example 3-9 Insert a Reference to a Song Object into the Songs List in the CdTable Table

-- Insert a reference to a SongObject into the Songs List in the CdTable Table
DECLARE
        songRef REF SongObject;
        songListInstance songList;
BEGIN
        SELECT REF(S) into songRef 
        FROM   SongsTable S
        where  S.songId = '00';

        SELECT C.songs INTO songListInstance
        FROM   CdTable C
        WHERE  C.itemId = 1
        FOR UPDATE;

        songListInstance.addSong(songRef);

        UPDATE CdTable C 
        SET    C.songs = songListInstance
        WHERE  C.itemId = 1;

        COMMIT;
END;

-- Check insertion of ref
-- This example works for the first entry inserted in the songList
DECLARE
        song             SongObject;
        songRef          REF SongObject;
        songListInstance songList;
        songType         songNstType;
BEGIN
        SELECT C.songs INTO songListInstance
        FROM   CdTable C
        WHERE  C.itemId = 1;

        SELECT songListInstance.songs INTO songType FROM DUAL;
        songRef := songType(1);
        SELECT DEREF(songRef) INTO song FROM DUAL;

        dbms_output.put_line('Song Title: ' ||
                             song.title);
END;

3.1.10 Adding a CD Reference to a Song

Example 3-10 describes how to add a CD reference to a song.

Example 3-10 Add a CD Reference to a Song

-- Adding a cd reference to a song
DECLARE
     songCdRef  REF CdObject;
BEGIN
     SELECT S.cdRef INTO songCdRef
     FROM   SongsTable S
     WHERE  S.songId = '00'
     FOR UPDATE;

     SELECT REF(C) INTO songCdRef
     FROM  CdTable C
     WHERE C.itemId = 1;

     UPDATE SongsTable S 
     SET    S.cdRef = songCdRef
     WHERE  S.songId = '00';

     COMMIT;
END;

-- Check cd Ref
DECLARE
     cdRef REF CdObject;
     cd    CdObject;
BEGIN
     SELECT S.cdRef INTO cdRef
     FROM   SongsTable S
     WHERE  S.songId = '00';

     SELECT DEREF(cdRef) INTO cd FROM DUAL;
     dbms_output.put_line('Cd Title: ' ||
                           cd.title);
END;

3.1.11 Retrieving Audio Data from a Song in a CD

Example 3-11 describes how to retrieve audio data from a song in a CD.

Example 3-11 Retrieve Audio Data from a Song in a CD

FUNCTION retrieveAudio(itemID IN INTEGER, 
                       songId IN INTEGER) 
         RETURN BLOB IS 
 obj ORDSYS.ORDAudio; 
BEGIN 
 select S.audioSource into obj from SongsTable S 
   where S.songId = songId; 
 return obj.getContent(); 
END;

3.1.12 Extending interMedia to Support a New Audio Data Format

To support a new audio data format, implement the required interfaces in the ORDX_<format>_AUDIO package in the ORDPLUGINS schema (where <format> represents the name of the new audio data format). See Section 6.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_AUDIO package. Use the package body example in Section 6.4.2 as a template to create the audio package body. Then set the new format parameter in the setFormat call to the appropriate format value to indicate to the audio object that package ORDPLUG-INS.ORDX_<format>_AUDIO is available as a plug-in.

See Section F.1 for more information on installing your own format plug-in and running the sample scripts provided. See the fplugins.sql and fpluginb.sql files that are installed in the $ORACLE_HOME/ord/aud/demo/ directory. These are demonstration (demo) plug-ins that you can use as a guideline to write any format plug-in that you want to support. See the auddemo.sql file in this same directory to learn how to install your own format plug-in.

3.1.13 Extending interMedia with a New Type

This section describes how to extend Oracle interMedia with a new object type.

You can use any of the interMedia objects types as the basis for a new type of your own creation.

See Example 3-45 for a more complete example and description.

Note:

When a type is altered any dependent type definitions are invalidated. You will encounter this problem if you define a new type that includes an ORDAudio attribute and the interMedia ORDAudio type is altered, which always occurs during an interMedia installation upgrade.

A workaround to this problem is to revalidate all invalid type definitions with the following SQL statement:

SQL> ALTER TYPE <type-name> COMPILE;

3.1.14 Using Audio Types with Object Views

This section describes how to use audio 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.

In Example 3-12, consider the following relational table (containing no ORDAudio objects).

Example 3-12 Define a Relational Table Containing No ORDAudio Object

create table flat (
   id                NUMBER,
   description       VARCHAR2(4000),
   localData         BLOB,
   srcType           VARCHAR2(4000),
   srcLocation       VARCHAR2(4000),
   srcName           VARCHAR2(4000),
   upDateTime        DATE,
   local             NUMBER,
   format            VARCHAR2(31),
   mimeType          VARCHAR2(4000),
   comments          CLOB,
   encoding          VARCHAR2(256),
   numberOfChannels  NUMBER,
   samplingRate      NUMBER,
   sampleSize        NUMBER,
   compressionType   VARCHAR2(4000),
   audioDuration     NUMBER,
);

You can create an object view on the relational table shown in Example 3-12 as follows in Example 3-13.

Example 3-13 Define an Object View Containing an ORDAudio Object and Relational Columns

create or replace view object_audio_v as 
  select
      id,
      ORDSYS.ORDAudio( 
      ORDSYS.ORDSource(
         T.srctype, T.srcLocation, T.srcName,, T.updateTime, T.Local),
         T.description,
         T.localData, 
         T.format,
         T.mimeType,
         T.comments, 
         T.encoding, 
         T.numberOfChannels, 
         T.samplingRate, 
         T.sampleSize, 
         T.compressionType, 
         T.audioDuration)
   from flat T;

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

3.1.15 Scripts for Creating and Populating an Audio Table from a BFILE Data Source

The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://otn.oracle.com/ as an end-to-end script that creates and populates an audio table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.

The following set of scripts:

Creates a tablespace for the audio data, creates a user and grants certain privileges to this new user, creates an audio data load directory (create_auduser.sql).
Creates a table with two columns, inserts two rows into the table and initializes the object column to empty with a locator (create_audtable.sql).
Loads the audio data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importaud.sql).
Performs a check of the properties for the loaded data to ensure that it is really there (chkprop.sql).

The fifth script (setup_audschema.sql) automates this entire process by running each script in the required order. The last script (readaudio.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of audio data from the BLOB, beginning at a particular offset, until all the audio data is read. To successfully load the audio data, you must have an auddir directory created on your system. This directory contains the aud1.wav and aud2.mp3 files, which are installed in <ORACLE_HOME>/ord/aud/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_auduser.sql file.

Script 1: Create a Tablespace, Create an Audio User, Grant Privileges to the Audio User, and Create an Audio Data Load Directory (create_auduser.sql)

This script creates the auddemo tablespace. It contains a data file named auddemo.dbf of 200MB in size, an initial extent of 64K, and a next extent of 128K, and turns on table logging. Next, the auddemo user is created and given connect, resource, create library, and create directory privileges followed by creating the audio data load directory. Before running this script, you must change the create directory line to point to your data load directory location.

Note:

You must edit the create_auduser.sql file and either enter the system password in the connect statement or comment out the connect statement and run this file in the system account. You must specify the disk drive in the CREATE DIRECTORY statement. Also, create the temp temporary tablespace if you have not already created it, otherwise this file will not run.

-- create_auduser.sql
-- Connect as admin
connect system/<system password>;

-- Edit this script and either enter your system password here 
-- to replace <system password> or comment out this connect 
-- statement and connect as system before running this script.

set serveroutput on
set echo on

-- Need system manager privileges to delete a user.
-- Note: There is no need to delete auddemo user if you do not delete 
-- the auddemo tablespace, therefore comment out the next line.

-- drop user auddemo cascade;

-- Need system manager privileges to delete a directory. If there is no need to 
-- delete it, then comment out the next line.

-- drop directory auddir;

-- Delete then create tablespace.

-- Note: It is better to not delete and create tablespaces, 
-- so comment this next line out. The create tablespace statement
-- will fail if it already exists.

-- drop tablespace auddemo including contents;

-- If you uncomment the preceding line and really want to delete the 
-- auddemo tablespace, remember to manually delete the auddemo.dbf 
-- file to complete this operation. Otherwise, you cannot create 
-- the auddemo tablespace again because the auddemo.dbf file 
-- already exists. Therefore, it might be best to create this tablespace
-- once and not delete it.

create tablespace auddemo
       datafile 'auddemo.dbf' size 200M
       minimum extent 64K
       default storage (initial 64K next 128K)
       logging;

-- Create auddemo user.
create user auddemo identified by auddemo
default tablespace auddemo
temporary tablespace temp;

-- Note: If you do not have a temp tablespace already defined, you will have to 
-- create it first for this script to work. 

grant connect, resource, create library to auddemo;
grant create any directory to auddemo;

-- Note: If this user already exists, you get an error message
-- when you try and create this user again.

-- Connect as auddemo.
connect auddemo/auddemo

-- Create the auddir load directory; this is the directory where the audio 
-- files are residing.

create or replace directory auddir
       as 'e:\oracle\ord\aud\demo';
grant read on directory auddir to public with grant option;

-- Note: If this directory already exists, an error message 
-- is returned stating the operation will fail; ignore the message.

Script 2: Create the Audio Table and Initialize the Column Object (create_audtable.sql)

This script creates the audio table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.

--create_audtable.sql

connect auddemo/auddemo;
set serveroutput on
set echo on

drop table audtable;
create table audtable (id number,
       Audio ordsys.ordAudio);

-- Insert a row with empty BLOB.
insert into audtable values(1,ORDSYS.ORDAudio.init());

-- Insert a row with empty BLOB.
insert into audtable values(2,ORDSYS.ORDAudio.init());
commit;

Script 3: Load the Audio Data (importaud.sql)

This script performs a SELECT FOR UPDATE operation to load the audio data by first setting the source for loading the audio data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two audio clips to your AUDDIR directory using the names specified in this script, or modify this script to match the file names of your audio clips.

-- importaud.sql

set serveroutput on
set echo on
-- Import two files into the database.

DECLARE 
  obj ORDSYS.ORDAUDIO;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the audio file aud1.wav from the auddir directory 
-- on a local file system (srcType=file) and sets the properties.

  select Audio into obj from audtable where id = 1 for update;
  obj.setSource('file','AUDDIR','aud1.wav');
  obj.import(ctx);
  obj.setProperties(ctx);

  update audtable set audio = obj where id = 1;
  commit;

-- This imports the audio file aud2.mp3 from the auddir directory 
-- on a local file system (srcType=file) and sets the properties.

  select Audio into obj from audtable where id = 2 for update;
  obj.setSource('file','AUDDIR','aud2.mp3');
  obj.import(ctx);
  obj.setProperties(ctx);
 
  update audtable set audio = obj where id = 2;
  commit;
END;
/

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

This script performs a SELECT operation of the rows of the audio table, then gets the audio characteristics of the BLOB data to check that the BLOB data is in fact loaded.

--chkprop.sql
set serveroutput on;
--Connect auddemo/auddemo
--Query audtable for ORDSYS.ORDAudio. 
DECLARE
  audio ORDSYS.ORDAudio;
  idnum integer;
  properties_match BOOLEAN;
  ctx RAW(4000) := NULL;

BEGIN
 FOR I IN 1..2 LOOP
  SELECT id, audio into idnum, audio from audtable where id=I;
   dbms_output.put_line('audio id:          '|| idnum);

  properties_match := audio.checkProperties(ctx);
  IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded');
  END IF;

dbms_output.put_line('audio encoding:           '|| audio.getEncoding()); dbms_
output.put_line('audio number of channels:'|| audio.getNumberOfChannels());
dbms_output.put_line('audio MIME type:          '|| audio.getMimeType());
dbms_output.put_line('audio file format:        '|| audio.getFormat());
 dbms_output.put_line('BLOB Length:             '|| TO_
CHAR(audio.getContentLength(ctx)));
dbms_output.put_line('----------------------------------------------');

 END loop;
END;

Results from running the script chkprop.sql are the following:

SQL> @chkprop.sql 
audio id:          1
Check Properties Succeeded
audio encoding:        MS-PCM
audio number of channels:  1
audio MIME type:       audio/x-wav
audio file format:     WAVE
BLOB Length:           93594
----------------------------------------------
audio id:          2
Check Properties Succeeded 
audio encoding:        LAYER3
audio number of channels: 1 
audio MIME type:       audio/mpeg
audio file format:     MPGA
BLOB Length:           51537
---------------------------------------------- 
PL/SQL procedure successfully completed.

Automated Script (setup_audschema.sql)

This script runs each of the previous four scripts in the correct order to automate this entire process.

--setup_audschema.sql
-- Create auddemo user, tablespace, and load directory to 
-- hold the audio files:
@create_auduser.sql

-- Create Audio table:
@create_audtable.sql

--Import 2 audio clips and set properties:
@importaud.sql

--Check the properties of the audio clips:
@chkprop.sql

--exit;

Read Data from the BLOB (readaudio.sql)

This script creates a stored procedure that performs a SELECT operation to read a specified amount of audio data from the BLOB, beginning at a particular offset, until all the audio data is read.

--readaudio.sql

set serveroutput on
set echo on

create or replace procedure readaudio as

   obj ORDSYS.ORDAudio;
   buffer RAW (32767);
   numBytes BINARY_INTEGER := 32767;
   startpos integer := 1;
   read_cnt integer := 1;
   ctx RAW(4000) := NULL;

BEGIN

   Select audio into obj from audtable where id = 1;

   LOOP
           obj.readFromSource(ctx,startPos,numBytes,buffer);

            DBMS_OUTPUT.PUT_LINE('BLOB Length: '   || TO_CHAR(obj.getContentLength(ctx)));

             DBMS_OUTPUT.PUT_LINE('start position: '|| startPos);
             DBMS_OUTPUT.PUT_LINE('doing read: '    || read_cnt);
           startpos := startpos + numBytes;
           read_cnt := read_cnt + 1;
   END LOOP;
-- Note: Add your own code here to process the audio data being read;  
--       this routine just reads the data into the buffer 32767 bytes 
--       at a time, then reads the next chunk, overwriting the first 
--       buffer full of data. 

EXCEPTION

   WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('End of data ');
   WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
    DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION caught');

END;

/
show errors

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on; 
SQL> execute readaudio 
Content Length: 93594
start position: 1
doing read: 1
start position: 32768
doing read: 2
start position: 65535
doing read: 3
----------------
End of data

PL/SQL procedure successfully completed.

3.2 Media Data Examples

Media data examples using interMedia include the following common operations:

Defining a media object named documentObject
Creating an object table named DocumentsTable
Creating a list object named docList that contains a list of media
Defining the implementation of the docList object
Creating a library object and LibraryTable table
Inserting media into the DocumentsTable table
Inserting a library into the LibraryTable table
Loading media into the DocumentsTable table
Inserting a reference to a document object into the media list in the LibraryTable table
Adding a library reference to document
Extending interMedia to support a new media data format
Extending interMedia with new object types
Using interMedia with object views
Using the ORDDoc object type as a repository
Using a set of scripts for creating and populating a media table from a BFILE data source

Reference information on the methods used in these examples is presented in Chapter 7.

3.2.1 Defining a Media Object

Example 3-14 describes how to define a media object. You must create an empty LibraryObject object type first for the REF to work in this example. The actual LibraryObject is created in Example 3-18.

Example 3-14 Define a Media Object

-- Forward Declarations --
-- -------------------- --
CREATE OR REPLACE TYPE LibraryObject;
/

CREATE TYPE DocumentObject as OBJECT (
  LibraryRef      REF LibraryObject,   -- REF into the library table
  documentId      VARCHAR2(40),
  title           VARCHAR2(4000),
  author          VARCHAR2(4000),
  category        VARCHAR2(20),
  copyright       VARCHAR2(4000),
  publisher       VARCHAR2(4000),
  awards          VARCHAR2(4000),
  timePeriod      VARCHAR2(20),
  length          INTEGER,        
  txtcontent      CLOB,
  coverImage      REF ORDSYS.ORDImage,
  documentSource  ORDSYS.ORDDOC 
);
/
show errors

3.2.2 Creating an Object Table DocumentsTable

Example 3-15 describes how to create an object table named DocumentsTable.

Example 3-15 Create a Table Named DocumentsTable

CREATE TABLE DocumentsTable of DocumentObject (UNIQUE (documentId), documentId 
NOT NULL);

3.2.3 Creating a List Object Containing a List of References to Media

Example 3-16 describes how to create a list object containing a list of references to media.

Example 3-16 Create a List Object Containing a List of References to Media

CREATE TYPE documentNstType AS TABLE of REF DocumentObject;
/
show errors

CREATE TYPE documentList AS OBJECT (documents documentNstType,
     MEMBER PROCEDURE addDocument(d IN REF DocumentObject));
/
show errors

3.2.4 Defining the Implementation of the documentList Object

Example 3-17 describes how to define the implementation of the documentList object.

Example 3-17 Define the Implementation of the documentList Object

CREATE TYPE BODY documentList AS
  MEMBER PROCEDURE addDocument(d IN REF DocumentObject) 
  IS
    pos INTEGER := 0;
  BEGIN
    IF documents IS NULL THEN
      documents := documentNstType(NULL);
      pos := 0;
    ELSE
      pos := document.count;
    END IF;
    documents.EXTEND;
    documents(pos+1) := d;
  END;
END;
/
show errors

3.2.5 Creating a Library Object and a Library Table

This section describes how to create a Library object and a Library table of media abstracts that includes, for each media abstract, the following information:

Item ID
Library DB ID
Library title
Library author
Library category
Copyright
Name of publisher
Awards
Time period
Rating
Length of media in bytes
Text content
Cover image
Documents

Example 3-18 creates a Library object named LibraryObject, a Library table named LibraryTable that contains the Library information, and an Image table named ImageTable that contains the media cover images.

Example 3-18 Create a Library Table Containing Library Information

CREATE TYPE LibraryObject as OBJECT (
  itemId        INTEGER,      
  librarydbID   INTEGER,
  title         VARCHAR2(4000),
  author        VARCHAR2(4000),
  category      VARCHAR2(20),
  copyright     VARCHAR2(4000),
  publisher     VARCHAR2(4000),
  awards        VARCHAR2(4000),
  timePeriod    VARCHAR2(20),
  rating        VARCHAR2(256),
  length        INTEGER,      
  txtcontent    CLOB,         
  coverImg      REF ORDSYS.ORDImage,
  documents     documentsList);
/
show errors

CREATE TABLE LibraryTable OF LibraryObject (UNIQUE(itemId), itemId NOT NULL)
        NESTED TABLE documents.documents STORE AS document_store_table;
CREATE TABLE ImageTable OF ORDSYS.ORDImage;

3.2.6 Inserting Media into the DocumentsTable Table

Example 3-19 describes how to insert media into the DocumentsTable table.

Example 3-19 Insert Media into the DocumentsTable Table

-- Insert media into the documents table
INSERT INTO DocumentsTable VALUES (NULL,
                               '00',
                               'The Big Wind Storm',
                               'author',
                               'storms',
                               '1999',
                               'Windy Rivers Publishers',
                               'Classic Tales Award',
                               '1992',
                               2430000,
                               EMPTY_CLOB(),
                               NULL,
                               ORDSYS.ORDDoc.init());

-- Check media insertion
SELECT d.title 
FROM   DocumentsTable d 
WHERE documentId = '00';

3.2.7 Inserting a Library into the LibraryTable Table

Example 3-20 describes how to insert a Library into the LibraryTable table.

Example 3-20 Insert a Library into the LibraryTable Table

-- Insert a library into the library table
INSERT INTO LibraryTable VALUES (1, 23232323, 
                            'Sailing Classics', 
                            'authors', 
                            'sailing', 
                            '1998',
                            'BMV Company',
                            'Young Authors Award',
                            '90s',
                            'no rating',
                            4000000,             -- in characters
                            EMPTY_CLOB(),
                            NULL,
                            documentList(NULL));

-- Check library insertion
SELECT library.title 
FROM   Librarytable library;

3.2.8 Loading Media into the DocumentsTable Table

Example 3-21 describes how to load media into the DocumentsTable table. This example requires a DOCDIR directory to be defined; see the comments in the example.

Example 3-21 Load Media into the DocumentsTable Table

-- Load media into the DocumentsTable
-- Create your directory specification below 
-- CREATE OR REPLACE DIRECTORY DOCDIR AS '/document/';
DECLARE
        documentObj ORDSYS.ORDDOC;
        ctx RAW(4000) := NULL;
BEGIN
        SELECT D.documentSource INTO documentObj 
        FROM   DocumentsTable D
        WHERE  D.documentId = '00'
        FOR UPDATE;

        documentObj.setSource('file', 'DOCDIR', 'BigWindStorm.pdf');
        documentObj.setMimeType('application/pdf');
        documentObj.import(ctx,FALSE);
        documentObj.setProperties(ctx,FALSE);

        UPDATE DocumentsTable D 
        SET    D.documentSource = documentObj 
        WHERE  D.documentId = '00';
        COMMIT;
END;
/
-- Check document insertion
DECLARE
     documentObj ORDSYS.ORDDOC;
     ctx RAW(4000) := NULL;
BEGIN
     SELECT D.documentSource INTO documentObj
     FROM   DocumentsTable D
     WHERE  D.documentId = '00';

     dbms_output.put_line('Content Length: ' ||
                     documentObj.getContentLength());
     dbms_output.put_line('Content MimeType: ' ||
                     documentObj.getMimeType());
END;
/

3.2.9 Inserting a Reference to a Document Object into the Documents List in the LibraryTable Table

Example 3-22 describes how to insert a reference to a document object into the documents list in the LibraryTable table.

Example 3-22 Insert a Reference to a Document Object into the Documents List in the LibraryTable Table

-- Insert a reference to a DocumentObject into the Documents List in the 
LibraryTable Table
DECLARE
        documentRef REF DocumentObject;
        documentListInstance documentList;
BEGIN
        SELECT REF(D) into documentRef 
        FROM   DocumentsTable D
        where  D.documentId = '00';

        SELECT L.documents INTO documentListInstance
        FROM   LibraryTable L
        WHERE  L.itemId = 1
        FOR UPDATE;

        documentListInstance.addDocument(documentRef);

        UPDATE LibraryTable L 
        SET    L.documents = documentListInstance
        WHERE  L.itemId = 1;

        COMMIT;
END;
-- Check insertion of ref
-- This example works for the first entry inserted in the documentList
DECLARE
        document             DocumentObject;
        documentRef          REF DocumentObject;
        documentListInstance documentList;
        documentType         documentNstType;
BEGIN
        SELECT L.documents INTO documentListInstance
        FROM   LibraryTable L
        WHERE  L.itemId = 1;

        SELECT documentListInstance.documents INTO documentType FROM DUAL;
        documentRef := documentType(1);
        SELECT DEREF(documentRef) INTO document FROM DUAL;

        dbms_output.put_line('Document Title: ' ||
                             document.title);
END;
/

3.2.10 Adding a Library Reference to a Document

Example 3-23 describes how to add a library reference to a document.

Example 3-23 Add a Library Reference to a Document

-- Adding a library reference to a document
DECLARE
     documentLibraryRef  REF LibraryObject;
BEGIN
     SELECT D.libraryRef INTO documentLibraryRef
     FROM   DocumentsTable D
     WHERE  D.documentId = '00'
     FOR UPDATE;

     SELECT REF(L) INTO documentLibraryRef
     FROM  LibraryTable L
     WHERE L.itemId = 1;

     UPDATE DocumentsTable D 
     SET    D.libraryRef = documentLibraryRef
     WHERE  D.documentId = '00';

     COMMIT;
END;

-- Check library Ref
DECLARE
     libraryRef REF LibraryObject;
     library    LibraryObject;
BEGIN
     SELECT D.libraryRef INTO libraryRef
     FROM   DocumentsTable D
     WHERE  D.documentId = '00';

     SELECT DEREF(libraryRef) INTO library FROM DUAL;
     dbms_output.put_line('Library Title: ' ||
                           library.title);
END;
/

3.2.11 Extending interMedia to Support a New Media Data Format

To support a new media data format, implement the required interfaces in the ORDX_<format>_DOC package in the ORDPLUGINS schema (where <format> represents the name of the new media data format). See Section 7.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_DOC package. Use the package body example in Section 7.4.2 as a template to create the media package body. Then set the new format parameter in the setFormat call to the appropriate format value to indicate to the media object that package ORDPLUG-INS.ORDX_<format>_DOC is available as a plug-in. See Section 7.4.2 for more information about extending interMedia to support a new media data format.

3.2.12 Extending interMedia with a New Type

This section describes how to extend Oracle interMedia with a new object type.

You can use any of the interMedia objects types as the basis for a new type of your own creation.

See Example 3-45 for a more complete example and description.

Note:

When a type is altered any dependent type definitions are invalidated. You will encounter this problem if you define a new type that includes an ORDDoc attribute and the interMedia ORDDoc type is altered, which always occurs during an interMedia installation upgrade.

A workaround to this problem is to revalidate all invalid type definitions with the following SQL statement:

SQL> ALTER TYPE <type-name> COMPILE;

3.2.13 Using Document Types with Object Views

This section describes how to use document types with object views. Just as a view is a virtual table, an object view is a virtual object table.

The Oracle database 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 can 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 does not have attributes containing sensitive data or a deletion method. Object views also let you 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. See Example 3-12 and Example 3-13 for examples of defining a relational table containing no media (ORDAudio) object type and how to define an object view containing a media (ORDAudio) object type and relational columns.

3.2.14 Using the ORDDoc Object Type as a Repository

The ORDDoc document object type is most useful for applications that require the storage of different types of media, such as audio, image, video, and any other type of document in the same column so you can build a common metadata index on all the different types of media and perform searches across different types of media using this index.

Note:

You cannot use this same search technique if the different types of media are stored in different types of objects in different columns of relational tables.

Example 3-24 shows how to create a repository of media using the tdoc table. A requirement for creating the metadata index is to create a primary key constraint on column n. After initializing each row, load each row with a different media, in this case two audio clips, two video clips, and two images. For each media file, call the setProperties( ) method after each row is loaded and specify the setComments = TRUE value for this parameter to populate the comments field of the object with an extensive set of format and application properties in XML form. Because the format of each media type is natively supported by interMedia, the setProperties method is used to extract the properties from the media source and the comments field of the object is populated in XML form. If the format of the media type is not known, then the setProperties( ) method raises a DOC_PLUGIN_EXCEPTION exception. interMedia does not support any document media type file (html, pdf, doc, and so forth), therefore you must create your own format plug-in in order to extract the media attributes from the media data.

Next, use Oracle Text and create the metadata index on the comments attribute of the doc column. Then, begin to search for interesting formats, mimeTypes, and so forth.

Example 3-24 Build a Repository of Media

-- Connect as system manager to create a tablespace and a user. 
-- May need to create a temp tablespace for this to work.

CONNECT SYSTEM/MANAGER

--Create tablespace docrepository.

CREATE TABLESPACE docrepository
     DATAFILE 'docrepos.dbf' SIZE 200M
     MINIMUM EXTENT 64K
     DEFAULT STORAGE (INITIAL 64K NEXT 128K)
     LOGGING;

-- Create a docuser user.
-- Create a temp tablespace if you do not have one.

CREATE USER DOCUSER IDENTIFIED BY DOCUSER
DEFAULT TABLESPACE docrepository;
-- TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, CREATE LIBRARY to docuser;
GRANT CREATE ANY DIRECTORY TO docuser;

-- End of system manager tasks.


-- Begin user tasks.

CONNECT docuser/docuser


-- Create the docdir directory.

CREATE OR REPLACE DIRECTORY docdir
              as 'e:\oracle\ord\aud\demo';
GRANT READ ON DIRECTORY docdir TO PUBLIC WITH GRANT OPTION;

-- Create the tdoc table.

CREATE TABLE tdoc (n NUMBER CONSTRAINT n_pk PRIMARY KEY, doc ORDSYS.ORDDoc)
   STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

INSERT INTO tdoc VALUES(1, ORDSYS.ORDDoc.init());
INSERT INTO tdoc VALUES(2, ORDSYS.ORDDoc.init());

DECLARE 
  obj ORDSYS.ORDDOC;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the audio file aud1.wav from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 1 FOR UPDATE;
  obj.setSource('file','DOCDIR','aud1.wav');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx,TRUE);
UPDATE tdoc SET doc = obj WHERE n = 1;
COMMIT;

-- This imports the audio file aud2.mp3 from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 2 FOR UPDATE;
  obj.setSource('file','DOCDIR','aud2.mp3');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx, TRUE);
UPDATE tdoc SET doc = obj WHERE n = 2;
COMMIT;
END;
/

INSERT INTO tdoc VALUES(3, ORDSYS.ORDDoc.init());
INSERT INTO tdoc VALUES(4, ORDSYS.ORDDoc.init());

CREATE OR REPLACE DIRECTORY docdir
       as 'e:\oracle\ord\vid\demo';
GRANT READ ON DIRECTORY docdir TO PUBLIC WITH GRANT OPTION;

DECLARE 
  obj ORDSYS.ORDDOC;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the video file vid1.mov from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 3 FOR UPDATE;
  obj.setSource('file','DOCDIR','vid1.mov');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx,TRUE);
UPDATE tdoc SET doc = obj WHERE n = 3;
COMMIT;

-- This imports the video file vid2.mov from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 4 FOR UPDATE;
  obj.setSource('file','DOCDIR','vid2.mov');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx, TRUE);
UPDATE tdoc SET doc = obj WHERE n = 4;
COMMIT;
END;
/

INSERT INTO tdoc VALUES(5, ORDSYS.ORDDoc.init());
INSERT INTO tdoc VALUES(6, ORDSYS.ORDDoc.init());

CREATE OR REPLACE DIRECTORY docdir
       as 'e:\oracle\ord\img\demo';
GRANT READ ON DIRECTORY docdir TO PUBLIC WITH GRANT OPTION;

DECLARE 
  obj ORDSYS.ORDDOC;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the image file img71.gif from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 5 FOR UPDATE;
  obj.setSource('file','DOCDIR','img71.gif');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx, TRUE);
UPDATE tdoc SET doc = obj WHERE n = 5;
COMMIT;

-- This imports the image file img50.gif from the docdir directory 
-- on a local file system (srcType=file) and sets the properties.

SELECT doc INTO obj FROM tdoc WHERE n = 6 FOR UPDATE;
  obj.setSource('file','DOCDIR','img50.gif');
  obj.import(ctx,FALSE);
  obj.setProperties(ctx, TRUE);
UPDATE tdoc SET doc = obj WHERE n = 6;
COMMIT;
END;
/

-- Create the index using Oracle Text.
--
CREATE INDEX mediaidx ON tdoc(doc.comments) INDEXTYPE IS ctxsys.context;
COMMIT;

-- As part of the CREATE INDEX statement, you can create a preference, 
-- create media attribute sections for each media attribute, 
-- that is, format, mimeType, and contentLength.
-- For example,
--
-- Create a preference.

EXECUTE ctx_ddl.create_preference('ANNOT_WORDLIST', 'BASIC_WORDLIST');
EXECUTE ctx_ddl.set_attribute('ANNOT_WORDLIST', 'stemmer', 'ENGLISH');
EXECUTE ctx_ddl.set_attribute('ANNOT_WORDLIST', 'fuzzy_match', 'ENGLISH');

-- Create a section group.
-- Define Media Attribute sections, that is, the XML tags for the attributes 
-- or samples.

EXECUTE CTX_DDL.DROP_SECTION_GROUP('MEDIAANN_TAGS');
EXECUTE CTX_DDL.CREATE_SECTION_GROUP('MEDIAANN_TAGS','xml_section_group');
EXECUTE CTX_DDL.ADD_ZONE_SECTION('MEDIAANN_TAGS', 
'MEDIAFORMATENCODINGTAG','MEDIA_FORMAT_ENCODING_CODE');
EXECUTE CTX_DDL.ADD_ZONE_SECTION('MEDIAANN_TAGS','MEDIASOURCEMIMETYPETAG', 
'MEDIA_SOURCE_MIME_TYPE');
EXECUTE CTX_DDL.ADD_ZONE_SECTION('MEDIAANN_TAGS', 'MEDIASIZETAG','MEDIA_SIZE');
-- 
-- Add the following PARAMETERS clause to the end of the CREATE INDEX statement:
-- PARAMETERS ('section group MEDIAANN_TAGS'), so the statement appears 
-- as follows:

CREATE INDEX mediaidx ON tdoc(doc.comments) INDEXTYPE IS 
     CTXSYS.CONTEXT PARAMETERS('stoplist CTXSYS.EMPTY_STOPLIST wordlist 
     ANN_WORDLIST filter CTXSYS.NULL_FILTER section group MEDIAANN_TAGS');
COMMIT;
--
-- Now, perform a SELECT statement on the attributes in the doc.comments column.

SELECT n from tdoc;
-- Should display 6 rows.
 
SELECT n, score(99) from tdoc t WHERE CONTAINS(t.doc.comments, '(MPEG) WITHIN 
MEDIAFORMATENCODINGTAG',99)>0;
-- Should find one row for the aud2.mp3 audio file.

3.2.15 Scripts for Creating and Populating a Media Table from a BFILE Data Source

The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://otn.oracle.com/ as an end-to-end script that creates and populates a media table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.

The following set of scripts:

Creates a tablespace for the media data, creates a user and grants certain privileges to this new user, and creates a media data load directory (create_docuser.sql).
Creates a table with two columns, inserts two rows into the table and initializes the object column to empty with a locator (create_doctable.sql).
Loads the media data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importdoc.sql).
Performs a check of the properties for the loaded data to ensure that it is really there (chkprop.sql).

The fifth script (setup_docschema.sql) automates this entire process by running each script in the required order. The last script (readdoc.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of media data from the BLOB, beginning at a particular offset, until all the media data is read. To successfully load the media data, you must have a docdir directory created on your system. This directory contains the aud1.wav and aud2.mp3 files, which are installed in <ORACLE_HOME>/ord/aud/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_docuser.sql file.

Script 1: Create a Tablespace, Create a Media User, Grant Privileges to the Media User, and Create a Media Data Load Directory (create_docuser.sql)

This script creates the docdemo tablespace. It contains a data file named docdemo.dbf of 200MB in size, an initial extent of 64K, and a next extent of 128K, and turns on table logging. Next, the docdemo user is created and given connect, resource, create library, and create directory privileges followed by creating the media data load directory. Before running this script, you must change the create directory line to point to your data load directory location.

Note:

You must edit the create_docuser.sql file and either enter the system password in the connect statement or comment out the connect statement and run this file in the system account. You must specify the disk drive in the CREATE DIRECTORY statement. Also, create the temp temporary tablespace if you have not already created it, otherwise this file will not run.

-- create_docuser.sql
-- Connect as admin
connect system/<system password>;

-- Edit this script and either enter your system password here 
-- to replace <system password> or comment out this connect 
-- statement and connect as system before running this script.

set serveroutput on
set echo on

-- Need system manager privileges to delete a user.
-- Note: There is no need to delete docdemo user if you do not delete 
-- the docdemo tablespace, therefore comment out the next line.

-- drop user docdemo cascade;

-- Need system manager privileges to delete a directory. If there is no need to 
-- delete it, then comment out the next line.

-- drop directory docdir;

-- Delete then create tablespace.

-- Note: It is better to not delete and create tablespaces, 
-- so comment this next line out. The create tablespace statement
-- will fail if it already exists.

-- drop tablespace docdemo including contents;

-- If you uncomment the preceding line and really want to delete the 
-- docdemo tablespace, remember to manually delete the docdemo.dbf 
-- file to complete this operation. Otherwise, you cannot create 
-- the docdemo tablespace again because the docdemo.dbf file 
-- already exists. Therefore, it might be best to create this tablespace
-- once and not delete it.

create tablespace docdemo
       datafile 'docdemo.dbf' size 200M
       minimum extent 64K
       default storage (initial 64K next 128K)
       logging;

-- Create docdemo user.
create user docdemo identified by docdemo
default tablespace docdemo
temporary tablespace temp;

-- Note: If you do not have a temp tablespace already defined, you will have to 
-- create it first for this script to work. 

grant connect, resource, create library to docdemo;
grant create any directory to docdemo;

-- Note: If this user already exists, you get an error message
-- when you try and create this user again.

-- Connect as docdemo.
connect docdemo/docdemo

-- Create the docdir load directory; this is the directory where the media 
-- files are residing.

create or replace directory docdir
       as 'e:\oracle\ord\aud\demo';
grant read on directory docdir to public with grant option;
-- Note for Solaris, the directory specification could be '/user/local'
-- Note: If this directory already exists, an error message 
-- is returned stating the operation will fail; ignore the message.

Script 2: Create the Media Table and Initialize the Column Object (create_doctable.sql)

This script creates the media table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.

--create_doctable.sql

connect docdemo/docdemo;
set serveroutput on
set echo on

drop table doctable;
create table doctable (id number,
       Document ordsys.ordDoc);

-- Insert a row with empty BLOB.
insert into doctable values(1,ORDSYS.ORDDoc.init());

-- Insert a row with empty BLOB.
insert into doctable values(2,ORDSYS.ORDDoc.init());
commit;

Script 3: Load the Media Data (importdoc.sql)

This script performs a SELECT FOR UPDATE operation to load the media data by first setting the source for loading the media data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two media files to your DOCDIR directory using the names specified in this script, or modify this script to match the file names of your media.

-- importdoc.sql

set serveroutput on
set echo on
-- Import two files into the database.

DECLARE 
  obj ORDSYS.ORDDOC;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the audio file aud1.wav from the DOCDIR directory 
-- on a local file system (srcType=file) and sets the properties.

  select Document into obj from doctable where id = 1 for update;
  obj.setSource('file','DOCDIR','aud1.wav');
  obj.import(ctx,TRUE);
  update doctable set document = obj where id = 1;
  commit;

-- This imports the audio file aud2.mp3 from the DOCDIR directory 
-- on a local file system (srcType=file) and sets the properties.

  select Document into obj from doctable where id = 2 for update;
  obj.setSource('file','DOCDIR','aud2.mp3');
  obj.import(ctx,TRUE);
  update doctable set document = obj where id = 2;
  commit;
END;
/

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

This script performs a SELECT operation of the rows of the media table, then gets the media characteristics of the BLOB data to check that the BLOB data is in fact loaded.

--chkprop.sql
set serveroutput on;
--Connect docdemo/docdemo
--Query doctable for ORDSYS.ORDDoc. 
DECLARE
  document ORDSYS.ORDDoc;
  idnum integer;
  properties_match BOOLEAN;
  ctx RAW(4000) := NULL;

BEGIN
 FOR I IN 1..2 LOOP
  SELECT id, document into idnum, document from doctable where id=I;
   dbms_output.put_line('document id:          '|| idnum);

 dbms_output.put_line('document MIME type:          '|| document.getMimeType());
 dbms_output.put_line('document file format:        '|| document.getFormat());
 dbms_output.put_line('BLOB Length:   '|| TO_CHAR(document.getContentLength()));
dbms_output.put_line('----------------------------------------------');

 END loop;
END;
/

Results from running the script chkprop.sql are the following:

SQL> @chkprop.sql 
document id:          1
document MIME type:       audio/xwav
document file format:     WAVE
BLOB Length:           93594
----------------------------------------------
document id:          2
document MIME type:       audio/mpeg
document file format:     MPGA
BLOB Length:           51537
---------------------------------------------- 
PL/SQL procedure successfully completed.

Automated Script (setup_docschema.sql)

This script runs each of the previous four scripts in the correct order to automate this entire process.

--setup_docschema.sql
-- Create docdemo user, tablespace, and load directory to 
-- hold the media files:
@create_docuser.sql

-- Create Media table:
@create_doctable.sql

--Import 2 media clips and set properties:
@importdoc.sql

--Check the properties of the media clips:
@chkprop.sql

--exit;

Read Data from the BLOB (readdoc.sql)

This script creates a stored procedure that performs a select operation to read a specified amount of media data from the BLOB, beginning at a particular offset, until all the media data is read.

--readdoc.sql

set serveroutput on
set echo on

create or replace procedure readdocument as

   obj ORDSYS.ORDDoc;
   buffer RAW (32767);
   numBytes BINARY_INTEGER := 32767;
   startpos integer := 1;
   read_cnt integer := 1;
   ctx RAW(4000) := NULL;

BEGIN

   Select document into obj from doctable where id = 1;

   LOOP
           obj.readFromSource(ctx,startPos,numBytes,buffer);

            DBMS_OUTPUT.PUT_LINE('BLOB Length: '   || TO_CHAR(obj.getContentLength()));

             DBMS_OUTPUT.PUT_LINE('start position: '|| startPos);
             DBMS_OUTPUT.PUT_LINE('doing read: '    || read_cnt);
           startpos := startpos + numBytes;
           read_cnt := read_cnt + 1;
   END LOOP;
-- Note: Add your own code here to process the media data being read;  
--       this routine just reads the data into the buffer 32767 bytes 
--       at a time, then reads the next chunk, overwriting the first 
--       buffer full of data. 

EXCEPTION

   WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('End of data ');
   WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
    DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION caught');

END;

/
show errors

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on; 
SQL> execute readdocument 
Content Length: 93594
start position: 1
doing read: 1
start position: 32768
doing read: 2
start position: 65535
doing read: 3
----------------
End of data

PL/SQL procedure successfully completed.

3.3 Image Data Examples

Image data examples using interMedia include the following common operations:

Adding types to a new or existing table
Inserting a row using BLOB images
Populating a row using BLOB images
Inserting a row using BFILE images
Populating a row using BFILE images
Querying a row
Importing an image from an external file into the database
Retrieving images (simple read operation; no content-based retrieval)
Retrieving images similar to a comparison image (content-based retrieval)
Creating a domain index
Retrieving images similar to a comparison image using indexing operations (indexed content-based retrieval)
Copying an image
Converting an image format
Copying and converting an image in one step
Extending interMedia with new object types
Using image types with object views
Using a set of scripts for creating and populating an image table from a BFILE data source
Using a set of scripts for creating and populating an image table from an HTTP data source
Addressing Globalization Support issues

3.3.1 Adding Image Types to an Existing Table

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

photo_id     NUMBER 
photographer VARCHAR2(64)
annotation   VARCHAR2(255)

To add two new columns to the 'stockphotos' table called 'photo' using the ORDImage type and photo_sig using the ORDImageSignature type, issue the statement in Example 3-25. The photo column will store images and the photo_sig column will store image signatures, so you can later compare these images to a comparison image by means of their image signature.

Example 3-25 adds two new columns of type ORDImage and ORDImageSignature to the stockphotos table.

Example 3-25 Add New Columns of Type ORDImage and ORDImageSignature to the stockphotos Table

ALTER TABLE stockphotos 
ADD (photo ORDSYS.ORDImage, photo_sig ORDSYS.ORDImageSignature);

3.3.2 Adding Image Types to a New Table

Suppose you are creating a new table called 'stockphotos' with the following information:

Photo ID number
Photographer's name
Descriptive annotation
Photographic image
Photograph signature

The column for the photograph is for photographs of cloth patterns and uses the ORDImage type, and the column for the photograph signature 'photo_sig' uses the ORDImageSignature type. The statement in Example 3-26 creates the table and adds ORDImage and ORDImageSignature types to the new table.

Example 3-26 Create the stockphotos Table and Add ORDImage and ORDImageSignature Types

CREATE TABLE stockphotos (
  photo_id NUMBER, 
  photographer VARCHAR2(64), 
  annotation VARCHAR2(255), 
  photo ORDSYS.ORDImage, 
  photo_sig ORDSYS.ORDImageSignature);

3.3.3 Inserting a Row Using BLOB Images

To insert a row into a table that has storage for image content using the ORDImage and ORDImageSignature types, you must populate each type with an initializer. Note that this is different from NULL. Attempting to use the ORDImage or ORDImageSignature types with a NULL value results in an error.

Example 3-27 describes how to insert rows into the table using the ORDImage and ORDImageSignature types. Assume you have a table 'stockphotos' with the following columns:

photo_id      NUMBER 
photographer  VARCHAR2(64) 
annotation    VARCHER2(255) 
photo         ORDImage 
photo_sig     ORDImageSignature

If you are going to store image data in the database (in a binary large object (BLOB)), you must populate the ORDSource.localData attribute with a value and initialize storage for the localData attribute with an empty_blob( ) constructor. To insert a row into the table with empty data in the 'photo' and 'photo_sig' columns, issue the statement in Example 3-27.

Example 3-27 inserts a row into a table with empty data in the ORDImage type column.

Example 3-27 Insert a Row into a Table with Empty Data in the ORDImage Type Column

INSERT INTO stockphotos VALUES (
  1, 'John Doe', 'red plaid', 
  ORDSYS.ORDImage.init(),
  ORDSYS.ORDImageSignature.init());

3.3.4 Populating a Row Using BLOB Images

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 Oracle Call Interface (OCI) pin or lock function in OCI programs.

Example 3-28 populates a row with ORDImage BLOB data and ORDImageSignature data. See Section 3.1.15 for another set of examples for populating rows using BLOB images.

Example 3-28 Populate a Row with ORDImage BLOB Data

DECLARE
    -- application variables
    Image ORDSYS.ORDImage;
    ctx RAW(4000) := NULL;
BEGIN
    INSERT INTO stockphotos VALUES (
      1,'John Doe', red plaid,
      ORDSYS.ORDImage.init(),
      ORDSYS.ORDImageSignature.init());
    -- Select the newly inserted row for update
      SELECT photo INTO Image FROM stockphotos
           WHERE photo_id = 1 for UPDATE;
    -- Can use the getContent method to get the LOB locator.
    -- Populate the data with DBMS LOB calls or write an OCI program to
    -- fill in the image BLOB.
    -- This example imports the image file test.gif from the ORDIMGDIR
    -- directory on a local file system
    -- (srcType=FILE) and automatically sets the properties.

    Image.setSource('file','ORDIMGDIR','redplaid.gif');
    Image.import(ctx);

  UPDATE stockphotos SET photo = Image WHERE photo_id = 1;
  COMMIT;
  -- Continue processing
END;
/

An UPDATE statement is required to update the property attributes. If you do not use the UPDATE statement now, you can still commit, and the change to the image will be reflected in the BLOB attribute, but not in the properties. See Oracle9i Application Developer's Guide - Large Objects (LOBs) for more information on BLOBs.

3.3.5 Inserting a Row Using BFILE Images

To insert a row into a table that has storage for image content in external files using the ORDImage type, you must populate the type with an initializer. Note that this is different from NULL. Attempting to use the ORDImage type with a NULL value results in an error.

Example 3-29 describes how to insert rows into the table using the ORDImage type. Assume you have a table 'stockphotos' with the following columns:

photo_id     NUMBER 
photographer VARCHAR2(64)
annotation   VARCHAR2(255) 
photo        ORDImage
photo_sig    ORDImageSignature

If you are going to use the ORDImage and ORDImageSignature type columns, you must first populate the columns with a value. To populate the value of the ORDImage type column with an image stored externally in a file, you must populate the row with a file constructor.

Example 3-29 inserts a row into the table with an image called 'redplaid.gif' from the ORDIMGDIR directory.

Example 3-29 Insert a Row into a Table Pointing to an External Image Data File

INSERT INTO stockphotos VALUES (
  1,'John Doe','red plaid',
  ORDSYS.ORDImage.init('file','ORDIMGDIR','redplaid.gif'),
  ORDSYS.ORDImageSignature.init());

For a description of row insertion into an object type, see Chapter 8, and the Oracle9i Application Developer's Guide - Large Objects (LOBs) manual.

The sourceLocation argument 'ORDIMGDIR' is a directory referring to a file system directory. Note that the directory name must be in uppercase. The following sequence creates a directory named ORDIMGDIR:

-- Make a directory referring to a file system directory 
CREATE DIRECTORY ORDIMGDIR AS '<MYIMAGEDIRECTORY>'; 
GRANT READ ON DIRECTORY ORDIMGDIR TO <user-or-role>;

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

3.3.6 Populating a Row Using BFILE Images

Example 3-30 populates the row with ORDImage data stored externally in files.

Example 3-30 Populate a Row with ORDImage External File Data

DECLARE
    Image ORDSYS.ORDImage;
BEGIN
    INSERT INTO stockphotos VALUES (1,'John Doe','red plaid',
    ORDSYS.ORDImage.init('file','ORDIMGDIR','redplaid.gif'),
    ORDSYS.ORDImageSignature.init());
    -- Select the newly inserted row for update
    SELECT photo INTO Image FROM stockphotos
           WHERE photo_id = 1 FOR UPDATE;
    -- Set property attributes for the image data
    Image.setProperties;
    UPDATE stockphotos SET photo = Image WHERE photo_id = 1;
    COMMIT;
    -- Continue processing
END;
/

3.3.7 Querying a Row

Example 3-31 and Example 3-32 assume you have this table:

CREATE TABLE stockphotos (
photo_id NUMBER, 
photographer VARCHAR2(64), 
annotation VARCHAR2(255), 
photo ORDSYS.ORDImage, 
photo_sig ORDSYS.ORDImageSignature);

Example 3-31 queries the stockphotos table for the photo_id of 1 and the ORDImage data for rows with minimum photo widths (greater than 32 pixels).You must create a table alias (E in this example) when you refer to a type in a SELECT statement.

Example 3-31 Query Rows of ORDImage Data for Widths Greater Than 32 Pixels

SELECT photo_id, s.photo.getWidth()
  FROM stockphotos S
  WHERE photo_id = 1 and
        S.photo.getWidth() > 32;

Example 3-32 queries the stockphotos table for photo_id =1 and the ORDImage data for rows with minimum photo widths (greater than 32 pixels) and a minimum content length (greater than 10000 bytes).

Example 3-32 Query Rows of ORDImage Data for Widths Greater Than 32 Pixels and a Minimum Content Length

SELECT photo_id, S.photo.getCompressionFormat()
  FROM stockphotos S
  WHERE photo_id = 1 and
        S.photo.getWidth() > 32 and
        S.photo.getContentLength() > 10000;

3.3.8 Importing an Image from an External File into the Database

To import an image from an external file into the database, use the ORDImage.import method. Example 3-33 imports image data from an external file into the database. The source type, source location, and source name must be set prior to calling the import( ) method.

Example 3-33 Import an Image from an External File

DECLARE
    Image ORDSYS.ORDImage;
    ctx RAW(4000) := NULL;
BEGIN
    SELECT photo
      INTO Image FROM stockphotos
      WHERE photo_id = 1 FOR UPDATE;
    -- Import the image into the database
    Image.import(ctx);
    UPDATE stockphotos SET photo = IMAGE
           WHERE photo_id = 1;
    COMMIT;
END;
/

3.3.9 Retrieving an Image

The following examples, Example 3-38 through Example 3-41 use the table definition described in Example 3-34 that includes both an image object and image signature object for content-based retrieval of images.

Example 3-34 Table stockphotos Definition Used for Content-Based Retrieval of Images

CREATE TABLE stockphotos(photo_id INTEGER,
                 photographer VARCHAR2(64),
                 annotation   VARCHAR2(255),
                 photo ORDSYS.ORDImage,
                 photo_signature ORDSYS.ORDImageSignature);

The stockphotos table is loaded with image data as described in Example 3-35.

Example 3-35 Load the stockphotos Table with Image Data

DECLARE
    myimg ORDSYS.ORDImage;
    mysig ORDSYS.ORDImageSignature;
    x INTEGER;
    ctx RAW(4000):= NULL;
BEGIN
    -- create 4 plaid patterns, for each get an image from ORDIMGDIR directory
    INSERT INTO stockphotos(photo_id,photographer,annotation,photo,photo_sig)
        VALUES(1, 
               'John MacIvor', 
               'red plaid', 
               ORDSYS.ORDImage.init('file','ORDIMGDIR','redplaid.gif'), 
               ORDSYS.ORDImageSignature.init());
    INSERT INTO stockphotos(photo_id,photographer,annotation,photo,photo_sig)
        VALUES(2, 
               'Jane Cranston',
               'green plaid',
               ORDSYS.ORDImage.init('file','ORDIMGDIR','greenplaid.gif'),
               ORDSYS.ORDImageSignature.init());
    INSERT INTO stockphotos(photo_id,photographer,annotation,photo,photo_sig)
        VALUES(3, 
               'Clark Gordon',
               'blue plaid',
               ORDSYS.ORDImage.init('file','ORDIMGDIR','blueplaid.gif'),
               ORDSYS.ORDImageSignature.init());
    INSERT INTO stockphotos(photo_id,photographer,annotation,photo,photo_sig)
        VALUES(4,
               'Bruce MacLeod',
               'yellow plaid',
               ORDSYS.ORDImage.init('file','ORDIMGDIR','yellowplaid.gif'),
               ORDSYS.ORDImageSignature.init());
                          
    -- import images and generate signatures
    FOR x in 1..4 LOOP
        SELECT S.photo, S.photo_sig INTO myimg, mysig 
            FROM stockphotos S
            WHERE S.photo_id = x FOR UPDATE;
        myimg.import(ctx);
        mysig.generateSignature(myimg);
        UPDATE stockphotos S
            SET S.photo = myimg,
                S.photo_sig = mysig
            WHERE S.photo_id = x; 
    END LOOP;
END;
/

Rows can be read from the emp table as shown in Example 3-36 to check the contents of the table.

Example 3-36 Check the Contents of the stockphotos Table

SELECT photo_id, photographer, annotation
    FROM stockphotos
    ORDER BY photo_id;

Finally, Example 3-37 shows how to create the tablespaces needed for index creation by the imageuser user in Example 3-41.

Example 3-37 Create the Tablespaces for the Index

CONNECT system/<system-password>;

GRANT CREATE TABLESPACE TO imageuser;
GRANT DROP TABLESPACE TO imageuser;

CONNECT imageuser/imageuser;
CREATE TABLESPACE ordimage_idx_tbs_1
    DATAFILE 'e:\<ORACLE_HOME>\DATABASE\ordimage_idx_tbs_1.dbf' SIZE 1M REUSE;
CREATE TABLESPACE ordimage_idx_tbs_2
    DATAFILE 'e:\<ORACLE_HOME>\DATABASE\ordimage_idx_tbs_2.dbf' SIZE 1M REUSE;

Example 3-38 reads an image from the table and prepares it to be passed along, either directly to the end user or to the application for further processing. The program segment selects the desired photograph (where photo_id = 1) and places it in an image storage area.

Example 3-38 Retrieve an Image (Simple Read)

SET SERVEROUTPUT ON
SET ECHO ON

DECLARE
   image     ORDSYS.ORDIMAGE;
BEGIN
   -- Select the desired photograph from the stockphotos table.
   SELECT photo INTO image FROM stockphotos 
          WHERE photo_id = 1;
END;
/

3.3.10 Retrieving Images Similar to a Comparison Image (Content-Based Retrieval)

Example 3-39 performs content-based retrieval; it finds images that are similar to an image chosen for comparison.

The program segment performs the following operations:

Defines a cursor to perform the matching. The cursor sets the following weight values:
- Color: 0.2
- Texture: 0.1
- Shape: 0.4
- Location: 0.3
The example assumes that all signatures for images are generated and stored in the photo_sig column.
Selects all photo signatures in the photo_sig column to compare with the comparison image signature (compare_img) and where the photo_id is not 1 (photo_id <> 1).
Sets the threshold value at 25.
Selects the matching images, using the cursor.

Example 3-39 Retrieve Images Similar to a Comparison Image

SET SERVEROUTPUT ON
SET ECHO ON

DECLARE
   threshold    NUMBER;
   compare_sig  ORDSYS.ORDImageSignature;
   photographer VARCHAR2(64);
   annotation   VARCHAR2(255);
   photo        ORDSYS.ORDIMAGE;
   
-- Define cursor for matching. Set weights for the visual attributes.
CURSOR getphotos IS
   SELECT photograpger, annotation, photo FROM stockphotos S
   WHERE ORDSYS.IMGSimilar(S.photo_sig, compare_sig, 
                 'color="0.2" texture="0.1" shape="0.4"
                  location="0.3"', threshold)=1 AND photo_id <> 1;

BEGIN
 -- select signature of image you want to match against
   SELECT P.photo_sig INTO compare_img FROM stockphotos P 
   WHERE P.photo_id = 1;

   -- Set the threshold value.
   threshold := 25;

   -- Retrieve rows for matching images.
   OPEN getphotos;
   LOOP
      FETCH getphotos INTO photographer, annotation, photo;
      EXIT WHEN getphotos%NOTFOUND;
      -- Display or store the results.
      -- .
      -- .
   END LOOP;
   CLOSE getphotos;
END;
/

Example 3-40 finds the photo_id and score of the image that is most similar to a comparison image with respect to texture. None of the other image characteristics is considered. This example uses the IMGScore( ) operator, which is an ancillary operator used in conjunction with the IMGSimilar operator. The parameter passed to IMGScore( ) (123 in this example) is an identifier to an IMGSimilar( ) operator, indicates that the image matching score value returned by an IMGScore( ) operator is the same one used in the corresponding IMGSimilar( ) operator (with label 123). In this example, one of the three images compared to the comparison image were identical to the comparison image and showed a score of zero (0).

Example 3-40 Find photo_id and Score of Similar Image

SET SERVEROUTPUT ON
SET ECHO ON

SELECT Q.photo_id,  
       ORDSYS.IMGScore(123) SCORE 
       FROM stockphotos Q, stockphotos E 
       WHERE E.photo_id=1 AND Q.photo_id != E.photo_id AND 
             ORDSYS.IMGSimilar(Q.photo_sig, E.photo_sig, 
                                'texture=1', 20.0, 123)=1;
PHOTO_ID   SCORE
--------- ---------
        1          0

3.3.11 Creating a Domain Index

To improve performance, you can create a domain index on the image signature attribute. Example 3-41 creates an index called imgindex.

Example 3-41 Create an interMedia Index

SET SERVEROUTPUT ON
SET ECHO ON

CREATE INDEX imgindex ON stockphotos(photo_sig) 
 INDEXTYPE IS ORDSYS.ORDImageIndex
 PARAMETERS('
   ORDIMG_FILTER_TABLESPACE = ordimage_idx_tbs_1, 
   ORDIMG_INDEX_TABLESPACE = ordimage_idx_tbs_2');

As with any index, the tablespace (ordimage_idx_tbs_1 and ordimage_idx_tbs_2) must be created first.

The following recommendations are good starting points for further index tuning:

ORDIMG_FILTER_TABLESPACE -- Each signature requires approximately 350 bytes in this tablespace. The tablespace should be at least 350 times the number of signatures in the table.
ORDIMG_INDEX_TABLESPACE -- The size of the tablespace should be 100 times the size of the initial and final extents specified. For example, if an extent is 10 KB, the tablespace size should be 1 MB. The initial and final extents should be equal to each other. The size of the tablespace should also be approximately equal to the size of ORDIMG_DATA_TABLESPACE.
Typically, it will be much faster if you create the index after the images are loaded into the database and analyzed.
Creating an index for large tables can be very time consuming. When importing a large number of images, you should postpone index creation until after the import operation completes. Do this by specifying the following parameters to the IMPORT statement: INDEXES=N and INDEXNAME=<filename>. See Oracle9i Database Utilities for details.
Rollback segments of an appropriate size are required. The size depends on the size of your transactions, such as, how many signatures are indexed at one time.
Analyze the new index. See Section 2.4.

3.3.12 Retrieving Images Similar to a Comparison Image Using Index Operations (Indexed Content-Based Retrieval)

Queries for indexed and nonindexed comparisons are identical. The Oracle optimizer uses the domain index if it determines that the first argument passed to the IMGSimilar operator is a domain-indexed column. Otherwise, the optimizer invokes a functional implementation of the operator that compares the query signature with the stored signatures, one row at a time.

See Section 3.3.10 for examples of retrieving similar images. As in the example, be sure to specify the query signature as the second parameter.

3.3.13 Copying an Image

To copy an image, use the ORDImage.copy method. Example 3-42 copies image data.

Example 3-42 Copy an Image

DECLARE
    Image_1 ORDSYS.ORDImage;
    Image_2 ORDSYS.ORDImage;
BEGIN
    SELECT photo INTO Image_1
       FROM stockphotos WHERE photo_id = 1;
    SELECT photo INTO Image_2
       FROM stockphotos WHERE photo_id = 1 FOR UPDATE;
    -- Copy the data from Image_1 to Image_2
    Image_1.copy(Image_2);
    -- Continue processing
    UPDATE stockphotos SET photo = Image_2
        WHERE photo_id = 1;
    COMMIT;
END;
/

3.3.14 Converting an Image Format

To convert the image data into a different format, use the process( ) method.

Note:

The process( ) method processes only into a BLOB, so the image data must be stored locally.

Example 3-43 converts the image data to the TIFF image file format.

Example 3-43 Convert an Image Format

DECLARE
    Image ORDSYS.ORDImage;
BEGIN
    SELECT photo INTO Image FROM stockphotos
        WHERE photo_id = 1 FOR UPDATE;
    -- Convert the image to TIFF (in place)
    Image.process('fileFormat=TIFF');
    UPDATE stockphotos SET photo = Image WHERE photo_id = 1;
    COMMIT;
END;
/

3.3.15 Copying and Converting in One Step

To make a copy of the image and convert it in one step, use the processCopy( ) method.

Note:

The processCopy( ) method processes only into a BLOB, so the destination image must be set to local and the localData attribute in the source must be initialized.

Example 3-44 creates a thumbnail image, converts the image data to the TIFF image file format, copies it to a BLOB, and leaves the original image intact.

Example 3-44 Copy and Convert an Image Format

DECLARE
    Image_1 ORDSYS.ORDImage;
    Image_2 ORDSYS.ORDImage;
BEGIN
       SELECT photo INTO Image_1
       FROM stockphotos WHERE photo_id = 1;
    SELECT photo INTO Image_2
       FROM stockphotos WHERE photo_id = 2 FOR UPDATE;
    -- Convert the image to a TIFF thumbnail image and store the 
    -- result in Image_2
    Image_1.processCopy('fileFormat=TIFF fixedScale=32 32', Image_2);
    -- Continue processing
    UPDATE stockphotos SET photo = Image_2 WHERE photo_id = 2;
    COMMIT;
END;
/

Changes made by the processCopy( ) method can be rolled back. This technique may be useful for a temporary format conversion.

3.3.16 Extending interMedia with a New Type

You can use the ORDImage type as the basis for a new type of your own creation as shown in Example 3-45.

Note:

When a type is altered any dependent type definitions are invalidated. You will encounter this problem if you define a new type that includes an ORDImage attribute and the interMedia ORDImage type is altered, which always occurs during an interMedia installation upgrade.

A workaround to this problem is to revalidate all invalid type definitions with the following SQL statement:

SQL> ALTER TYPE <type-name> COMPILE;

Example 3-45 Extend Oracle interMedia with a New Object Type

CREATE TYPE AnnotatedImage AS OBJECT
    ( image ORDSYS.ORDImage,
      description VARCHAR2(2000),
      MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage),
      MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage),
      MEMBER PROCEDURE ProcessCopy(command IN VARCHAR2,
                                   dest IN OUT AnnotatedImage)
    );
/

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 object as a subtype';
  END SetProperties;
  MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage) IS
  BEGIN
    SELF.image.copy(dest.image);
    dest.description := SELF.description;
  END Copy;
  MEMBER PROCEDURE ProcessCopy(command IN VARCHAR2,
                               dest IN OUT AnnotatedImage) IS
  BEGIN
    SELF.Image.processCopy(command,dest.image);
    dest.description := SELF.description;
  END ProcessCopy;
END;
/

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

CREATE OR REPLACE DIRECTORY ORDIMGDIR AS 'C:\TESTS';

CREATE TABLE my_example(id NUMBER, an_image AnnotatedImage);
INSERT INTO my_example VALUES (1,
    AnnotatedImage(
        ORDSYS.ORDImage.init('file','ORDIMGDIR','plaid.gif'));
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;
/

3.3.17 Using 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.

In Example 3-46, consider the following relational table (containing no ORDImage objects):

Example 3-46 Define a Relational Table Containing No ORDImage Object

CREATE TABLE flat(
    id                NUMBER,
    localData         BLOB,
    srcType           VARCHAR2(4000),
    srcLocation       VARCHAR2(4000),
    srcName           VARCHAR2(4000),
    updateTime        DATE,
    local             NUMBER,
    height            INTEGER,
    width             INTEGER,
    contentLength     INTEGER,
    fileFormat        VARCHAR2(4000),
    contentFormat     VARCHAR2(4000),
    compressionFormat VARCHAR2(4000),
    mimeType          VARCHAR2(4000)
    );

You can create an object view on the relational table shown in Example 3-46 as follows in Example 3-47.

Example 3-47 Define an Object View Containing an ORDImage Object and Relational Columns

CREATE OR REPLACE VIEW object_images_v AS
    SELECT
      id,
      ORDSYS.ORDImage(
        ORDSYS.ORDSource(
          T.localData,
          T.srcType,
          T.srcLocation,
          T.srcName,
          T.updateTime,
          T.local),
        T.height,
        T.width,
        T.contentLength,
        T.fileFormat,
        T.contentFormat,
        T.compressionFormat,
        T.mimeType
        ) 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 Oracle9i Database Concepts manual for more information on defining, using, and updating object views.

3.3.18 Scripts for Creating and Populating an Image Table from a BFILE Data Source

The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://otn.oracle.com/ as end-to-end scripts that create and populate an image table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.

The following set of scripts:

Creates a tablespace for the image data, creates a user and grants certain privileges to this new user, creates an image data load directory (create_imguser.sql).
Creates a table with two columns, inserts two rows into the table and initializes the object column to empty with a locator (create_imgtable.sql).
Loads the image data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importimg.sql).
Performs a check of the properties for the loaded data to ensure that it is really there (chkprop.sql).

The fifth script (setup_imgschema.sql) automates this entire process by running each script in the required order. The last script (readimage.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of image data from the BLOB beginning at a particular offset until all the image data is read. To successfully load the image data, you must have an imgdir directory created on your system containing the img71.gif and img50.gif files, which are installed in the <ORACLE_HOME>/ord/img/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_imguser.sql file.

Script 1: Create a Tablespace, Create an Image User, Grant Privileges to the Image User, and Create an Image Data Load Directory (create_imguser.sql)

This script creates the imgdemo tablespace with a data file named imgdemo.dbf of 200MB in size, with an initial extent of 64K, a next extent of 128K, and turns on table logging. Next, the imgdemo user is created and given connect, resource, create library, and create directory privileges, followed by creating the image data load directory.

Note:

You must edit the create_imguser.sql file and either enter the system password in the connect statement or comment out the connect statement and run this file in the system account. You must specify the disk drive in the CREATE DIRECTORY statement. Also, create the temp temporary tablespace if you have not already created it, otherwise this file will not run.

-- create_imguser.sql
-- Connect as admin.
connect system/<system password>;
-- Edit this script and either enter your system password here
-- to replace <system password> or comment out this connect
-- statement and connect as system before running this script. 

set serveroutput on
set echo on

-- Need system manager privileges to delete a user.
-- Note: There is no need to delete imgdemo user if you do not delete the 
-- imgdemo tablespace, therefore comment out the next line.

-- drop user imgdemo cascade;

-- Need system manager privileges to delete a directory. If threre is 
-- no need to really delete it, then comment out the next line.

-- drop directory imgdir;

-- Delete then create the tablespace.

-- Note: It is better to not delete and create tablespaces, 
-- so comment this next line out. The create tablespace statement
-- will fail if it already exists.

-- drop tablespace imgdemo including contents;

-- If you uncomment the preceding line and really want to delete the 
-- imgdemo tablespace, remember to manually delete the imgdemo.dbf 
-- file to complete the operation. Otherwise, you cannot create 
-- the imgdemo tablespace again because the imgdemo.dbf file 
-- already exists. Therefore, it might be best to create this 
-- tablespace once and not delete it.

-- Create tablespace.
create tablespace imgdemo
       datafile 'imgdemo.dbf' size 200M
       minimum extent 64K
       default storage (initial 64K next 128K)
       logging;

-- Create imgdemo user.
create user imgdemo identified by imgdemo
default tablespace imgdemo
temporary tablespace temp;

-- Note: If you do not have a temp tablespace already defined, you will 
-- have to create it first for this script to work. 

grant connect, resource, create library to imgdemo;
grant create any directory to imgdemo;

-- Note: If this user already exists, you get an error message when you 
-- try and create this user again.

-- Connect as imgdemo.
connect imgdemo/imgdemo

-- Create the imgdir load directory; this is the directory where the image 
-- files are residing.

create or replace directory imgdir
       as 'e:\oracle\ord\img\demo';
grant read on directory imgdir to public with grant option;
-- Note: If this directory already exists, an error message 
-- is returned stating the operation will fail; ignore the message.

Script 2: Create the Image Table and Initialize the Column Object (create_imgtable.sql)

This script creates the image table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.


-- create_imgtable.sql
connect imgdemo/imgdemo;
set serveroutput on
set echo on

drop table imgtable;
create table imgtable (id number,
       Image ordsys.ordImage);

-- Insert a row with empty BLOB.
insert into imgtable values(1,ORDSYS.ORDImage.init());

-- Insert a row with empty BLOB.
insert into imgtable values(2,ORDSYS.ORDImage.init());
commit;

Script 3: Load the Image Data (importimg.sql)

This script performs a SELECT FOR UPDATE operation to load the image data by first setting the source for loading the image data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two image files to your IMGDIR directory using the names specified in this script, or modify this script to match the file names of your image files.

--importimg.sql
set serveroutput on
set echo on
-- Import the two files into the database.

DECLARE 
  obj ORDSYS.ORDIMAGE;
  ctx RAW(4000) := NULL;
BEGIN
-- This imports the image file img71.gif from the IMGDIR directory 
-- on a local file system (srcType=file) and sets the properties.

  select Image into obj from imgtable where id = 1 for update;
  obj.setSource('file','IMGDIR','img71.gif');
  obj.import(ctx);

  update imgtable set image = obj where id = 1;
  commit;

-- This imports the image file img50.gif from the IMGDIR directory 
-- on a local file system (srcType=file) and sets the properties.

  select Image into obj from imgtable where id = 2 for update;
  obj.setSource('file','IMGDIR','img50.gif');
  obj.import(ctx);

  update imgtable set image = obj where id = 2;
  commit;
END;
/

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

This script performs a SELECT operation of the rows of the image table, then gets the image characteristics of the BLOB data to check that the BLOB data is in fact loaded.

-- chkprop.sql
set serveroutput on;
--connect imgdemo/imgdemo
--Query imgtable for ORDSYS.ORDImage. 
DECLARE
  image ORDSYS.ORDImage;
  idnum integer;
  properties_match BOOLEAN;

BEGIN
 FOR I IN 1..2 LOOP
  SELECT id into idnum from imgtable where id=I;
   dbms_output.put_line('image id:          '|| idnum);

  SELECT Image into image from imgtable where id=I;

  properties_match := image.checkProperties();
  IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded');
  END IF;

   dbms_output.put_line('image height:      '|| image.getHeight());
   dbms_output.put_line('image width:       '|| image.getWidth());
   dbms_output.put_line('image MIME type:   '|| image.getMimeType());
   dbms_output.put_line('image file format: '|| image.getFileFormat());

   dbms_output.put_line('BLOB Length:      '|| TO_CHAR(image.getContentLength()));

   dbms_output.put_line('-------------------------------------------');

 END loop;
END;
/

Results from running the script chkprop.sql are the following:

SQL> @chkprop.sql
image id:          1
Check Properties Succeeded
image height:      15
image width:       43
image MIME type:   image/gif
image file format: GIFF
BLOB Length:       1124
-------------------------------------------
image id:          2
Check Properties Succeeded
image height:      32
image width:       110
image MIME type:   image/gif
image file format: GIFF
BLOB Length:       686
-------------------------------------------

PL/SQL procedure successfully completed.

Automated Script (setup_imgschema.sql)

This script runs each of the previous four scripts in the correct order to automate this entire process.

-- setup_imgschema.sql
-- Create imgdemo user, tablespace, and load directory to 
-- hold image files:
@create_imguser.sql

-- Create image table:
@create_imgtable.sql

--Import 2 images and set properties:
@importimg.sql

--Check the properties of the images:
@chkprop.sql

--exit;

Read Data from the BLOB (readimage.sql)

This script performs a SELECT operation to read a specified amount of image data from the BLOB, beginning at a particular offset until all the image data is read.

-- readimage.sql

set serveroutput on
set echo on

create or replace procedure readimage as

-- Note: ORDImage has no readFromSource method like ORDAudio
-- and ORDVideo; therefore, you must use the DBMS_LOB package to 
-- read image data from a BLOB. 

   buffer RAW (32767);
   src BLOB;   
   obj ORDSYS.ORDImage;
   amt BINARY_INTEGER := 32767;
   pos integer := 1;
   read_cnt integer := 1;

BEGIN

   Select  t.image.getcontent into src from imgtable t where t.id = 1;
   Select image into obj from imgtable t where t.id = 1;

         DBMS_OUTPUT.PUT_LINE('Content length is: '|| TO_CHAR(obj.getContentLength()));

   LOOP
         DBMS_LOB.READ(src,amt,pos,buffer);
           DBMS_OUTPUT.PUT_LINE('start position: '|| pos);
           DBMS_OUTPUT.PUT_LINE('doing read  '|| read_cnt);
         pos := pos + amt;
         read_cnt := read_cnt + 1;

-- Note: Add your own code here to process the image data being read;
-- this routine just reads data into the buffer 32767 bytes 
--  at a time, then reads the next chunk, overwriting the first 
--  buffer full of data. 
   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('----------------');
   DBMS_OUTPUT.PUT_LINE('End of data ');

END;

/
show errors

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on;
SQL> execute readimage(1);
Content length is: 1124
start position: 1
doing read  1
----------------
End of data

PL/SQL procedure successfully completed.

3.3.19 Scripts for Populating an Image Table from an HTTP Data Source

The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://otn.oracle.com/ as end-to-end scripts that create and populate an image table from an HTTP data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.

Note:

Before you run the importimg.sql script described in this section to load image data from an HTTP data source, check to ensure you have already run the create_imguser.sql and create_imgtable.sql scripts described in Section 3.3.18.

The following set of scripts performs a row insert operation and an import operation, then checks the properties of the loaded images to ensure that the images are really loaded.

Initialize the Column Object and Import the Image Data (importimghttp.sql)

This script inserts two rows into the imgtable table, initializing the object column for each row to empty with a locator, and indicating the HTTP source information (source type (HTTP), URL location, and HTTP object name). Within a SELECT FOR UPDATE statement, an import operation loads each image object into the database followed by an UPDATE statement to update the object attributes for each image, and finally a COMMIT statement to commit the transaction.

To successfully run this script, you must modify this script to point to two images located on your own Web site.

--importimghttp.sql
-- Import the two HTTP images from a Web site into the database. 
-- Running this script assumes you have already run the 
-- create_imguser.sql and create_imgtable.sql scripts.
-- Modify the HTTP URL and object name to point to two images
-- on your own Web site.

set serveroutput on
set echo on

-- Import two images from HTTP source URLs.

connect imgdemo/imgdemo;

-- Insert two rows with empty BLOB.

insert into imgtable values (7,ORDSYS.ORDImage.init(
             'http','your.web.site.com/intermedia','image1.gif'));

insert into imgtable values (8,ORDSYS.ORDImage.init(
             'http','your.web.site.com/intermedia','image2.gif'));

DECLARE 
  obj ORDSYS.ORDIMAGE;
  ctx RAW(4000) := NULL;
BEGIN
-- This imports the image file image1.gif from the HTTP source URL 
-- (srcType=HTTP), and automatically sets the properties.

  select Image into obj from imgtable where id = 7 for update;
    obj.import(ctx);
 
  update imgtable set image = obj where id = 7;
  commit;

-- This imports the image file image2.gif from the HTTP source URL
-- (srcType=HTTP), and automatically sets the properties.

  select Image into obj from imgtable where id = 8 for update;
    obj.import(ctx);
 
  update imgtable set image = obj where id = 8;
  commit;
END;
/

Check the Properties of the Loaded Data

This script performs a SELECT operation of the rows of the image table, then gets the image characteristics of the BLOB data to check that the BLOB data is in fact loaded.

--chkprop.sql 
set serveroutput on; 
--connect imgdemo/imgdemo 
--Query imgtable for ORDSYS.ORDImage. 
DECLARE 
image ORDSYS.ORDImage; 
idnum integer; 
properties_match BOOLEAN; 

BEGIN 
  FOR I IN 7..8 LOOP 
    SELECT id into idnum from imgtable where id=I; 
     dbms_output.put_line('image id: '|| idnum); 
    SELECT Image into image from imgtable where id=I for update; 
    properties_match := image.checkProperties(); 
    IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); 
    END IF; 
     dbms_output.put_line('image height: '|| image.getHeight()); 
     dbms_output.put_line('image width: '|| image.getWidth()); 
     dbms_output.put_line('image MIME type: '|| image.getMimeType()); 
     dbms_output.put_line('image file format: '|| image.getFileFormat()); 
     dbms_output.put_line('BLOB length: '|| TO_CHAR(image.getContentLength())); 
     dbms_output.put_line('-------------------------------------------'); 
  END loop; 
END; 
/

3.3.20 Addressing Globalization Support Issues

Example 3-48 shows how to use the processCopy( ) method with language settings that use the comma as the decimal point. For example, when the territory is FRANCE, the decimal point is expected to be a comma. Notice the ",75" specified as the scale factor. This application addresses Globalization Support issues.

Example 3-48 Address a Globalization Support Issue

ALTER SESSION SET NLS_LANGUAGE = FRENCH;
ALTER SESSION SET NLS_TERRITORY = FRANCE;
DECLARE
    myimage ORDSYS.ORDImage;
    mylargeimage ORDSYS.ORDImage;
BEGIN
    SELECT photo, large_photo INTO myimage, mylargeimage
      FROM emp FOR UPDATE;
    myimage.setProperties();
    myimage.ProcessCopy('scale=",75"', mylargeimage);
    UPDATE emp SET photo = myimage, large_photo = mylargeimage;
    COMMIT;
END;
/

3.4 Video Data Examples

Video data examples using interMedia include the following common operations:

Defining a clip object named clipObject
Creating an object table named clipsTable
Creating a list object named clipList that contains a list of clips
Defining the implementation of the clipList object
Creating a video object and VideoTable table
Inserting a video clip into the ClipsTable table
Inserting a row into the VideoTable table
Loading a video into the ClipsTable table
Inserting a reference to a clipObject into the clips list in the video table
Inserting a reference to a video object into the clip
Retrieving a video clip from the VideoTable table
Extending interMedia to support a new video data format
Extending interMedia with new object types
Using video types with object views
Using a set of scripts for creating and populating a video table from a BFILE data source

The video examples in this section use a table of video clips and a table of videos. For each video clip the following are stored: a videoRef (REF into the video table), clip ID, title, director, category, copyright, producer, awards, time period, rating, duration, cdRef (REF into CdObject for sound tracks), text content (indexed by CONTEXT), cover image (REF into the image table), and video source. For each video the following are stored: an item ID, duration, text content (indexed by CONTEXT), cover image (REF into the image table), and a list of clips on the video.

Reference information on the methods used in these examples is presented in Chapter 9.

3.4.1 Defining a Clip Object

Example 3-49 describes how to define a clip object.

Example 3-49 Define a Clip Object

CREATE TYPE clipObject as OBJECT (
  videoRef      REF VideoObject,       -- REF into the video table
  clipId        VARCHAR2(20),          -- Id inside of the clip table
  title         VARCHAR2(4000),
  director      VARCHAR2(4000),
  category      VARCHAR2(20),
  copyright     VARCHAR2(4000),
  producer      VARCHAR2(4000),
  awards        VARCHAR2(4000),
  timePeriod    VARCHAR2(20),
  rating        VARCHAR2(256),
  duration      INTEGER,         
  cdRef         REF CdObject,          -- REF into a CdObject(soundtrack)
  txtcontent    CLOB,           
  coverImg      REF ORDSYS.ORDImage,   -- REF into the ImageTable
  videoSource   ORDSYS.ORDVideo);

3.4.2 Creating an Object Table ClipsTable

Example 3-50 describes how to create an object table named ClipsTable.

Example 3-50 Create a Table Named ClipsTable

CREATE TABLE ClipsTable of clipObject (UNIQUE (clipId), clipId NOT NULL);

3.4.3 Creating a List Object Containing a List of Clips

Example 3-51 describes how to create a list object containing a list of clips.

Example 3-51 Create a List Object Containing a List of Clips

CREATE TYPE clipNstType AS TABLE of REF clipObject;

CREATE TYPE clipList AS OBJECT (clips clipNstType,
        MEMBER PROCEDURE addClip(c IN REF clipObject));

3.4.4 Defining the Implementation of the clipList Object

Example 3-52 describes how to define the implementation of the clipList object.

Example 3-52 Define the Implementation of the clipList Object

CREATE TYPE BODY clipList AS
  MEMBER PROCEDURE addClip(c IN REF clipObject) 
  IS
    pos INTEGER := 0;
  BEGIN
    IF clips IS NULL THEN
      clips := clipNstType(NULL);
      pos := 0;
    ELSE
      pos := clips.count;
    END IF;
    clips.EXTEND;
    clips(pos+1) := c;
  END;
END;

3.4.5 Creating a Video Object and a Video Table

This section describes how to create a video object and a video table of video clips that includes, for each video clip, the following information:

Item ID
Duration
Text content
Cover image
Clips

Example 3-53 creates a video object named videoObject and a video table named VideoTable that contains the video information.

Example 3-53 Create a Video Table Containing Video Information

CREATE TYPE VideoObject as OBJECT (
        itemId        INTEGER,  
        duration      INTEGER,  
        txtcontent    CLOB,     
        coverImg      REF ORDSYS.ORDImage,
        clips         clipList);

CREATE TABLE VideoTable OF VideoObject (UNIQUE(itemId),itemId NOT NULL)
        NESTED TABLE clips.clips STORE AS clip_store_table;

3.4.6 Inserting a Video Clip into the ClipsTable Table

Example 3-54 describes how to insert a video clip into the ClipsTable table.

Example 3-54 Insert a Video Clip into the ClipsTable Table

-- Insert a Video Clip into the ClipsTable
insert into ClipsTable values (NULL,
                        '11',
                        'Oracle Commercial',
                        'Larry Ellison',
                        'commercial',
                        'Oracle Corporation',
                        '',
                        'no awards',
                        '90s'
                        'no rating',
                        30,
                        NULL,
                        EMPTY_CLOB(),
                        NULL,
                        ORDSYS.ORDVIDEO.init());

3.4.7 Inserting a Row into the VideoTable Table

Example 3-55 describes how to insert a row into the VideoTable table.

Example 3-55 Insert a Row into the VideoTable Table

-- Insert a row into the VideoTable
insert into VideoTable values (11,
                               30,
                               NULL,
                               NULL,
                               clipList(NULL));

3.4.8 Loading a Video into the ClipsTable Table

Example 3-56 describes how to load a video into the ClipsTable table. This example requires a VIDDIR directory to be defined; see the comments in the example.

Example 3-56 Load a Video into the ClipsTable Table

-- Load a Video into a clip
-- Create your directory specification below 
-- CREATE OR REPLACE DIRECTORY VIDDIR AS '/video/';
DECLARE
     videoObj ORDSYS.ORDVIDEO;
     ctx RAW(4000) := NULL;
BEGIN
     SELECT C.videoSource INTO videoObj 
     FROM   ClipsTable C
     WHERE  C.clipId = '11'
     FOR UPDATE;

     videoObj.setDescription('Under Pressure Video Clip');
     videoObj.setSource('file', 'VIDDIR', 'UnderPressure.mov');
     videoObj.import(ctx);
     videoObj.setProperties(ctx,TRUE)

     UPDATE ClipsTable C
        SET    C.videoSource = videoObj 
     WHERE  C.clipId = '11';
     COMMIT;
END;

-- Check video insertion
DECLARE
     videoObj ORDSYS.ORDVideo;
     ctx RAW(4000) := NULL;
BEGIN
     SELECT C.videoSource INTO videoObj
     FROM   ClipsTable C
     WHERE  C.clipId = '11';

     dbms_output.put_line('Content Length: ' ||
                     videoObj.getContentLength(ctx));
     dbms_output.put_line('Content MimeType: ' ||
                     videoObj.getMimeType());
END;

3.4.9 Inserting a Reference to a Clip Object into the Clips List in the VideoTable Table

Example 3-57 describes how to insert a reference to a clip object into the clips list in the VideoTable table.

Example 3-57 Insert a Reference to a Clip Object into the Clips List in the VideoTable Table

-- Insert a reference to a ClipObject into the Clips List in the VideoTable
DECLARE
     clipRef          REF ClipObject;
     clipListInstance clipList;
BEGIN
     SELECT REF(C) into clipRef 
        FROM   ClipsTable C
     where  C.clipId = '11';

     SELECT V.clips INTO clipListInstance
        FROM   VideoTable V
     WHERE  V.itemId = 11
     FOR UPDATE;

     clipListInstance.addClip(clipRef);

     UPDATE VideoTable V
     SET    V.clips = clipListInstance
     WHERE  V.itemId = 11;
 
     COMMIT;
END;

-- Check insertion of clip ref
DECLARE
     clip             ClipObject;
     clipRef          REF ClipObject;
     clipListInstance clipList;
     clipType         clipNstType;
BEGIN
     SELECT V.clips INTO clipListInstance
     FROM   VideoTable V
     WHERE  V.itemId = 11;

     SELECT clipListInstance.clips INTO clipType FROM DUAL;
     clipRef := clipType(1);
     SELECT DEREF(clipRef) INTO clip FROM DUAL;

     dbms_output.put_line('Clip Title: ' ||
                           clip.title);
END;

3.4.10 Inserting a Reference to a Video Object into the Clip

Example 3-58 describes how to insert a reference to a video object into the clip.

Example 3-58 Insert a Reference to a Video Object into the Clip

-- Insert a reference to a video object into the clip
DECLARE
     aVideoRef REF VideoObject;
BEGIN
-- Make a VideoRef an obj to use for update
     SELECT Cp.videoRef INTO aVideoRef
     FROM   ClipsTable Cp
     WHERE  Cp.clipId = '11'
     FOR UPDATE;

-- Change its value 
     SELECT REF(V) INTO aVideoRef
     FROM   VideoTable V
     WHERE  V.itemId = 11;

-- Update database 
     UPDATE ClipsTable C
     SET    C.videoRef = aVideoRef
     WHERE  C.clipId = '11';

     COMMIT;
END;

3.4.11 Retrieving a Video Clip from the VideoTable Table

Example 3-59 describes how to retrieve a video clip from the VideoTable table and return it as a BLOB. The program segment performs these operations:

Defines the retrieveVideo( ) method to retrieve the video clip by its clipId as an ORDVideo BLOB.
Selects the desired video clip (where C.clipId = clipId) and returns it using the getContent method.

Example 3-59 Retrieve a Video Clip

FUNCTION retrieveVideo(clipId IN INTEGER) 
RETURN BLOB IS 
 obj ORDSYS.ORDVideo;

BEGIN
   -- Select the desired video clip from the ClipTable table.
   SELECT C.videoSource INTO obj from ClipTable C 
          WHERE C.clipId = clipId;
   return obj.getContent;
END;

3.4.12 Extending interMedia to Support a New Video Data Format

This section describes how to extend Oracle interMedia to support a new video data format.

To support a new video data format, implement the required interfaces in the ORDX_<format>_VIDEO package in the ORDPLUGINS schema (where <format> represents the name of the new video data format). See Section 9.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_VIDEO package. Use the package body example in Section 9.4.2 as a template to create the video package body.

Then set the new format parameter in the setFormat call to the appropriate format value to indicate to the video object that package ORDPLUGINS.ORDX_<format> _VIDEO is available as a plug-in.

See Section F.4 for more information on installing your own format plug-in and running the sample scripts provided. See the fplugins.sql and fpluginb.sql files that are installed in the$ORACLE_HOME/ord/vid/demo/ directory. These are demonstration (demo) plug-ins that you can use as a guideline to write any format plug-in that you want to support. See the viddemo.sql file in this same directory to learn how to install your own format plug-in.

3.4.13 Extending interMedia with a New Object Type

This section describes how to extend Oracle interMedia with a new object type.

You can use the ORDVideo type as the basis for a new type of your own creation.

See Example 3-45 for a more complete example and description.

Note:

When a type is altered any dependent type definitions are invalidated. You will encounter this problem if you define a new type that includes an ORDVideo attribute and the interMedia ORDVideo type is altered, which always occurs during an interMedia installation upgrade.

A workaround to this problem is to revalidate all invalid type definitions with the following SQL statement:

SQL> ALTER TYPE <type-name> COMPILE;

3.4.14 Using Video Types with Object Views

This section describes how to use video types with object views. Just as a view is a virtual table, an object view is a virtual object table.

In Example 3-60, consider the following relational table (containing no ORDVideo objects).

Example 3-60 Define a Relational Table Containing No ORDVideo Object

create table flat (
   id                number,
   description       VARCHAR2(4000),
   localData         BLOB,
   srcType           VARCHAR2(4000),
   srcLocation       VARCHAR2(4000),
   srcName           VARCHAR2(4000),
   upDateTime        DATE,
   local             NUMBER,
   format            VARCHAR2(31),
   mimeType          VARCHAR2(4000),
   comments          CLOB,
   width             INTEGER,
   height            INTEGER,
   frameResolution   INTEGER,
   frameRate         INTEGER,
   videoDuration     INTEGER,
   numberOfFrames    INTEGER,
   compressionType   VARCHAR2(4000),
   numberOfColors    INTEGER,
   bitRate           INTEGER,
   );

You can create an object view on the relational table shown in Example 3-60 as follows in Example 3-61.

Example 3-61 Define an Object View Containing an ORDVideo Object and Relational Columns

create or replace view object_video_v as 
  select
      id,
      ORDSYS.ORDVideo(
      ORDSYS.ORDSource(
         T.localData, T.srcType, T.srcLocation, T.srcName, T.updateTime,
         T.local),
         T.description,
         T.format, 
         T.mimeType,
         T.comments,
         T.width,
         T.height,
         T.frameResolution,
         T.frameRate,
         T.videoDuration,
         T.numberofFrames,
         T.compressionType,
         T.numberOfColors,
         T.bitRate) VIDEO
   from flat T;

3.4.15 Scripts for Creating and Populating a Video Table from a BFILE Data Source

The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://otn.oracle.com/ as end-to-end scripts that create and populate a video table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.

The following set of scripts:

Creates a tablespace for the video data, creates a user and grants certain privileges to this new user, creates a video data load directory (create_viduser.sql).
Creates a table with two columns, inserts two rows into the table and initializes the object column to empty with a locator (create_vidtable.sql).
Loads the video data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importvid.sql).
Performs a check of the properties for the loaded data to ensure that it is really there (chkprop.sql).

The fifth script (setup_vidschema.sql) automates this entire process by running each script in the required order. The last script (readvideo.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of video data from the BLOB, beginning at a particular offset, until all the video data is read. To successfully load the video data, you must have a viddir directory created on your system containing the vid1.mov and vid2.mov files, which are installed in the <ORACLE_HOME>/ord/vid/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_viduser.sql file.

Script 1: Create a Tablespace, Create a Video User, Grant Privileges to the Video User, and Create a Video Data Load Directory (create_viduser.sql)

This script creates the viddemo tablespace with a data file named viddemo.dbf of 200MB in size, with an initial extent of 64K, a next extent of 128K, and turns on table logging. Next, the viddemo user is created and given connect, resource, create library, and create directory privileges followed by creating the video data load directory.

Note:

You must edit the create_viduser.sql file and either enter the system password in the connect statement or comment out the connect statement and run this file in the system account. You must specify the disk drive in the CREATE DIRECTORY statement. Also, create the temp temporary tablespace if you have not already created it, otherwise this file will not run.

-- create_viduser.sql

-- Connect as admin.
connect system/<system password>;

-- Edit this script and either enter your system password here

-- to replace <system password> or comment out this connect

-- statement and connect as system before running this script. 

set serveroutput on
set echo on

-- Need system manager privileges to delete a user.
-- Note: There is no need to delete viddemo user if you do not 
-- delete the viddemo tablespace, therefore comment out the next line.

-- drop user viddemo cascade;

-- Need system manager privileges to delete a directory. If there is no 
-- need to really delete it, then comment out the next line.

-- drop directory viddir;

-- Delete then create tablespace.

-- Note: It is better to not delete and create tablespaces, 
-- so comment this next line out. The create tablespace statement
-- will fail if it already exists.

-- drop tablespace viddemo including contents;

-- If you uncomment the previous line and want to delete the 
-- viddemo tablespace, remember to manually delete the viddemo.dbf
-- file to complete the operation. Otherwise, you cannot create
-- the viddemo tablespace again because the viddemo.dbf file 
-- already exists. Therefore, it might be best to create this 
-- tablespace once and not delete it.

-- Create tablespace.
create tablespace viddemo
       datafile 'viddemo.dbf' size 200M
       minimum extent 64K
       default storage (initial 64K next 128K)
       logging;

-- Create viddemo user.
create user viddemo identified by viddemo
default tablespace viddemo
temporary tablespace temp;

-- Note: If you do not have a temp tablespace already defined, you
-- will have to create it first for this script to work. 

grant connect, resource, create library to viddemo;
grant create any directory to viddemo;

-- Note: If this user already exists, you get an error message 
-- when you try and create this user again.

-- Connect as viddemo.
connect viddemo/viddemo

-- Create the viddir load directory; this is the directory where the video 
-- files are residing.

create or replace directory viddir
       as 'e:\oracle\ord\vid\demo';
grant read on directory viddir to public with grant option;

-- Note: If this directory already exists, an error message 
-- is returned stating the operation will fail; ignore the message.

Script 2: Create the Video Table and Initialize the Column Object (create_vidtable.sql)

This script creates the video table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.

--create_vidtable.sql
connect viddemo/viddemo;
set serveroutput on
set echo on

drop table vidtable;
create table vidtable (id number,
       Video ordsys.ordVideo);

-- Insert a row with empty BLOB.
insert into vidtable values(1,ORDSYS.ORDVideo.init());

-- Insert a row with empty BLOB.
insert into vidtable values(2,ORDSYS.ORDVideo.init());
commit;

Script 3: Load the Video Data (importvid.sql)

This script performs a SELECT FOR UPDATE operation to load the video data by first setting the source for loading the video data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two video clips to your VIDDIR directory using the names specified in this script, or modify this script to match the file names of your video clips.

-- importvid.sql

set serveroutput on
set echo on
-- Import the two files into the database.

DECLARE 
  obj ORDSYS.ORDVIDEO;
  ctx RAW(4000) := NULL;

BEGIN
-- This imports the video file vid1.mov from the VIDDIR directory
-- on a local file system (srcType=file) and sets the properties.

  select Video into obj from vidtable where id = 1 for update;
  obj.setSource('file','VIDDIR','vid1.mov');
  obj.import(ctx);
  obj.setProperties(ctx);
 
  update vidtable set video = obj where id = 1;
  commit;

-- This imports the video file vid2.mov from the VIDDIR directory 
-- on a local file system (srcType=file) and sets the properties.

  select Video into obj from vidtable where id = 2 for update;
  obj.setSource('file','VIDDIR','vid2.mov');
  obj.import(ctx);
  obj.setProperties(ctx);
 
  update vidtable set video = obj where id = 2;
  commit;
END;
/

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

This script performs a SELECT operation of the rows of the video table, then gets the video characteristics of the BLOB data to check that the BLOB data is in fact loaded.

--chkprop.sql
set serveroutput on;
--connect viddemo/viddemo
--Query vidtable for ORDSYS.ORDVideo. 
DECLARE
  video ORDSYS.ORDVideo;
  idnum integer;
  properties_match BOOLEAN;
  ctx RAW(4000) := NULL;
  width integer;
  height integer;

BEGIN
 FOR I IN 1..2 LOOP
  SELECT id, video into idnum, video from vidtable where id=I;
   dbms_output.put_line('video id:          '|| idnum);

  properties_match := video.checkProperties(ctx);
  IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded');
  END IF;

 --dbms_output.put_line('video frame rate:     '|| video.getFrameRate(ctx));
 --dbms_output.put_line('video width & height:   '|| video.getFrameSize(ctx,width,height);
 dbms_output.put_line('video MIME type:       '|| video.getMimeType());
 dbms_output.put_line('video file format:     '|| video.getFormat(ctx));
 dbms_output.put_line('BLOB Length:            '|| TO_CHAR(video.getContentLength(ctx)));
 dbms_output.put_line('----------------------------------------------');

 END loop;
END;
/

Results from running the script chkprop.sql are the following:

SQL> @chkprop.sql
video id:          1
Check Properties Succeeded
video MIME type:        video/quicktime
video file format:      MOOV
BLOB Length:            4958415
----------------------------------------------
video id:          2
Check Properties Succeeded
video MIME type:        video/quicktime
video file format:      MOOV
BLOB Length:            2891247
----------------------------------------------

Automated Script (setup_vidschema.sql)

This script runs each of the previous four scripts in the correct order to automate this entire process.

-- setup_vidschema.sql
-- Create viddemo user, tablespace, and load directory to 
-- hold the video files:
@create_viduser.sql

-- Create Video table:
@create_vidtable.sql

--Import 2 video clips and set properties:
@importvid.sql

--Check the properties of the video clips:
@chkprop.sql

--exit;

Read Data from the BLOB (readvideo.sql)

This script creates a stored procedure that performs a SELECT operation to read a specified amount of video data from the BLOB, beginning at a particular offset, until all the video data is read.

-- readvideo.sql

set serveroutput on
set echo on

create or replace procedure readvideo as

   obj ORDSYS.ORDVideo;
   buffer RAW (32767);
   numbytes BINARY_INTEGER := 32767;
   startpos integer := 1;
   read_cnt integer := 1;
   ctx RAW(4000) := NULL;

BEGIN

   Select video into obj from vidtable where id = 1;

   LOOP
           obj.readFromSource(ctx,startpos,numbytes,buffer);

            DBMS_OUTPUT.PUT_LINE('Content length is: '|| TO_CHAR(obj.getContentLength()));

            DBMS_OUTPUT.PUT_LINE('start position: '|| startpos);
            DBMS_OUTPUT.PUT_LINE('doing read  '|| read_cnt);
           startpos := startpos + numbytes;
           read_cnt := read_cnt + 1;

-- Note: Add your own code here to process the video data being read;
-- this routine just reads the data into the buffer 32767 bytes
-- at a time, then reads the next chunk, overwriting the first
-- buffer full of data.
   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('End of data ');
   DBMS_OUTPUT.PUT_LINE('----------------');

   WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
    DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION caught');

END;
/
show errors

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on;
SQL> execute readvideo
Content Length: 4958415
start position: 1
doing read  1
start position: 32768
doing read  2
start position: 65535
.
.
.
doing read  151
start position: 4947818
doing read  152
----------------
End of data

PL/SQL procedure successfully completed.

3.5 Extending interMedia to Support a New Data Source

This section describes how to extend Oracle interMedia to support a new data source.

To support a new data source, implement the required interfaces in the ORDX_<srcType>_SOURCE package in the ORDPLUGINS schema (where <srcType> represents the name of the new external source type). See Section I.3.1 and Section I.3.2 for a complete description of the interfaces for the ORDX_FILE_SOURCE and ORDX_HTTP_SOURCE packages. See Section I.3.4 for an example of modifying the package body listing that is provided. Then set the source type parameter in the setSourceInformation call to the appropriate source type to indicate to the video object that package ORDPLUGINS.ORDX_<srcType>_SOURCE is available as a plug-in.

3 interMedia Examples

3.1 Audio Data Examples

3.1.1 Defining a Song Object

Example 3-1 Define a Song Object

3.1.2 Creating an Object Table SongsTable

Example 3-2 Create a Table Named SongsTable

3.1.3 Creating a List Object Containing a List of References to Songs

Example 3-3 Create a List Object Containing a List of References to Songs

3.1.4 Defining the Implementation of the songList Object

Example 3-4 Define the Implementation of the songList Object

3.1.5 Creating a CD Object and a CD Table

Example 3-5 Create a CD Table Containing CD Information

3.1.6 Inserting a Song into the SongsTable Table

Example 3-6 Insert a Song into the SongsTable Table

3.1.7 Inserting a CD into the CdTable Table

Example 3-7 Insert a CD into the CdTable Table

3.1.8 Loading a Song into the SongsTable Table

Example 3-8 Load a Song into the SongsTable Table

3.1.9 Inserting a Reference to a Song Object into the Songs List in the CdTable Table

Example 3-9 Insert a Reference to a Song Object into the Songs List in the CdTable Table

3.1.10 Adding a CD Reference to a Song

Example 3-10 Add a CD Reference to a Song

3.1.11 Retrieving Audio Data from a Song in a CD

Example 3-11 Retrieve Audio Data from a Song in a CD

3.1.12 Extending interMedia to Support a New Audio Data Format

3.1.13 Extending interMedia with a New Type

3.1.14 Using Audio Types with Object Views

Example 3-12 Define a Relational Table Containing No ORDAudio Object

Example 3-13 Define an Object View Containing an ORDAudio Object and Relational Columns

3.1.15 Scripts for Creating and Populating an Audio Table from a BFILE Data Source

Script 1: Create a Tablespace, Create an Audio User, Grant Privileges to the Audio User, and Create an Audio Data Load Directory (create_auduser.sql)

Script 2: Create the Audio Table and Initialize the Column Object (create_audtable.sql)

Script 3: Load the Audio Data (importaud.sql)

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

Automated Script (setup_audschema.sql)

Read Data from the BLOB (readaudio.sql)

3.2 Media Data Examples

3.2.1 Defining a Media Object

Example 3-14 Define a Media Object

3.2.2 Creating an Object Table DocumentsTable

Example 3-15 Create a Table Named DocumentsTable

3.2.3 Creating a List Object Containing a List of References to Media

Example 3-16 Create a List Object Containing a List of References to Media

3.2.4 Defining the Implementation of the documentList Object

Example 3-17 Define the Implementation of the documentList Object

3.2.5 Creating a Library Object and a Library Table

Example 3-18 Create a Library Table Containing Library Information

3.2.6 Inserting Media into the DocumentsTable Table

Example 3-19 Insert Media into the DocumentsTable Table

3.2.7 Inserting a Library into the LibraryTable Table

Example 3-20 Insert a Library into the LibraryTable Table

3.2.8 Loading Media into the DocumentsTable Table

Example 3-21 Load Media into the DocumentsTable Table

3.2.9 Inserting a Reference to a Document Object into the Documents List in the LibraryTable Table

Example 3-22 Insert a Reference to a Document Object into the Documents List in the LibraryTable Table

3.2.10 Adding a Library Reference to a Document

Example 3-23 Add a Library Reference to a Document

3.2.11 Extending interMedia to Support a New Media Data Format

3.2.12 Extending interMedia with a New Type

3.2.13 Using Document Types with Object Views

3.2.14 Using the ORDDoc Object Type as a Repository

Example 3-24 Build a Repository of Media

3.2.15 Scripts for Creating and Populating a Media Table from a BFILE Data Source

Script 1: Create a Tablespace, Create a Media User, Grant Privileges to the Media User, and Create a Media Data Load Directory (create_docuser.sql)

Script 2: Create the Media Table and Initialize the Column Object (create_doctable.sql)

Script 3: Load the Media Data (importdoc.sql)

Script 4: Check the Properties of the Loaded Data (chkprop.sql)

Automated Script (setup_docschema.sql)

Read Data from the BLOB (readdoc.sql)

3.3 Image Data Examples

3.3.1 Adding Image Types to an Existing Table

Example 3-25 Add New Columns of Type ORDImage and ORDImageSignature to the stockphotos Table

3.3.2 Adding Image Types to a New Table

Example 3-26 Create the stockphotos Table and Add ORDImage and ORDImageSignature Types

3.3.3 Inserting a Row Using BLOB Images

Example 3-27 Insert a Row into a Table with Empty Data in the ORDImage Type Column

3.3.4 Populating a Row Using BLOB Images

Example 3-28 Populate a Row with ORDImage BLOB Data

3.3.5 Inserting a Row Using BFILE Images

Example 3-29 Insert a Row into a Table Pointing to an External Image Data File

3
interMedia Examples