Oracle interMedia Audio, Image, and Video User's Guide and Reference
Release 8.1.7

Part Number A85336-01

Library

Product

Contents

Index

Go to previous page Go to next page

2
interMedia Examples

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

2.1 Audio Data Examples

interMedia audio examples include the following common operations:

The audio data examples in this section use a table of songs and a table of CDs. For each song, the following information is stored: a CDRef (REF into the CD table), a song ID, song title, artist, awards, time period of song, duration of song, clipRef (REF into the audio clips table or music video), text content, and the audio source containing lyrics. (A REF refers to row objects with globally unique object IDs that capture references between row objects; row objects are automatically indexed for fast access.) For each CD, the following are stored: an item ID, CD DB ID, CD title, CD artist, CD category, copyright, name of producer, awards, time period, rating, duration, text content, cover image, and the list of songs on the CD.

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

2.1.1 Defining a Song Object

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

Example 2-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 
);

2.1.2 Creating an Object Table SongsTable

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

Example 2-2 Create a Table Named SongsTable

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

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

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

Example 2-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));

2.1.4 Defining the Implementation of the songList Object

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

Example 2-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;

2.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:

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

Example 2-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;

2.1.6 Inserting a Song into the SongsTable Table

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

Example 2-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';

2.1.7 Inserting a CD into the CdTable Table

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

Example 2-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;

2.1.8 Loading a Song into the SongsTable Table

Example 2-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 2-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/';
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.setMimeType('audio/basic');
        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;

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

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

Example 2-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;

2.1.10 Adding a CD Reference to a Song

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

Example 2-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;

2.1.11 Retrieving Audio Data from a Song in a CD

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

Example 2-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; 

2.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 4.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_AUDIO package. Use the package body example in Section 4.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.

2.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 2-3 and Example 2-4 for brief examples. See Example 2-26 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;

Now you can alter the dependent type definition as follows:

SQL> ALTER TYPE <type-name> REPLACE AS OBJECT
(...);
/
 

2.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.

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.

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

Example 2-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,
   audioclip         RAW(2000)
);

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

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

create or replace view object_audio_v as 
  select
      id,
      ordsys.ORDAudio( 
         T.description,
         T.localData, 
         T.comments, 
         T.format, 
         T.encoding, 
         T.numberOfChannels, 
         T.samplingRate, 
         T.sampleSize, 
         T.compressionType, 
         T.audioDuration,
         T.audioclip) AUDIO 
   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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.

2.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://technet.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:

  1. 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).

  2. 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).

  3. Loads the audio data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importaud.sql).

  4. 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 auddemo 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.

2.2 Image Data Examples

interMedia image examples include the following common operations:

2.2.1 Adding Image Types to an Existing Table

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

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

To add a new column to the 'emp' table called 'photo' using the ORDImage type, issue the statement in Example 2-14.

Example 2-14 adds a new column of type ORDImage to the emp table.

Example 2-14 Add a New Column of Type ORDImage to the emp Table

ALTER TABLE emp 
ADD (photo ORDSYS.ORDImage); 

2.2.2 Adding Image Types to a New Table

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

The column for the badge photograph (maybe a thumbnail image cropped and scaled from the large personnel photograph) uses the ORDImage type, and the column 'large_photo' also uses the ORDImage type. The statement in Example 2-15 creates the table and adds ORDImage types to the new table.

Example 2-15 Add ORDImage Types to a New Table

CREATE TABLE emp (
  ename VARCHAR2(50), 
  salary NUMBER, 
  job VARCHAR2(50), 
  department INTEGER, 
  photo ORDSYS.ORDImage, 
  large_photo ORDSYS.ORDImage); 

2.2.3 Inserting a Row Using BLOB Images

To insert a row into a table that has storage for image content 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 2-16 describes how to insert rows into the table using the ORDImage type. Assume you have a table 'emp' with the following columns:

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

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' column, issue the statement in Example 2-16.

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

Example 2-16 Insert a Row into a Table with Empty Data in the ORDImage Type Column

INSERT INTO emp VALUES (
  'John Doe', 24000, 'Technical Writer', 123,
  ORDSYS.ORDImage.init());

2.2.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 2-17 populates a row with ORDImage BLOB data. See Section 2.1.15 for another set of examples for populating rows using BLOB images.

Example 2-17 Populate a Row with ORDImage BLOB Data

DECLARE
    -- applicaition variables
    Image ORDSYS.ORDImage;
    ctx RAW(4000) := NULL;
BEGIN
    INSERT INTO emp VALUES (
      'John Doe', 24000, 'Technical Writer', 123,
      ORDSYS.ORDImage.init());
    -- Select the newly inserted row for update
      SELECT photo INTO Image FROM emp
           WHERE ename = 'John Doe' 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 IMGDIR
    -- directory on a local file system
    -- (srcType=FILE) and automatically sets the properties.

    Image.setSource('FILE','IMGDIR','test.gif');
    Image.import(ctx);

  UPDATE emp SET photo = Image WHERE ename = 'John Doe';
  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 Oracle8i Application Developer's Guide - Large Objects (LOBs) for more information on BLOBs.

2.2.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 2-18 describes how to insert rows into the table using the ORDImage type. Assume you have a table 'emp' with the following columns:

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

If you are going to use the ORDImage type column, you must first populate the column 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 2-18 inserts a row into the table with an image called 'jdoe.gif' from the ORDIMGDIR directory.

Example 2-18 Insert a Row into a Table with an Image in the ORDImage Type Column

INSERT INTO emp VALUES (
  'John Doe', 24000, 'Technical Writer', 123,
  ORDSYS.ORDImage.init('file','ORDIMGDIR','jdoe.gif'));


Note:

In releases 8.1.5, 8.1.6, and 8.1.7 of Oracle8i, the content stored in ORDImage in files or URLs is read-only. 


For a description of row insertion into an object type, see Chapter 5, and the Oracle8i 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.

2.2.6 Populating a Row Using BFILE Images

Example 2-19 populates the row with ORDImage data stored externally in files.

Example 2-19 Populate a Row with ORDImage External File Data

DECLARE
    Image ORDSYS.ORDImage;
BEGIN
    INSERT INTO emp VALUES ('John Doe', 24000, 'Technical Writer', 123,
    ORDSYS.ORDImage.init('file','ORDIMGDIR','jdoe.gif'));
    -- Select the newly inserted row for update
    SELECT large_photo INTO Image FROM emp
           WHERE ename = 'John Doe' FOR UPDATE;
    -- Set property attributes for the image data
    Image.setProperties;
    UPDATE emp SET large_photo = Image WHERE ename = 'John Doe';
    COMMIT;
    -- Continue processing
END;

2.2.7 Querying a Row

Example 2-20 and Example 2-21 assume you have this table:

create table emp (
ename VARCHAR2(50), 
salary NUMBER, 
job VARCHAR2(50), 
department INTEGER, 
photo ORDSYS.ORDImage, 
large_photo ORDSYS.ORDImage); 

Example 2-20 queries the emp (employees) table for the name John Doe 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 2-20 Query Rows of ORDImage Data for Widths Greater Than 32

SELECT ename, E.large_photo.getWidth()
  FROM emp E
  WHERE ename = 'John Doe' and
        E.large_photo.getWidth() > 32;

Example 2-21 queries the emp (employees) table for the name John Doe and the ORDImage data for rows with minimum photo widths (greater than 32 pixels) and a minimum content length (greater than 10000 bytes).

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

SELECT ename, E.large_photo.getCompressionFormat()
  FROM emp E
  WHERE ename = 'John Doe' and
        E.large_photo.getWidth() > 32 and
        E.large_photo.getContentLength() > 10000;

2.2.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 2-22 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 2-22 Import an Image from an External File

DECLARE
    Image ORDSYS.ORDImage;
    ctx RAW(4000) := NULL;
BEGIN
    SELECT large_photo
      INTO Image FROM emp
      WHERE ename = 'John Doe' FOR UPDATE;
    -- Import the image into the database
    Image.import(ctx);
    UPDATE emp SET large_photo = IMAGE
           WHERE ename = 'John Doe';
    COMMIT;
END;

2.2.9 Copying an Image

To copy an image, use the ORDImage.copy method. Example 2-23 copies image data.

Example 2-23 Copy an Image

DECLARE
    Image_1 ORDSYS.ORDImage;
    Image_2 ORDSYS.ORDImage;
BEGIN
    SELECT photo INTO Image_1
       FROM emp WHERE ename = 'John Doe';
    SELECT photo INTO Image_2
       FROM emp WHERE ename = 'Also John Doe' FOR UPDATE;
    -- Copy the data from Image_1 to Image_2
    Image_1.copy(Image_2);
    -- Continue processing
    UPDATE emp SET photo = Image_2
        WHERE ename = 'Also John Doe';
    COMMIT;
END;

2.2.10 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 2-24 converts the image data to the TIFF image file format.

Example 2-24 Convert an Image Format

DECLARE
    Image ORDSYS.ORDImage;
BEGIN
    SELECT photo INTO Image FROM emp
        WHERE ename = 'John Doe' FOR UPDATE;
    -- Convert the image to TIFF (in place)
    Image.process('fileFormat=TIFF');
    UPDATE emp SET photo = Image WHERE ename = 'John Doe';
    COMMIT;
END;

2.2.11 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 2-25 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 2-25 Copy and Convert an Image Format

DECLARE
    Image_1 ORDSYS.ORDImage;
    Image_2 ORDSYS.ORDImage;
BEGIN
    SELECT photo, large_photo
        INTO Image_2, Image_1
        FROM emp
        WHERE ename = 'John Doe' 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 emp SET photo = Image_2 WHERE ename = 'John Doe';
    COMMIT;
END;

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

2.2.12 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 2-26.


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;

Now you can alter the dependent type definition as follows:

SQL> ALTER TYPE <type-name> REPLACE AS OBJECT
(...);
/
 

Example 2-26 Extend Oracle interMedia Image 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 TEST_DIR as 'C:\TESTS';

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

2.2.13 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.

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.

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

Example 2-27 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 2-27 as follows in Example 2-28.

Example 2-28 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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.

2.2.14 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://technet.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:

  1. 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).

  2. 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).

  3. Loads the image data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importimg.sql).

  4. 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 imgdemo 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.

2.2.15 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://technet.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 2.2.14.  


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; 
/ 

2.2.16 Addressing National Language Support (NLS) Issues

Example 2-29 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 National Language Support issues.

Example 2-29 Address a National Language 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;
/

2.3 Video Data Examples

interMedia video examples include the following common operations:

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 6.

2.3.1 Defining a Clip Object

Example 2-30 describes how to define a clip object.

Example 2-30 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);

2.3.2 Creating an Object Table ClipsTable

Example 2-31 describes how to create an object table named ClipsTable.

Example 2-31 Create a Table Named ClipsTable

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

2.3.3 Creating a List Object Containing a List of Clips

Example 2-32 describes how to create a list object containing a list of clips.

Example 2-32 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));

2.3.4 Defining the Implementation of the clipList Object

Example 2-33 describes how to define the implementation of the clipList object.

Example 2-33 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;

2.3.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:

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

Example 2-34 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;

2.3.6 Inserting a Video Clip into the ClipsTable Table

Example 2-35 describes how to insert a video clip into the ClipsTable table.

Example 2-35 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('Oracle Commercial 1 Video Clip'),
                       'QuickTime File Format',
                       'video/quicktime',
                        160, 120, 72, 15, 30, 450, 'Cinepak', 256, 15000));

2.3.7 Inserting a Row into the VideoTable Table

Example 2-36 describes how to insert a row into the VideoTable table.

Example 2-36 Insert a Row into the VideoTable Table

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

2.3.8 Loading a Video into the ClipsTable Table

Example 2-37 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 2-37 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)

     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;

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

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

Example 2-38 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;

2.3.10 Inserting a Reference to a Video Object into the Clip

Example 2-39 describes how to insert a reference to a video object into the clip.

Example 2-39 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;

2.3.11 Retrieving a Video Clip from the VideoTable Table

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

  1. Defines the retrieveVideo( ) method to retrieve the video clip by its clipId as an ORDVideo BLOB.

  2. Selects the desired video clip (where C.clipId = clipId) and returns it using the getContent method.

Example 2-40 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;

2.3.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 6.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_VIDEO package. Use the package body example in Section 6.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.3 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.

2.3.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 2-32 and Example 2-33 for brief examples. See Example 2-26 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;

Now you can alter the dependent type definition as follows:

SQL> ALTER TYPE <type-name> REPLACE AS OBJECT
(...);
/
 

2.3.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.

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

Object views 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.

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

Example 2-41 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,
   videoclip         RAW(2000)
   );

You can create an object view on the relational table shown in Example 2-41as follows in Example 2-42.

Example 2-42 Define an Object View Containing an ORDVideo Object and Relational Columns

create or replace view object_video_v as 
  select
      id,
      ordsys.ORDVideo(
         T.description,
         T.localData, 
         T.comments, 
         T.format, 
         T.width,
         T.height,
         T.frameResolution,
         T.frameRate,
         T.videoDuration,
         T.numberofFrames,
         T.compressionType,
         T.numberOfColors,
         T.bitRate,
         T.videoclip) VIDEO
   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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.

2.3.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://technet.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:

  1. 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).

  2. 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).

  3. Loads the video data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importvid.sql).

  4. 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 viddemo 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.

2.4 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 7.3.1 and Section 7.3.2 for a complete description of the interfaces for the ORDX_FILE_SOURCE and ORDX_HTTP_SOURCE packages. See Section 7.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.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index