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 media_dir;
 
-- Create the media_dir 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 media_dir AS 'C:/media_dir';

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

-- create_audtable.sql
--
-- This script must be run from a user with the CREATE TABLE privilege.
--
-- Create the audio_table table.
 
CREATE TABLE audio_table ( id NUMBER,
                       audio ORDAudio )
LOB(audio.source.localData) STORE AS SECUREFILE;

Example 9-6 import_aud.sql

--import_aud.sql
 
DECLARE
  obj ORDAUDIO;
  ctx RAW(64) := NULL;
 
BEGIN
 
  -- insert a row with ORDAudio object.
  INSERT INTO audio_table VALUES 
    (1, ORDAudio('FILE', 'MEDIA_DIR', 'aud1.wav'))
    returning audio into obj;
 
  --import audio clip aud1.wav from media_dir
  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
-- 
 
-- Use the ORDAudio constructor that takes BLOB as the input parameter
-- in the SQL INSERT statement.
-- 
-- 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.
-- 
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

--
-- 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) 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 a SQL statement.
--
clear columns
column id       format 99;
column encoding format a15;
column mimetype   format a20;
column fileformat   format a15;
column channels     format 99;
SELECT id, 
       t.audio.getEncoding() encoding, 
       t.audio.getNumberOfChannels() channels, 
       t.audio.getMimetype() mimetype, 
       t.audio.getFormat() fileformat 
from audio_table t;

Example 9-9 setup_audsample.sql

-- setup_audsample.sql
--
-- This script automates the demo by invoking each script in
-- the required order.
--
 
-- Create the media_dir 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 media_dir;
 
-- Drop the tables created by the demo.
DROP TABLE soundtable PURGE;
DROP TABLE audio_table PURGE;

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 media_dir;
 
-- Create the media_dir 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 media_dir AS 'C:/media_dir';
 
-- 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 media_dir 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.
--
 
CREATE TABLE doc_table ( id NUMBER,
                         document ORDDoc )
LOB(document.source.localData) STORE AS SECUREFILE;

Example 9-13 import_doc.sql

-- import_doc.sql
--
-- This script creates a procedure that 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 
-- MEDIA_DIR directory using the names specified in this script, or modify 
-- this script to match the file names of your media.
--
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 file 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-14 read_doc.sql

--read_doc.sql
 
 
SET SERVEROUTPUT ON
 
-- Read from the OrdDoc object column in the doc_table with 
-- the given id.
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 just reads the data into the buffer 32767 bytes
   -- at a time, then reads the next chunk, overwriting the first
   -- buffer full of data.
 END LOOP;
 EXCEPTION
     WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('End of data ');
     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-15 showprop_doc.sql

-- showprop_doc.sql
--
 
SET SERVEROUTPUT ON;
 
--
-- Query doctable for ORDDoc and print the properties using PL/SQL.
--
 
BEGIN
  FOR rec in (SELECT id, document FROM doc_table ) 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 doctable for ORDDoc and list the properties using a SQL statement.
--
clear columns
column id    format 99;
column mimetype format a20;
column format   format a10;
column length   format 99999999;
SELECT id, 
       t.document.getMimeType() mimetype, 
       t.document.getFormat() format, 
       t.document.getContentLength() length
from doc_table t;

Example 9-16 setup_docsample.sql

-- setup_docsample.sql
--
-- This script automates the demo by invoking each script in
-- the required order.
--
 
-- Create the media_dir 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 read_document(1);
 
--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 media_dir;
 
-- Drop the table and procedures created by the demo.
DROP TABLE doc_table PURGE;
DROP PROCEDURE read_document;
DROP PROCEDURE load_document;

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 media_dir;
 
-- Create the media_dir 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 media_dir AS 'C:/media_dir';
 
-- 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 media_dir 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.
 
CREATE TABLE image_table ( id NUMBER,
                           image ORDImage )
LOB(image.source.localData) STORE AS SECUREFILE;

Example 9-20 import_img.sql

-- import_img.sql
--
 
-- This procedure imports a image file from a local file system 
-- into image_table and sets the properties of the ORDImage object
-- during the import.
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-21 read_image.sql

-- 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 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;
 
--
-- This script displays properties of the image stored in image_table
-- using PL/SQL;
--
 
BEGIN
 
  FOR rec in (SELECT id, image from image_table) 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;
/

--
-- This script displays properties of the image 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;

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 read_image(1);
 
--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 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;
 
commit;
exit;