9 Oracle Multimedia Examples

Example 9-3 create_mediadir.sql

-- create_mediadir.sql
-- You must have the CREATE ANY DIRECTORY privilege to run this script.
--
-- You must edit this script to:
--  o replace the directory path in the CREATE DIRECTORY statement 
--    with your own (see below for details)
--  o uncomment the GRANT READ ON DIRECTORY statement and grant to the 
--    user under which you will run this demo
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
-- You need DROP ANY DIRECTORY privilege to delete a directory. If there 
-- is no need to delete the directory, then leave the next line 
-- commented out.
-- DROP DIRECTORY mediadir;
 
-- Create the mediadir load directory, the directory where the image
-- clips reside. You must specify the disk drive and path in the 
-- CREATE DIRECTORY statement below. Edit this file to replace the 
-- directory specification with your own.
CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';

-- If you intend to run this demo under a user other than the user 
-- that just created this directory, edit the following grant to grant 
-- READ on the directory just created to the user under which you will 
-- run the demo scripts.  For example, if you will run the demo scripts 
-- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".  
-- Then uncomment the following GRANT statement.  There is no need to
-- do this if the user creating the directory will also be used to run
-- the other demo scripts.
-- GRANT READ ON DIRECTORY mediadir TO <USER>;

Example 9-4 create_soundtable.sql

-- create_soundtable.sql
--
-- This script must be run from a user with CREATE TABLE privilege.
--
-- Create the soundtable table.  This table is used ONLY to show
-- how to copy data from a BLOB column to an ORDAudio column.
--
-- 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.
-- Close the files and commit the transaction.
-- Check the length of the BLOB loaded. Is the length
-- what you are expecting?
--
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
DROP TABLE soundtable PURGE;
CREATE TABLE soundtable ( id number,
                         sound BLOB
       default EMPTY_BLOB() )
--
-- store audio data as SecureFile LOBs
--
LOB(sound) STORE AS SECUREFILE;
-- 
INSERT INTO soundtable(id, sound) VALUES (1, EMPTY_BLOB());
COMMIT;

DECLARE
   f_lob BFILE := BFILENAME('MEDIADIR','aud1.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);
 
-- Populate the BLOB from the 'aud1.wav' file in the BFILE
  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 and verify length is not null.
  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;
/

Example 9-5 create_audtable.sql

-- create_audtable.sql
--
-- This script must be run from a user with the CREATE TABLE privilege.
--
-- Create the audio_table table.
-- Insert two rows with empty BLOBs and initialize object attributes.
 
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
DROP TABLE audio_table PURGE;
 
 
CREATE TABLE audio_table ( id NUMBER,
                       audio ORDAudio )
LOB(audio.source.localData) STORE AS SECUREFILE;
 
 
-- Insert rows with an empty BLOB and initialize the object attributes.
 
INSERT INTO audio_table VALUES(1,ORDAudio.init());
INSERT INTO audio_table VALUES(2,ORDAudio.init());
 
COMMIT;

Example 9-6 import_aud.sql

--import_aud.sql
 
DECLARE
  obj ORDAUDIO;
  ctx RAW(64) := NULL;
 
BEGIN
 
  -- selects the audio column for update
  SELECT audio INTO obj FROM audio_table WHERE id = 1 FOR UPDATE;
 
  --import audio clip aud1.wav from mediadir
  obj.setSource('FILE','MEDIADIR','aud1.wav');
  obj.import(ctx);
 
  --set properties
  obj.setProperties(ctx);
 
  --update table with audio object 
  UPDATE audio_table SET audio = obj WHERE id = 1;
 
 
  COMMIT;
 
END;
/

Example 9-7 copy_audblob.sql

--copy_audblob.sql
--
 
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 audio_table table containing an audio column defined as
-- an ORDAudio object type in which the ID=2. 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 ORDAudio;
  ctx RAW(64) :=NULL;
 
BEGIN
  UPDATE audio_table T SET T.audio.source.localData = (SELECT sound FROM
         soundtable S WHERE S.id = 1) WHERE T.id=2;
  COMMIT;
 
  SELECT audio INTO obj FROM audio_table WHERE id = 2 FOR UPDATE;
  obj.setProperties(ctx);
  obj.setUpdateTime(SYSDATE);
  UPDATE audio_table SET audio = obj WHERE id = 2;
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Operation failed');
 
END;
/
 
EXECUTE UPDATE_PROC;

Example 9-8 showprop_aud.sql

-- showprop_aud.sql
--
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
--Query audio_table for ORDAudio content.
--
-- This script verifies that the properties of the audio data 
-- in the row with ID=1 (which was populated with the ORDAudio 
-- import() method), match the properties of the audio data
-- in the row with ID-2 (which was populated by copying data
-- from a BLOB in the soundtable table.
--
 
DECLARE
  audio 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=2.
 
  dbms_output.put_line(' Properties of these audio clips are identical:');
 
  SELECT id, audio INTO idnum, audio FROM audio_table WHERE id=1;
  dbms_output.put_line('Properties for id: ' || idnum);
 
  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=2.
 
  SELECT id, audio INTO idnum, audio FROM audio_table WHERE id=2;
  dbms_output.put_line('Properties for id: ' || idnum);
  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;
/

Example 9-9 setup_audsample.sql

-- setup_audsample.sql
--
-- This script automates the demo by invoking each script in
-- the required order.
--
 
-- Create the mediadir load directory
@create_mediadir.sql
 
-- Create a soundtable table and populate it with
-- an audio clip:
@create_soundtable.sql
 
-- Create an audtable table 
@create_audtable.sql
 
--import an audio clip
@import_aud.sql
 
-- Copy a BLOB into an ORDAudio object, set the properties,
-- and update the time:
@copy_audblob.sql
 
-- Check the properties of the audio clips. The properties
-- should be identical:
@showprop_aud.sql
 
--exit;

Example 9-10 cleanup_audsample.sql

-- cleanup_audsample.sql
--
-- This script removes all tables, procedures, and directories 
-- created by this demonstration.  You must have the DROP ANY 
-- DIRECTORY privilege to drop the audio load directory.  This 
-- script should be run under the same user as the demo was run 
-- under.
 
-- drop the audio load directory.
DROP DIRECTORY mediadir;
 
-- Drop the tables created by the demo.
DROP TABLE soundtable PURGE;
DROP TABLE audio_table PURGE;
 
-- Drop the Update procedure.
DROP PROCEDURE update_proc;
 
commit;

Example 9-11 create_mediadir.sql

-- create_mediadir.sql
-- You must have the CREATE ANY DIRECTORY privilege to run this script.
--
-- You must edit this script to:
--  o replace the directory path in the CREATE DIRECTORY statement 
--    with your own (see below for details)
--  o uncomment the GRANT READ ON DIRECTORY statement and grant to the 
--    user under which you will run this demo
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
-- You need DROP ANY DIRECTORY privilege to delete a directory. If there 
-- is no need to delete the directory, then leave the next line 
-- commented out.
-- DROP DIRECTORY mediadir;
 
-- Create the mediadir load directory, the directory where the media
-- data resides. You must specify the disk drive and path in the 
-- CREATE DIRECTORY statement below. Edit this file to replace the 
-- directory specification with your own.
CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';
 
-- If you intend to run this demo under a user other than the user 
-- that just created this directory, edit the following grant to grant 
-- READ on the directory just created to the user under which you will 
-- run the demo scripts.  For example, if you will run the demo scripts 
-- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".  
-- Then uncomment the following GRANT statement.  There is no need to
-- do this if the user creating the directory will also be used to run
-- the other demo scripts.
-- GRANT READ ON DIRECTORY mediadir TO <USER>;

Example 9-12 create_doctable.sql

-- create_doctable.sql
--
-- This script must be run from a user with the CREATE TABLE privilege.
--
-- Create the doc_table table.
-- Insert two rows with empty ORDDoc columns.
--
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
DROP TABLE doc_table PURGE;
CREATE TABLE doc_table ( id NUMBER,
                       document ORDDoc )
LOB(document.source.localData) STORE AS SECUREFILE;
 
-- Insert a row with an empty ORDDoc object.
INSERT INTO doc_table VALUES(1,ORDDoc.init());
-- Insert a row with an empty BLOB.
INSERT INTO doc_table VALUES(2,ORDDoc.init());
 
COMMIT;

Example 9-13 import_doc.sql

-- import_doc.sql
--
-- This script uses the import method to load the media data into the
-- ORDDoc column.  It then extracts properties from the media using
-- the setProperties method.  
--
-- To successfully run this script, you must copy two media files to your 
-- MEDIADIR directory using the names specified in this script, or modify 
-- this script to match the file names of your media.
--
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
DECLARE
  obj ORDDOC;
  ctx RAW(64) := NULL;
 
BEGIN
-- This imports the audio file aud1.wav from the MEDIADIR directory
-- on a local file system (srcType=file) and sets the properties.
 
  SELECT document INTO obj FROM doc_table WHERE id = 1 FOR UPDATE;
  obj.setSource('FILE','MEDIADIR','aud1.wav');
  obj.import(ctx,TRUE);
 
  UPDATE doc_table SET document = obj WHERE id = 1;
  COMMIT;
 
-- This imports the audio file aud2.mp3 from the MEDIADIR directory
-- on a local file system (srcType=file) and sets the properties.
  SELECT document INTO obj FROM doc_table WHERE id = 2 FOR UPDATE;
  obj.setSource('FILE','MEDIADIR','aud2.mp3');
  obj.import(ctx,TRUE);
 
  UPDATE doc_table SET document = obj WHERE id = 2;
  COMMIT;
END;
/

Example 9-14 read_doc.sql

--read_doc.sql
 
 
SET SERVEROUTPUT ON
SET ECHO ON
 
----Read from the OrdDoc object column in the doc_table.
create or replace procedure readdocument as
obj 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 doc_table 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

Example 9-15 showprop_doc.sql

-- showprop_doc.sql
--
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
--
-- Query doctable for ORDDoc.
 
DECLARE
  document ORDDoc;
  idnum integer;
  ctx RAW(64) := NULL;
BEGIN
  FOR I IN 1..2 LOOP
  SELECT id, document INTO idnum, document FROM doc_table 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;
/

Example 9-16 setup_docsample.sql

-- setup_docsample.sql
--
-- This script automates the demo by invoking each script in
-- the required order.
--
 
-- Create the mediadir load directory
@create_mediadir.sql
 
-- Create the media table:
@create_doctable.sql
 
--Import 2 media clips and set properties:
@import_doc.sql
 
--Display the properties of the media clips:
@showprop_doc.sql
 
--create stored procedure to read from ordDoc
@read_doc.sql
 
--Execute stored procedure
execute readdocument
 
--exit;

Example 9-17 cleanup_docsample.sql

-- cleanup_docsample.sql
--
-- This script removes all tables and directories created by this 
-- demonstration.  You must have the DROP ANY DIRECTORY privilege 
-- to drop the doc load directory.  This script should be run under 
-- the same user as the demo was run under.
 
-- drop the doc load directory.
DROP DIRECTORY mediadir;
 
-- Drop the table created by the demo.
DROP TABLE doc_table PURGE;

Example 9-18 create_mediadir.sql

-- create_mediadir.sql
-- You must have the CREATE ANY DIRECTORY privilege to run this script.
--
-- You must edit this script to:
--  o replace the directory path in the CREATE DIRECTORY statement 
--    with your own (see below for details)
--  o uncomment the GRANT READ ON DIRECTORY statement and grant to the 
--    user under which you will run this demo
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
-- You need DROP ANY DIRECTORY privilege to delete a directory. If there 
-- is no need to delete the directory, then leave the next line 
-- commented out.
-- DROP DIRECTORY mediadir;
 
-- Create the mediadir load directory, the directory where the image
-- clips reside. You must specify the disk drive and path in the 
-- CREATE DIRECTORY statement below. Edit this file to replace the 
-- directory specification with your own.
CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';
 
-- If you intend to run this demo under a user other than the user 
-- that just created this directory, edit the following grant to grant 
-- READ on the directory just created to the user under which you will 
-- run the demo scripts.  For example, if you will run the demo scripts 
-- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".  
-- Then uncomment the following GRANT statement.  There is no need to
-- do this if the user creating the directory will also be used to run
-- the other demo scripts.
-- GRANT READ ON DIRECTORY mediadir TO <USER>;

Example 9-19 create_imgtable.sql

-- create_imgtable.sql
--
-- This script must be run from a user with the CREATE TABLE privilege.
--
-- Create the image_table table.
-- Insert two  rows with empty OrdImage columns  and initialize object attributes.
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
DROP TABLE image_table PURGE;
CREATE TABLE image_table ( id NUMBER,
                       image ORDImage )
LOB(image.source.localData) STORE AS SECUREFILE;
 
 
-- Insert rows with empty OrdImage columns and initialize the object attributes.
 
INSERT INTO image_table VALUES(1,ORDImage.init());
INSERT INTO image_table VALUES(2,ORDImage.init());
 
COMMIT;

Example 9-20 import_img.sql

-- import_img.sql
--
SET SERVEROUTPUT ON
SET ECHO ON
 
-- Import the two files into the database.
 
DECLARE
  obj ORDIMAGE;
  ctx RAW(64) := NULL;
BEGIN
-- This imports the image file img71.gif from the MEDIADIR directory
-- on a local file system (srcType=file). 
-- the import method  also sets the object properties by reading the image blob.
  select Image into obj from image_table where id = 1 for update;
  obj.setSource('file','MEDIADIR','img71.gif');
  obj.import(ctx);
  update image_table set image = obj where id = 1;
commit;
 
-- This imports the image file img50.gif from the MEDIADIR directory
select Image into obj from image_table where id = 2 for update;
obj.setSource('file','MEDIADIR','img50.gif');
obj.import(ctx);
update image_table set image = obj where id = 2;
commit;
END;
/

Example 9-21 read_image.sql

-- read_image.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 ORDImage;
amt BINARY_INTEGER := 32767;
pos integer := 1;
read_cnt integer := 1;
BEGIN
  Select t.image.getcontent() into src from image_table t where t.id = 1;
  Select image into obj from image_table 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

Example 9-22 showprop_img.sql

-- showprop_img.sql
--
 
 
SET SERVEROUTPUT ON;
SET ECHO ON;
 
-- This script displays properties of the image stored in image_table
--
 
DECLARE
 image ORDImage;
 idnum integer;
 rowcount integer;
BEGIN
 
  FOR I IN 1..2 LOOP
    SELECT id, image into idnum, image from image_table where id=I;
    dbms_output.put_line('Image properties:');
    dbms_output.put_line('image id: '|| idnum);
    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;
/

Example 9-23 setup_imgsample.sql

-- setup_imgsample.sql
--
-- This script automates the demo by invoking each script in
-- the required order.
--
 
-- Create the imgdir load directory
@create_mediadir.sql
 
-- Create image table:
@create_imgtable.sql
 
--Import images into image_table
@import_img.sql
 
--Show properties of images
@showprop_img.sql
 
--create stored procedure to read from ordImage
@read_image.sql
 
--Execute stored procedure
execute readimage
 
--exit;

Example 9-24 cleanup_imgsample.sql

-- cleanup_imgsample.sql
--
-- This script removes all tables, procedures, and directories 
-- created by this demonstration.  You must have the DROP ANY 
-- DIRECTORY privilege to drop the image load directory.  This 
-- script should be run under the same user as the demo was run 
-- under.
 
-- drop the image load directory.
DROP DIRECTORY mediadir;
 
-- Drop the tables created by the demo.
DROP TABLE image_table PURGE;
 
-- Drop the Update procedure.
DROP PROCEDURE read_image;
 
commit;
exit;