9 interMedia Examples

9.1.1 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 9-1, consider the following relational table (containing no ORDAudio objects).

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,
);

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

9.1.2 Scripts for Populating ORDAudio Objects with BLOB Data

The following scripts can be found on the Oracle Technology Network (OTN) Web site

http://otn.oracle.com/

These scripts are end-to-end scripts that show you how to populate an interMedia ORDAudio object from a BLOB stored in the database. You can get to this site by selecting the Sample Code icon, then under Oracle Database, select Oracle interMedia to go to the Oracle interMedia Sample Code Web page.

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 the soundtable table with two columns (id, sound), inserts a row into the table and initializes the BLOB column with an empty BLOB, and loads the audio data clip with a SELECT FOR UPDATE operation using a DBMS_LOB loadfromfile call to load the data from a BFILE, (create_soundtable.sql).

• Creates the audtable table with two columns (id, audio), inserts three rows and initializes the object column to empty with a locator and initializes the object attributes in the object, loads the audio data with a SELECT FOR UPDATE operation using an import( ) method to import the data from a BFILE for ID=1, and sets the properties of the object (create_audtable.sql).

• Copies the BLOB audio data clip stored in the BLOB column of the soundtable table to the ORDAudio object column of the audtable table using an UPDATE statement for ID=3 and updates the properties of the object and the time stamp with another UPDATE statement.

• Checks the properties of each audio data clip object, one that was imported from a BFILE into the ORDAudio object type for ID=1, and the other that was copied from a BLOB into the ORDAudio object type for ID=3. The attributes for each audio data clip should be identical.

• A sixth script (setup_audschema2.sql) automates this entire process by running each script in the required order.

To successfully load audio data, you must have an auddir directory created on your system. This directory contains your sample audio clip file, chimes.wav. Actually, you can copy any supported audio data clip file to the auddir directory to run this script. Be sure to change the data file names in the script to correspond with the name of the data file you use. This directory path and disk drive must be specified in the CREATE DIRECTORY statement in the creat_auduser.sql file.

This script creates the auddemo tablespace. It contains a data file named auddemo.dbf of 200 MB in size, an initial extent of 64 KB, and a next extent of 128 KB, 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.

-- create_auduser.sql

-- Connect as admin.
CONNECT SYS AS SYSDBA/<SYS password>;

-- Edit this script and either enter your sys password here 
-- to replace <SYS password> or comment out this CONNECT 
-- statement and connect as SYS AS SYSDBA before running this script.

SET SERVEROUTPUT ON;
SET ECHO ON;

-- You need SYSDBA privileges to delete a user.
-- Note: No need to delete auddemo user if you don't delete the 
-- auddemo tablespace, therefore comment out the next line.

-- DROP USER auddemo CASCADE;

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

-- DROP DIRECTORY auddir;

-- Delete, then create, a 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 line above and really want to delete the 
-- auddemo tablespace, remember to manually delete the auddemo.dbf 
-- file to complete the 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 a tablespace.
CREATE TABLESPACE auddemo
       DATAFILE 'auddemo.dbf' SIZE 25M
       MINIMIM EXTENT 64K
       DEFAULT STORAGE (INITIAL 64K NEXT 128K)
       LOGGING;

-- Create the 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 will get an error message 
-- when you try to create this user again.

-- Connect as auddemo.
CONNECT auddemo/auddemo

-- Create the auddemo load directory, the directory where the audio 
-- clips are residing. Replace directory specification with your own.

CREATE OR REPLACE DIRECTORY auddir
       AS 'e:\auddir';
GRANT READ ON DIRECTORY auddir TO PUBLIC WITH GRANT OPTION;

This script creates the soundtable table, performs an insert operation inserting a row with an empty BLOB, loads the row with BLOB data, then checks the length of the BLOB data to ensure that the BLOB data was loaded.

--create_soundtable.sql
--
-- Create the soundtable table.
-- Insert a row into the table with an empty BLOB.
-- Load the row with BLOB data by pointing to the audio
--  file to be loaded from the directory specified
--  using the BFILE data type.
-- Open the file and use the locator to insert the file.
-- Close the files and commit the transaction.
-- Check the length of the BLOB loaded. Is the length
--  what you are expecting?
 
CONNECT auddemo/auddemo;
SET SERVEROUTPUT ON;
SET ECHO ON;

DROP TABLE soundtable;
CREATE TABLE soundtable (id number,
                         sound BLOB 
       default EMPTY_BLOB());

INSERT INTO soundtable(id, sound) VALUES (1, EMPTY_BLOB());
COMMIT;

DECLARE
   f_lob  BFILE := BFILENAME('AUDDIR','chimes.wav');
   b_lob  BLOB;
   Lob    BLOB;
   Length INTEGER;
BEGIN

  SELECT sound INTO b_lob FROM soundtable WHERE id=1 FOR UPDATE;

-- Open the LOBs. 
  dbms_lob.open(f_lob, dbms_lob.file_readonly);
  dbms_lob.open(b_lob, dbms_lob.lob_readwrite);
  dbms_lob.loadfromfile
  (b_lob, f_lob, dbms_lob.getlength(f_lob));
-- Close the LOBs.
  dbms_lob.close(b_lob);
  dbms_lob.close(f_lob);
  COMMIT;

-- Select the LOB:
   SELECT sound INTO Lob FROM soundtable
       WHERE ID = 1;
-- Opening the LOB is optional.
   DBMS_LOB.OPEN (Lob, DBMS_LOB.LOB_READONLY);
-- Get the length of the LOB.
   length := DBMS_LOB.GETLENGTH(Lob);
   IF length IS NULL THEN
       DBMS_OUTPUT.PUT_LINE('LOB is null.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('The length is '|| length);
   END IF;
-- Closing the LOB is mandatory if you have opened it.
   DBMS_LOB.CLOSE (Lob);

END;
/

This script creates the audtable table, and then performs an insert operation to initialize the column object to empty for three 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. Next the 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 for ID=1, importing the data, setting the properties for the BLOB data, updating the row for ID=1, and committing the transaction. To successfully run this script, you must copy one audio clip to your auddir directory using the names specified in this script, or modify this script to match the file names of your audio clips.

-- create_audtable.sql
--
-- Create the audtable table.
-- Insert three rows with empty BLOBs and initialize object attributes.
-- Import a BFILE into the ORDAudio object for ID=1.

CONNECT auddemo/auddemo;
SET SERVEROUTPUT ON;
SET ECHO ON;

  DROP TABLE audtable;
  CREATE TABLE audtable (id    NUMBER,
                       audio ORDSYS.ORDAudio);

-- Insert rows with an empty BLOB and initialize the object attributes.

  INSERT INTO audtable VALUES(1,ORDSYS.ORDAudio.init());
  INSERT INTO audtable VALUES(2,ORDSYS.ORDAudio.init());
  INSERT INTO audtable VALUES(3,ORDSYS.ORDAudio.init());

  COMMIT;

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

BEGIN
-- This performs a SELECT FOR UPDATE from table audtable for ID=1,
-- imports the audio file chimes.wav from the AUDDIR directory 
-- as a BFILE on a local file system (srcType=FILE), sets the properties,
-- updates the row in table audtable for ID=1, then commits the transaction.

  SELECT audio INTO obj FROM audtable WHERE id = 1 FOR UPDATE;
  obj.setSource('FILE','AUDDIR','chimes.wav');
  obj.import(ctx);
  obj.setProperties(ctx);
 
  UPDATE audtable SET audio = obj WHERE id = 1;
  COMMIT;

END;
/

This script copies the BLOB audio data in the sound column of the soundtable table for a row (ID=1) to the ORDAudio object column of the audtable table for a row (ID=3). The script uses a SQL UPDATE statement to set the contents of T.audio.source.localData in the audtable table to be the same as the contents of the sound column of the soundtable table, which performs the copy operation. The script then sets the properties and updates the time stamp for the new BLOB stored in the ORDAudio object.

--copyblob3.sql
--
CONNECT auddemo/auddemo;
SET SERVEROUTPUT ON;
SET ECHO ON;

-- Use the SQL UPDATE statement to set the contents of 
-- T.audio.source.localData to be the same as the BLOB stored 
-- in the sound column of the soundtable table. This is an easy way
-- to copy a BLOB stored in the database into a row containing
-- a column defined as an interMedia ORDAudio object type. 
--
-- In this case, the BLOB (an audio clip), which was stored in
-- a row in the soundtable table containing a sound column 
-- defined as a BLOB data type for an ID=1 is copied to a row
-- in the audtable table containing an audio column defined as
-- an ORDSYS.ORDAudio object type in which the ID=3. The audio
-- clip is referenced through the source attribute of the
-- ORDAudio object type to the underlying localData attribute
-- of the ORDSource object type. 
--

-- Then (1) Call setProperties() and (2) call setUpdateTime()
-- for this new BLOB stored in the ORDAudio object type.

-- Create a procedure to do this.

CREATE OR REPLACE PROCEDURE update_proc IS

   obj ORDSYS.ORDAudio;
   ctx RAW(64) :=NULL;

BEGIN
   UPDATE audtable T SET T.audio.source.localData = (SELECT sound FROM
          soundtable S WHERE S.id = 1) WHERE T.id=3;
   COMMIT;

   SELECT audio INTO obj FROM audtable WHERE id = 3 FOR UPDATE;
   obj.setProperties(ctx);
   obj.setUpdateTime(SYSDATE);
   UPDATE audtable SET audio = obj WHERE id = 3;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
EXECUTE UPDATE_PROC;

This script performs a SELECT operation of two rows (ID=1 and ID=3) of the audtable table and gets the audio characteristics of the BLOB data to check that the BLOB data is in fact loaded. The properties of each stored audio clip should be identical.

-- chkprop.sql
SET SERVEROUTPUT ON;
--connect auddemo/auddemo
--Query audtable for ORDSYS.ORDAudio content.

DECLARE
  audio ORDSYS.ORDAudio;
  idnum integer;
  properties_match BOOLEAN;
  ctx RAW(64) :=NULL;

BEGIN
-- Check the properties of the audio data clip imported into the 
-- ORDAudio object type. Properties for ID=1 should be identical
-- with ID=3.

  SELECT id, audio INTO idnum, audio FROM audtable WHERE id=1;
  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('----------------------------------------------');

-- Check the properties of the audio data clip copied into the 
-- ORDAudio object type from a BLOB stored in the database.
-- Properties for ID=1 should be identical with ID=3.

   SELECT id, audio INTO idnum, audio FROM audtable WHERE id=3;
   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;
/

The results from running the script chkprop.sql show that the properties are identical for each stored audio clip.

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:           15932
----------------------------------------------
audio id:          3
Check Properties Succeeded
audio encoding:        MS_PCM
audio number of channels:  1
audio MIME type:       audio/x-wav
audio file format:     WAVE
BLOB Length:           15932
----------------------------------------------

PL/SQL procedure successfully completed.

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

-- setup_audschema2.sql
-- Create the auddemo user, a tablespace, and a load directory to 
-- hold the BFILE:
@create_auduser.sql

-- Create a soundtable table and populate it with 
-- an audio clip:
@create_soundtable.sql

-- Create an audtable table and import an audio clip:
@create_audtable.sql

-- Copy a BLOB into an ORDAudio object, set the properties, 
-- and update the time:
@copyblob3.sql

-- Check the properties of the audio clips. The properties 
-- should be identical:
@chkprop.sql

--exit;

9.2.1 Using the ORDDoc Object Type as a Repository

The ORDDoc 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.

Example 9-3 shows how to create a repository of media using the tdoc table by running the createschema.sql script followed by the createindex.sql script.

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 different media, in this case, rows 1 and 2 with an audio clip, rows 3 and 4 with a video clip, and rows 5 and 6 with an image. 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 attribute of the object with a 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 file type (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. After loading the media data, display the MIME type, format, and content length of the doc column for each row.

Next, use Oracle Text and create a metadata index on the comments attribute of the ORDDOC column. Then, search for the format MPGA in the comments attribute of each row; only one row, row 2, returns a match. Finally, perform a substring search of the CLOB comments attribute for row 2 to locate the value MPGA (the value is bolded here for contrast). At this point, you can begin to search for other interesting media formats, such as MOOV; or mimeTypes, such as audio/mpeg, and so forth, in the stored rows using the Oracle Text index.

-- createschema.sql
-- Connect as SYSDBA to create a tablespace and a user.
-- May need to create a temp tablespace for this to work.

CONNECT SYS AS SYSDBA
SET SERVEROUTPUT ON;
SET ECHO ON;

--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, CTXAPP to docuser;
GRANT CREATE ANY DIRECTORY TO docuser;

GRANT EXECUTE ON CTX_CLS TO docuser;
GRANT EXECUTE ON CTX_DDL TO docuser;
GRANT EXECUTE ON CTX_DOC TO docuser;
GRANT EXECUTE ON CTX_OUTPUT TO docuser;
GRANT EXECUTE ON CTX_QUERY TO docuser;
GRANT EXECUTE ON CTX_REPORT TO docuser;
GRANT EXECUTE ON CTX_THES TO docuser;

-- End of SYSDBA tasks.

-- Begin user tasks.

CONNECT docuser/docuser
SET SERVEROUTPUT ON;
SET ECHO ON;

DROP TABLE tdoc;

-- Create the docdir directory. Replace directory specification with your own.
CREATE OR REPLACE DIRECTORY docdir
              as 'c:\media';
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());
INSERT INTO tdoc VALUES(3, ORDSYS.ORDDOC.init());
INSERT INTO tdoc VALUES(4, ORDSYS.ORDDOC.init());
INSERT INTO tdoc VALUES(5, ORDSYS.ORDDOC.init());
INSERT INTO tdoc VALUES(6, ORDSYS.ORDDOC.init());

DECLARE 
  obj ORDSYS.ORDDoc;
  ctx RAW(64) := 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;
-- 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;
-- 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;
/
--Display the MIME type, format, and content length of the media.
DECLARE
  doc ORDSYS.ORDDOC;
  idnum integer;
BEGIN
 FOR I IN 1..6 LOOP
  SELECT n, doc into idnum, doc from tdoc where n=I;
   dbms_output.put_line('media n:           '|| idnum);
   dbms_output.put_line('media MIME type:   '|| doc.getMimeType);
   dbms_output.put_line('media file format: '|| doc.getFormat);
   dbms_output.put_line('BLOB length:      '|| TO_CHAR(doc.getContentLength()));
   dbms_output.put_line('-------------------------------------------');
 END loop;
END;
/

-- Display the output.
media n:           1
media MIME type:   audio/x-wav
media file format: WAVE
BLOB length:       93594
-------------------------------------------
media n:           2
media MIME type:   audio/mpeg
media file format: MPGA
BLOB length:       51537
-------------------------------------------
media n:           3
media MIME type:   video/quicktime
media file format: MOOV
BLOB length:       4958415
-------------------------------------------
media n:           4
media MIME type:   video/quicktime
media file format: MOOV
BLOB length:       2891247
-------------------------------------------
media n:           5
media MIME type:   image/gif
media file format: GIFF
BLOB length:       1124
-------------------------------------------
media n:           6
media MIME type:   image/gif
media file format: GIFF
BLOB length:       686
-------------------------------------------
PL/SQL procedure successfully completed.

-- createindex.sql
-- Connect as DOCUSER. 
-- Create the index using Oracle Text.
--
CONNECT DOCUSER/DOCUSER;
SET SERVEROUTPUT ON;
SET ECHO ON;
--
-- Next, you can create a preference, and
-- create media attribute sections for each media attribute, -- that is, format, mimeType, and contentLength.
--
-- Create a preference.
EXECUTE ctx_ddl.drop_preference('ANNOT_WORDLIST');
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:
DROP INDEX mediaidx FORCE;
--
CREATE INDEX mediaidx ON tdoc(doc.comments) INDEXTYPE IS 
     CTXSYS.CONTEXT PARAMETERS('stoplist CTXSYS.EMPTY_STOPLIST wordlist 
     ANNOT_WORDLIST filter CTXSYS.NULL_FILTER section group MEDIAANN_TAGS');
COMMIT;
--
-- Now, perform a SELECT statement on the attributes in the doc.comments column.
--
SELECT score(1), n from tdoc t WHERE CONTAINS(t.doc.comments, 'MPGA',1)>0;
-- Should find one row, representing the aud2.mp3 audio file.
-- Display the results, MPGA is found in row 2.

  SCORE(1)          N
---------- ----------
         5          2

-- Look for MPGA in the comments attribute of row 2 (bolded value MPGA).

SELECT DBMS_LOB.SUBSTR(t.doc.comments, 2000,1) FROM tdoc t WHERE n=2;
-- Display the output.

DBMS_LOB.SUBSTR(T.DOC.COMMENTS,2000,1)
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<!-- Generated by Oracle interMedia Annotator 1.0 -->
<AudioCDTrackAnn dt="oracle.ord.media.annotator.annotations.AudioCDTrackAnn">
  <Attributes>
    <MEDIA_FORMAT_ENCODING desc="Format of the media" dt="java.lang.String"><![C
DATA[Layer III]]></MEDIA_FORMAT_ENCODING>
    <AUDIO_CD_TRACK_ALBUM desc="Audio CD Title" dt="java.lang.String"><![CDATA[N
one]]></AUDIO_CD_TRACK_ALBUM>
    <MEDIA_DURATION desc="Duration in seconds of the media" dt="java.lang.Long">
<![CDATA[4]]></MEDIA_DURATION>
    <MEDIA_BITRATE desc="Bitrate of the media in bits per second" dt="java.lang.
DBMS_LOB.SUBSTR(T.DOC.COMMENTS,2000,1)
--------------------------------------------------------------------------------
Integer"><![CDATA[96000]]></MEDIA_BITRATE>
    <MEDIA_FORMAT_ENCODING_CODE desc="Format of the media in the form of a verbo
se code" dt="java.lang.String"><![CDATA[LAYER3]]></MEDIA_FORMAT_ENCODING_CODE>
    <MEDIA_SOURCE_FILE_FORMAT_CODE desc="Media file format code" dt="java.lang.S
tring"><![CDATA[MPGA]]></MEDIA_SOURCE_FILE_FORMAT_CODE>
    <MEDIA_SOURCE_FILE_FORMAT desc="Media file format" dt="java.lang.String"><![
CDATA[MPEG1 Audio (ISO/IEC 11172-3)]]></MEDIA_SOURCE_FILE_FORMAT>
    <MEDIA_SOURCE_MIME_TYPE desc="MIME Type of the media/its samples" dt="java.l
ang.String"><![CDATA[audio/mpeg]]></MEDIA_SOURCE_MIME_TYPE>
    <AUDIO_ARTIST desc="Main artist for the audio clip" dt="java.lang.String"><!
[CDATA[Oracle]]></AUDIO_ARTIST>
DBMS_LOB.SUBSTR(T.DOC.COMMENTS,2000,1)
--------------------------------------------------------------------------------
    <AUDIO_NUM_CHANNELS desc="The number of audio channels" dt="java.lang.Intege
r"><![CDATA[1]]></AUDIO_NUM_CHANNELS>
    <AUDIO_SAMPLE_RATE desc="Audio sample rate (samples/sec)" dt="java.lang.Inte
ger"><![CDATA[44100]]></AUDIO_SAMPLE_RATE>
    <MEDIA_CONTENT_DATE desc="Creation date for the media content" dt="java.lang
.String"><![CDATA[1999]]></MEDIA_CONTENT_DATE>
    <MEDIA_USER_DATA desc="String containing all user data" dt="java.lang.String
"><![CDATA[Welcome to Oracle 8i64]]></MEDIA_USER_DATA>
    <MEDIA_TITLE desc="Title of the media" dt="java.lang.String"><![CDATA[welcom
e3.mp3]]></MEDIA_TITLE>
  </Attributes>
DBMS_LOB.SUBSTR(T.DOC.COMMENTS,2000,1)
--------------------------------------------------------------------------------
  <Samples>
  </Samples>
</AudioCDTrackAnn>

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

These scripts are end-to-end scripts that create and populate a media table from a BFILE data source. You can get to this site by selecting the Sample Code icon, then under Oracle Database, select Oracle interMedia to go to the Oracle interMedia Sample Code Web page.

The following set of scripts:

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

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

3. Loads the media data with a SELECT FOR UPDATE operation using an import method to import the data from a BFILE (importdoc.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_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 the <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.

This script creates the docdemo tablespace. It contains a data file named docdemo.dbf of 200 MB in size, an initial extent of 64 KB, and a next extent of 128 KB, 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.

-- create_docuser.sql
-- Connect as admin.
connect SYS AS SYSDBA/<SYS password>;

-- Edit this script and either enter your sys password here 
-- to replace <SYS password> or comment out this CONNECT 
-- statement and connect as SYS AS SYSDBA before running this script.

set serveroutput on
set echo on

-- You need SYSDBA 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;

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

-- DROP DIRECTORY docdir;

-- Delete and then create a 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 the 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 will get an error message
-- when you try to 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. Replace directory specification with your own.

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'

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 an empty BLOB.
insert into doctable values(1,ORDSYS.ORDDoc.init());

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

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(64) := 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;
/

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
connect docdemo/docdemo
set serveroutput on;
--Query doctable for ORDSYS.ORDDoc. 
DECLARE
  document ORDSYS.ORDDoc;
  idnum integer;
  properties_match BOOLEAN;
  ctx RAW(64) := 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. 

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

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

-- Create the media table:
@create_doctable.sql

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

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

--exit;

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(64) := 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.

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

These scripts are 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 Sample Code icon, then under Oracle Database, select Oracle interMedia to go to the Oracle interMedia Sample Code Web page.

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.

This script creates the imgdemo tablespace with a data file named imgdemo.dbf of 200 MB in size, with an initial extent of 64 KB, a next extent of 128 KB, 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.

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

set serveroutput on
set echo on

-- You need SYSDBA 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;

-- You need SYSDBA privileges to delete a directory. If there 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 the tablespace.
create tablespace imgdemo
       datafile 'imgdemo.dbf' size 200M
       minimum extent 64K
       default storage (initial 64K next 128K)
       logging;

-- Create the 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 will get an error message when you 
-- try to 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. Replace directory specification with your own.

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

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 an empty BLOB.
insert into imgtable values(1,ORDSYS.ORDImage.init());

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

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(64) := 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;
/

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
connect imgdemo/imgdemo
set serveroutput on;
--Query imgtable for ORDSYS.ORDImage. 
DECLARE
  image ORDSYS.ORDImage;
  idnum integer;
  properties_match BOOLEAN;

BEGIN
 FOR I IN 1..2 LOOP
  SELECT id, image into idnum, image from imgtable where id=I;
   dbms_output.put_line('image id:          '|| idnum);
  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.

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;

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;
Content length is: 1124
start position: 1
doing read  1
----------------
End of data

PL/SQL procedure successfully completed.

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

These scripts are 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 Sample Code icon, then under Oracle Database, select Oracle interMedia to go to the Oracle interMedia Sample Code Web page.

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.

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 an 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(64) := 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;
/

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 , image into idnum, image from imgtable where id=I; 
     dbms_output.put_line('image id: '|| idnum); 
    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; 
/ 

9.3.3 Addressing Globalization Support Issues

Example 9-4 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 that the ",75" is specified as the scale factor. This application addresses globalization support issues.

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

9.5.1 Handling interMedia Exceptions in PL/SQL

This section shows examples and describes handling exceptions in the interMedia PL/SQL Web Toolkit Photo Album application.

If your program tries to set the properties of an uploaded image (it reads the image data to get the values of the object attributes so it can store them in the appropriate attribute fields) and the image format is not recognized, then the setProperties( ) method will fail. To catch this exception and work around this potential problem, the application uses the following exception block:

BEGIN
   new_image.setProperties();
EXCEPTION
   WHEN OTHERS THEN
         new_image.contentLength := upload_size;
         new_image.mimeType := upload_mime_type;

In this example, this exception handler sets the MIME type and length of the image based on the values from the upload table described at the beginning of the insert_new_photo procedure. The browser sets a MIME type header when the file is uploaded. The application reads this header to set the ORDImage field.

If your program tries to proces an image in cases when the image format is unknown, then the processCopy( ) method will always fail. To work around this potential problem, the application uses the following exception block:

BEGIN
   new_image.processCopy( 'maxScale=50,50', new_thumb);
EXCEPTION
   WHEN OTHERS THEN
      new_thumb.deleteContent();
      new_thumb.contentLength := 0;
END;

In this example from the interMedia PL/SQL Web Toolkit Photo Album application, when the image format is unknown and a thumbnail image cannot be created, this exception handler deletes the content of the thumbnail image and sets its length to zero.

9.5.2 Handling interMedia Exceptions in Java

This section shows examples and describes handling exceptions using the try/catch block that are in either the interMedia Java Servlet Photo Album application or the interMedia JavaServer Pages Photo Album application, or are in both applications. In addition, there are several examples of throwing exceptions.

In the getConnection( ) method in both the PhotoAlbumServlet class of the interMedia Java Servlet Photo Album application and in the PhotoAlbumBean class of the interMedia JavaServer Pages Photo Album application, when trying to get a free connection from the stack, if the stack is empty, a new connection object is created. Within the try/catch block a call is made to the version compatibility initialization method. Making this call on the client-side is recommended to ensure that the application will always work, without upgrading, with any potential future release of interMedia, which may have evolved object types. See Oracle interMedia Java Classes Reference for more information about the OrdMediaUtil.imCompatibilityInit( ) method. A catch block catches any SQL exception and throws a new SQLException, returning a string representation of the object thrown with the toString( ) method.

private Connection getConnection()
        throws SQLException
    {
        OracleConnection conn = null;

        //
        // Synchronize on the stack object. Load the JDBC driver if not yet
        // done. If there's a free connection on the stack, then pop it off
        // the stack and return it to the caller. Otherwise, create a new
        // connection object and call the version compatibility initialization
        // method.
        //
        synchronized( connStack )
        {
            if ( !driverLoaded )
            {
                DriverManager.registerDriver(
                                    new oracle.jdbc.driver.OracleDriver() );
                driverLoaded = true;
            }
            if ( connStack.empty() )
            {
                conn = (OracleConnection)DriverManager.getConnection
                    ( JDBC_CONNECT_STRING, JDBC_USER_NAME, JDBC_PASSWORD );
                try
                {
                    OrdMediaUtil.imCompatibilityInit( conn );
                }
                catch ( Exception e )
                {
                    throw new SQLException( e.toString() );
                }
            }
            else
            {
                conn = (OracleConnection)connStack.pop();
            }
        }

        //
        // Enable auto-commit by default.
        //
        conn.setAutoCommit( true );

        return conn;
    }

In the insertNewPhoto( ) method in both the PhotoAlbumServlet class of the interMedia Java Servlet Photo Album application and in the PhotoAlbumBean class of the interMedia JavaServer Pages Photo Album application, a new photo is inserted into the photo album, creating a thumbnail image at the same time. If the application tries to process an image in cases when the image format is unknown, then when the application calls the processCopy( ) method, the application will always fail. To work around this potential problem, the application uses the following try block and catch block to catch any SQL exceptions:

}
        try
        {
            image.processCopy( "maxScale=50,50", thumb );
        }
        catch ( SQLException e )
        {
            thumb.deleteContent();
            thumb.setContentLength( 0 );
        }

In this example, when the image format is unknown and a thumbnail image cannot be created, the application catches the SQL exception and calls the deleteContent( ) method to delete the content of the thumbnail image, and then calls the setContentLength( ) method to set its length to zero.