9 Oracle Multimedia Examples

Example 9-3 create_mediadir.sql Script

-- create_mediadir.sql
--
SET SERVEROUTPUT ON;
SET ECHO ON;
 
-- To delete the directory, uncomment the next statement; 
-- otherwise, leave it commented out.
-- DROP DIRECTORY media_dir;
 
-- To specify a different directory path, replace the default directory 
-- path with the new path in the next statement.
CREATE OR REPLACE DIRECTORY media_dir AS 'C:\media_dir';

-- To change the user, uncomment the next statement and replace the 
-- string "<USER>" with the new user. Otherwise, leave the statement 
-- commented out.
-- GRANT READ ON DIRECTORY media_dir TO <USER>;

Example 9-4 create_soundtable.sql Script

-- create_soundtable.sql
--
-- 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;
 
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('MEDIA_DIR','aud1.wav');
   b_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;
 
  -- Check the length of the LOB.
  SELECT dbms_lob.getlength(t.sound) INTO length FROM soundtable t WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('The length is '|| length);
END;
/

Example 9-5 create_audtable.sql Script

-- create_audtable.sql
--
CREATE TABLE audio_table ( id NUMBER,
                       audio ORDAudio )
LOB(audio.source.localData) STORE AS SECUREFILE;

Example 9-6 import_aud.sql Script

--import_aud.sql
--
DECLARE
  obj ORDAUDIO;
  ctx RAW(64) := NULL;
 
BEGIN
 
  -- Insert a row with an ORDAudio object.
  INSERT INTO audio_table VALUES 
    (1, ORDAudio('FILE', 'MEDIA_DIR', 'aud1.wav'))
    returning audio into obj;
 
  --Import the audio clip aud1.wav from media_dir.
  obj.import(ctx);
 
  --Set the properties.
  obj.setProperties(ctx);
 
  --Update the table with the audio object. 
  UPDATE audio_table SET audio = obj WHERE id = 1;
 
  COMMIT;
 
END;
/

Example 9-7 copy_audblob.sql Script

--copy_audblob.sql
-- 
-- Use the ORDAudio constructor that takes a BLOB as the input parameter
-- in the SQL INSERT statement.
-- 
-- In this case, the BLOB (an audio clip), which was stored in
-- a row with ID = 1 in the soundtable table, is copied to a row 
-- with ID = 2 in the audio_table table containing an audio column 
-- defined as an ORDAudio object type.
-- 
INSERT INTO audio_table
  (select 2, ORDAudio(S.sound) FROM soundtable S WHERE S.id = 1);
 
DECLARE
 obj ORDSYS.ORDAudio;
 ctx RAW(40) := NULL;
BEGIN
SELECT audio INTO obj FROM audio_table WHERE id = 2 for update;
obj.setProperties(ctx);
UPDATE audio_table SET audio = obj WHERE ID = 2;
END;
/
 
COMMIT; 

Example 9-8 showprop_aud.sql Script

-- showprop_aud.sql
--
SET SERVEROUTPUT ON;
 
--
--Query audio_table for ORDAudio content in PL/SQL.
--
 
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:');
 
  FOR rec in (SELECT id, audio FROM audio_table ORDER BY id) LOOP 
    dbms_output.put_line('Properties for id: ' || rec.id);
 
    dbms_output.put_line('audio encoding: ' || rec.audio.getEncoding);
    dbms_output.put_line('audio number of channels: '|| 
                        rec.audio.getNumberOfChannels);
    dbms_output.put_line('audio MIME type: ' || rec.audio.getMimeType);
    dbms_output.put_line('audio file format: ' || rec.audio.getFormat);
    dbms_output.put_line
        ('----------------------------------------------');
  END LOOP;
END;
/
--
-- Query audio_table for ORDAudio and list the properties using SQL.
--
clear columns
column id       format 99;
column encoding format a15;
column mimetype   format a20;
column fileformat   format a15;
column channels     format 99;
SELECT t.id, 
       t.audio.getEncoding() encoding, 
       t.audio.getNumberOfChannels() channels, 
       t.audio.getMimetype() mimetype, 
       t.audio.getFormat() fileformat 
from audio_table t ORDER BY t.id;

Example 9-9 setup_audsample.sql Script

-- setup_audsample.sql
--
 
-- Create the media_dir load directory:
@create_mediadir.sql
 
-- Create the soundtable table and populate it with
-- an audio clip:
@create_soundtable.sql
 
-- Create the audio_table 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 Script

-- cleanup_audsample.sql
--
-- Drop the audio load directory.
-- DROP DIRECTORY media_dir;
 
-- Drop the tables created by the demo.
DROP TABLE soundtable PURGE;
DROP TABLE audio_table PURGE;

exit;

Example 9-11 create_doctable.sql Script

-- create_doctable.sql
--
CREATE TABLE doc_table ( id NUMBER,
                         document ORDDoc )
LOB(document.source.localData) STORE AS SECUREFILE;

Example 9-12 import_doc.sql Script

-- import_doc.sql
--
CREATE OR REPLACE PROCEDURE load_document (in_id INTEGER, 
                                           in_dir VARCHAR2, 
                                           in_fname VARCHAR2)
AS
  obj ORDDOC;
  ctx RAW(64) := NULL;
BEGIN
  INSERT INTO doc_table VALUES 
    (in_id, ORDDoc('FILE', in_dir, in_fname))
    RETURNING document INTO obj;
  obj.import(ctx,TRUE);
  UPDATE doc_table SET document = obj WHERE id = in_id;
  COMMIT;
END;
/
show errors;
 
-- Import the audio files aud1.wav and aud2.mp3 from the MEDIA_DIR directory
-- on a local file system. 
EXECUTE load_document(1, 'MEDIA_DIR', 'aud1.wav');
EXECUTE load_document(2, 'MEDIA_DIR', 'aud2.mp3');

Example 9-13 read_doc.sql Script

--read_doc.sql
--
SET SERVEROUTPUT ON
 
create or replace procedure read_document( in_id integer) as
obj ORDDoc;
buffer RAW (32767);
numBytes integer;
bytesRead integer := 0;
startpos integer := 1;
ctx RAW(64) := NULL;
BEGIN
  select document into obj from doc_table where id = in_id;
  DBMS_OUTPUT.PUT_LINE('Content length is: ' || obj.getContentLength());
 
  LOOP
    numBytes := 32767;
    startpos := startpos + bytesRead;
    obj.readFromSource(ctx,startPos,numBytes,buffer);
    bytesRead := numBytes;
 
    DBMS_OUTPUT.PUT_LINE('start position: '|| startPos);
    DBMS_OUTPUT.PUT_LINE('read ' || bytesRead || ' bytes.');
 
   -- Note: Add your own code here to process the media data being read.
   -- This routine 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 ');
     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:' || SQLERRM);
END;
/
show errors
 

Example 9-14 showprop_doc.sql Script

-- showprop_doc.sql
--
SET SERVEROUTPUT ON;
 
--
-- Query doc_table for ORDDoc and print the properties using PL/SQL.
--
 
BEGIN
  FOR rec in (SELECT id, document FROM doc_table ORDER BY id) LOOP 
    dbms_output.put_line('document id: '|| rec.id);
    dbms_output.put_line('document MIME type: '|| rec.document.getMimeType());
    dbms_output.put_line('document file format: '|| rec.document.getFormat());
    dbms_output.put_line('BLOB Length: '|| rec.document.getContentLength());
    dbms_output.put_line('----------------------------------------------');
  END loop;
END;
/
 
--
-- Query doc_table for ORDDoc and list the properties using SQL.
--
clear columns
column id    format 99;
column mimetype format a20;
column format   format a10;
column length   format 99999999;
SELECT t.id, 
       t.document.getMimeType() mimetype, 
       t.document.getFormat() format, 
       t.document.getContentLength() length
from doc_table t ORDER BY t.id;

Example 9-15 setup_docsample.sql Script

-- setup_docsample.sql
--
 
-- Create the media_dir load directory:
@create_mediadir.sql
 
-- Create the doc_table table:
@create_doctable.sql
 
--Import 2 media clips and set the properties:
@import_doc.sql
 
--Display the properties of the media clips:
@showprop_doc.sql
 
--Create a stored procedure to read from ordDoc:
@read_doc.sql
 
--Execute the stored procedure:
execute read_document(1);
 
--exit;

Example 9-16 cleanup_docsample.sql Script

-- cleanup_docsample.sql
--
-- Drop the doc load directory.
-- DROP DIRECTORY media_dir;
 
-- Drop the table and procedures created by the demo.
DROP TABLE doc_table PURGE;
DROP PROCEDURE read_document;
DROP PROCEDURE load_document;

exit;

Example 9-17 create_imgtable.sql Script

-- create_imgtable.sql
--
CREATE TABLE image_table ( id NUMBER,
                           image ORDImage )
LOB(image.source.localData) STORE AS SECUREFILE;

Example 9-18 import_img.sql Script

-- import_img.sql
--
CREATE OR REPLACE PROCEDURE load_image(in_id INTEGER,
                                       in_dir VARCHAR2,
                                       in_fname VARCHAR2)
AS
  obj ORDIMAGE;
  ctx RAW(64) := NULL;
BEGIN
  INSERT INTO image_table VALUES
      (in_id, ORDImage('FILE', in_dir, in_fname))
      RETURNING image INTO obj;
  obj.import(ctx);
  UPDATE image_table SET image = obj WHERE id = in_id;
  COMMIT;
END;
/
show errors
 
-- Import the two files into the database.
EXECUTE load_image(1, 'MEDIA_DIR', 'img71.gif');
EXECUTE load_image(2, 'MEDIA_DIR', 'img50.gif');

Example 9-19 read_image.sql Script

-- read_image.sql
--
set serveroutput on
create or replace procedure read_image (in_id integer) 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;
amt integer; 
pos integer := 1;
bytesRead integer := 0;
length integer;
BEGIN
  Select t.image.getcontent(), t.image.getContentLength()
    into src, length from image_table t where t.id = in_id;
  DBMS_OUTPUT.PUT_LINE('Content length is: '|| length);
 
  LOOP
    amt := 32767;
    pos := pos + bytesRead;
    DBMS_LOB.READ(src,amt,pos,buffer);
    bytesRead := amt;
 
    DBMS_OUTPUT.PUT_LINE('start position: '|| pos);
    DBMS_OUTPUT.PUT_LINE('bytes read '|| bytesRead);
-- Note: Add your own code here to process the image data being read.
-- This routine 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-20 showprop_img.sql Script

-- showprop_img.sql
--
SET SERVEROUTPUT ON;
 
--
-- Display the properties of the images stored in image_table using PL/SQL.
--
 
BEGIN
 
  FOR rec in (SELECT id, image from image_table ORDER BY id) LOOP
    dbms_output.put_line('Image properties:');
    dbms_output.put_line('image id: '|| rec.id);
    dbms_output.put_line('image height: '|| rec.image.getHeight());
    dbms_output.put_line('image width: '|| rec.image.getWidth());
    dbms_output.put_line('image MIME type: '|| rec.image.getMimeType());
    dbms_output.put_line('image file format: '|| rec.image.getFileFormat());
    dbms_output.put_line('BLOB Length: '|| rec.image.getContentLength());
    dbms_output.put_line('-------------------------------------------');
  END loop;
END;
/

--
-- Display the properties of the images stored in image_table using SQL.
--
clear columns
column id        format 99;
column height    format 999999;
column width     format 999999;
column mimetype  format a15;
column fileformat format a10;
column length     format 999999999;
select t.id, 
       t.image.getHeight() height,
       t.image.getWidth() width,
       t.image.getMimetype() mimetype,
       t.image.getFileFormat() fileformat,
       t.image.getContentLength() length
from image_table t ORDER BY t.id;

Example 9-21 setup_imgsample.sql Script

-- setup_imgsample.sql
--
 
--Create the media_dir load directory:
@create_mediadir.sql
 
--Create the image_table table:
@create_imgtable.sql
 
--Import images into the image_table table:
@import_img.sql
 
--Show the properties of the images:
@showprop_img.sql
 
--Create a stored procedure to read from ordImage:
@read_image.sql
 
--Execute the stored procedure:
execute read_image(1);
 
--exit;

Example 9-22 cleanup_imgsample.sql Script

-- cleanup_imgsample.sql
--
-- Drop the image load directory.
DROP DIRECTORY media_dir;
 
-- Drop the tables created by the demo.
DROP TABLE image_table PURGE;
 
-- Drop the procedures.
DROP PROCEDURE read_image;
DROP PROCEDURE load_image;
 
exit;