Skip Headers
Oracle® Multimedia User's Guide
11g Release 2 (11.2)

Part Number E10777-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

9 Oracle Multimedia Examples

This chapter provides examples that show common operations with Oracle Multimedia. The examples are presented in these sections:

These scripts, and other examples, can be found on the Oracle Multimedia Sample Code section of the Oracle Technology Network (OTN) Web site at

http://www.oracle.com/technology/products/multimedia/

Select Sample Code under Oracle Multimedia Resources to go to the Oracle Multimedia Sample Code Web page. On that page, select Use Multimedia and PL/SQL to manage media content under Multimedia Code Samples.

9.1 Audio Data Examples

Audio data examples using Oracle Multimedia include the following common operations:

Reference information on the methods used in these examples is presented in Oracle Multimedia Reference.

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 -- of either built-in or user-defined types -- from data 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).

Example 9-1 Define a Relational Table Containing No ORDAudio Object

create table flat (
   id                NUMBER,
   description       VARCHAR2(4000),
   localData         BLOB,
   srcType           VARCHAR2(4000),
   srcLocation       VARCHAR2(4000),
   srcName           VARCHAR2(4000),
   upDateTime        DATE,
   local             NUMBER,
   format            VARCHAR2(31),
   mimeType          VARCHAR2(4000),
   comments          CLOB,
   encoding          VARCHAR2(256),
   numberOfChannels  NUMBER,
   samplingRate      NUMBER,
   sampleSize        NUMBER,
   compressionType   VARCHAR2(4000),
   audioDuration     NUMBER,
)
--
-- store audio data as SecureFile LOBs
--
LOB(localData) STORE AS SECUREFILE;

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

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

create or replace view object_audio_v as 
  select
      id,
      ORDSYS.ORDAudio(T.description,
      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;

Object views provide the flexibility of looking at the same relational or object data in more than one way. Therefore, you can use different in-memory object representations for different applications without changing the way you store the data in the database. See Oracle Database Concepts for more information about defining, using, and updating object views.

9.1.2 Scripts for Populating an ORDAudio Object with BLOB Data

The scripts presented in this section demonstrate how to populate an Oracle Multimedia ORDAudio object from an existing BLOB stored in the database.

Table 9-1 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-1 Audio Scripts

Script Name Operations Performed

create_mediadir.sql

Example 9-3

Creates an audio data load directory.

(See Section 9.1.2.1)

create_soundtable.sql

Example 9-4

Creates and populates the soundtable table.

(See Section 9.1.2.2)

create_audtable.sql

Example 9-5

Creates the audio_table table.

(See Section 9.1.2.3)

import_aud.sql

Example 9-6

Loads the audio data. This script imports the audio data from the soundtable table into the audio_table table using the ORDAudio import( ) method.

(See Section 9.1.2.4)

copy_audblob.sql

Example 9-7

Copies the BLOB data from the soundtable table to the audio_table table using a SQL UPDATE statement.

(See Section 9.1.2.5)

showprop_aud.sql

Example 9-8

Displays the properties of the loaded audio data stored in the audio_table table.

(See Section 9.1.2.6)

setup_audsample.sql

Example 9-9

Automates the process by running the previous audio scripts in the required order.

(See Section 9.1.2.7)

cleanup_audsample.sql

Example 9-10

Cleans up the process by removing the sample tables, directories, and procedures from your database.

(See Section 9.1.2.8)


9.1.2.1 Create an Audio Data Load Directory

The create_mediadir.sql script creates the audio data load directory. This script is shown in Example 9-3.

To successfully load the audio data, you must have a directory created on your system. Example 9-3 uses the media_dir directory. This directory must contain your sample audio clip file. The following examples use the sample file aud1.wav. Actually, you can copy any supported audio files to the media_dir directory to run the scripts in these examples. You must specify the directory path and disk drive of the media_dir directory in the CREATE DIRECTORY statement in the file create_mediadir.sql.

Note:

To run this script, you must have the CREATE ANY DIRECTORY privilege. To delete previous instances of the audio data load directory, you must also have the DROP ANY DIRECTORY privilege.

You must edit the create_mediadir.sql file to replace the directory path in the CREATE DIRECTORY statement. If you run the create_mediadir.sql script as a different user than the user who ran the other demo scripts, you must also replace the user in the GRANT READ statement. (See Example 9-3 for detailed instructions.)

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

9.1.2.2 Create and Populate the soundtable Table

The create_soundtable.sql script creates and populates the soundtable table. This table contains a BLOB column; it shows how to populate a table with an Oracle Multimedia ORDAudio column from a table with a BLOB column. The soundtable table is created for demonstration purposes only. This script is shown in Example 9-4.

To demonstrate how to populate a table with an Oracle Multimedia ORDAudio column from a table with a BLOB column, first we must have a table with a BLOB column. The soundtable table is our sample table with a BLOB column. This script creates the soundtable table, inserts a row with an empty BLOB, loads the BLOB with with audio data, and then checks the length of the BLOB data.

Be sure to change the data file name in the create_soundtable.sql script to correspond with the name of the data file you use.

Note:

To run this script, you must have the CREATE TABLE privilege.

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

9.1.2.3 Create the audio_table Table

The create_audtable.sql script creates the audio_table table. This table contains an ORDAudio column. This script creates the audio_table table with two columns (id, audio). This script is shown in Example 9-5.

Note:

To run this script, you must have the CREATE TABLE privilege.

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;

9.1.2.4 Load the Audio Data

The import_aud.sql script inserts a row into the audio_table table and imports audio data from an audio file (in the soundtable table) into the ORDAudio column in the audio_table table using the ORDAudio import( ) method. This script is shown in Example 9-6.

To successfully run this script, you must copy one audio clip to your media_dir directory using the names specified in this script, or modify this script to match the file names of your audio clips.

This script loads the same audio clip that was loaded by the create_soundtable.sql script. It is used later in the showprop_aud.sql script to show that data loaded with the import( ) method matches the data copied from the BLOB column of the soundtable table.

Note:

Run this script as the user who ran the scripts in Example 9-4 and Example 9-5.

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

9.1.2.5 Copy the BLOB Data to the ORDAudio Object

The copy_audblob.sql script inserts a row with id=2 into the audio_table table and copies the audio data in the sound column of the soundtable table into the ORDAudio object column of the audio_table table for a row with id=2. The script uses the ORDAudio constructor that takes a BLOB as the input parameter. It also sets the properties of the audio data after inserting it. This script is shown in Example 9-7.

Note:

Run this script as the user who ran the scripts in Example 9-4, Example 9-5, and Example 9-6.

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; 

9.1.2.6 Show the Properties of the Loaded Audio Data

The showprop_aud.sql script displays the properties of the audio data clips stored in the audio_table table. They should be identical. Different load methods were used to load the same audio clip from the soundtable table into two rows in the audio_table table. This script verifies that audio data loaded using the ORDAudio import( ) method matches audio data copied from a BLOB column of the soundtable table. This script is shown in Example 9-8.

Note:

Run this script as the user who ran the scripts in Example 9-4, Example 9-5, Example 9-6, and Example 9-7.

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;

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

Properties of these audio clips are identical:
Properties for id: 1
audio encoding: MS_PCM
audio number of channels: 1
audio MIME type: audio/x-wav
audio file format: WAVE
----------------------------------------------
Properties for id: 2
audio encoding: MS_PCM
audio number of channels: 1
audio MIME type: audio/x-wav
audio file format: WAVE
----------------------------------------------
 
PL/SQL procedure successfully completed.
 
 
ID ENCODING        CHANNELS MIMETYPE             FILEFORMAT     
--- --------------- -------- -------------------- ---------------
  1 MS_PCM                 1 audio/x-wav          WAVE
  2 MS_PCM                 1 audio/x-wav          WAVE

9.1.2.7 Automate the ORDAudio Examples

The setup_audsample.sql script runs each of the previous audio scripts in the correct order to automate this process. This script is shown in Example 9-9.

Note:

To run this script, you must have CREATE ANY DIRECTORY and CREATE TABLE privileges.

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;

9.1.2.8 Clean Up the ORDAudio Examples

The cleanup_audsample.sql script removes the sample tables, directories, and procedures created by the previous audio scripts from your database. This script is shown in Example 9-10.

Note:

To run this script, you must have the DROP ANY DIRECTORY privilege. And, you must run it as the user who ran the previous audio scripts.

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;

9.2 Media Data Examples

Media data examples using Oracle Multimedia include the following common operation on heterogeneous data:

Reference information on the methods used in these examples is presented in Oracle Multimedia Reference.

9.2.1 Scripts for Populating an ORDDoc Object from a File Data Source

The scripts presented in this section demonstrate how to populate an ORDDoc object from an existing file.

Table 9-2 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-2 Media Scripts

Script Name Operations Performed

create_mediadir.sql

Example 9-11

Creates a media data load directory.

(See Section 9.2.1.1)

create_doctable.sql

Example 9-12

Creates the doc_table table.

(See Section 9.2.1.2)

import_doc.sql

Example 9-13

Loads the media data. This script imports the media data from a file into the doc_table table using the ORDDoc import( ) method.

(See Section 9.2.1.3)

read_doc.sql

Example 9-14

Reads the media data from a BLOB using a stored procedure.

(See Section 9.2.1.4)

showprop_doc.sql

Example 9-15

Displays the properties of the loaded media data stored in the doc_table table.

(See Section 9.2.1.5)

setup_docsample.sql

Example 9-16

Automates the process by running the previous media scripts in the required order.

(See Section 9.2.1.6)

cleanup_docsample.sql

Example 9-17

Cleans up the process by removing the sample tables, directories, and procedures from your database.

(See Section 9.2.1.7)


9.2.1.1 Create a Media Data Load Directory

The create_mediadir.sql script creates the media data load directory. This script is shown in Example 9-11.

To successfully load the media data, you must have a directory created on your system. Example 9-11 uses the media_dir directory. This directory must contain your sample audio media files. The following examples use the sample files aud1.wav and aud2.mp3. Actually, you can copy any supported media files to the media_dir directory to run the scripts in these examples. You must specify the directory path and disk drive of the media_dir directory in the CREATE DIRECTORY statement in the create_mediadir.sql file.

Note:

To run this script, you must have the CREATE ANY DIRECTORY privilege. To delete previous instances of the media data load directory, you must also have the DROP ANY DIRECTORY privilege.

You must edit the create_mediadir.sql file to replace the directory path in the CREATE DIRECTORY statement. If you run the create_mediadir.sql script as a different user than the user who ran the other demo scripts, you must also replace the user in the GRANT READ statement. (See Example 9-11 for detailed instructions.)

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

9.2.1.2 Create the doc_table Table

The create_doctable.sql script creates the doc_table table. This table contains an ORDDoc column. This script creates the doc_table table with two columns (id, document). This script is shown in Example 9-12.

Note:

To run this script, you must have the CREATE TABLE privilege.

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;

9.2.1.3 Load the Media Data

The import_doc.sql script inserts two rows into the doc_table table and imports media data from a media file into the ORDDoc column in the doc_table table using the ORDDoc import( ) method. This script is shown in Example 9-13.

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

Note:

Run this script as the user who ran the script in Example 9-12.

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

9.2.1.4 Read the Media Data from the BLOB

The read_doc.sql script reads media data from a BLOB by creating the stored procedure read_document. This procedure reads a specified amount of media data from the BLOB attribute, beginning at a particular offset, until all the media data is read. This script is shown in Example 9-14.

Note:

Run this script as the user who ran the scripts in Example 9-12 and Example 9-13.

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
 

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on; 
SQL> execute read_document(1); 

Content length is: 93594
start position: 1
read 32767 bytes.
start position: 32768
read 32767 bytes.
start position: 65535
read 28060 bytes.
End of data

PL/SQL procedure successfully completed.

9.2.1.5 Show the Properties of the Loaded Media Data

The showprop_doc.sql script displays the properties of the media data loaded into the doc_table table. This script is shown in Example 9-15.

Note:

Run this script as the user who ran the scripts in Example 9-12, Example 9-13, and Example 9-14.

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;

The results from running the script showprop_doc.sql are the following:

SQL> @showprop_doc.sql 

document id: 1
document MIME type: audio/x-wav
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.
 
 
 ID MIMETYPE             FORMAT        LENGTH
--- -------------------- ---------- ---------
  1 audio/x-wav          WAVE           93594
  2 audio/mpeg           MPGA           51537

9.2.1.6 Automate the ORDDoc Examples

The setup_docsample.sql script runs each of the previous media scripts in the correct order to automate this process. This script is shown in Example 9-16.

Note:

To run this script, you must have CREATE ANY DIRECTORY and CREATE TABLE privileges.

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;

9.2.1.7 Clean Up the ORDDoc Examples

The cleanup_docsample.sql script removes the sample tables, directories, and procedures created by the previous media scripts from your database. This script is shown in Example 9-17.

Note:

To run this script, you must have the DROP ANY DIRECTORY privilege, and you must run it as the user who ran the previous media scripts.

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;

9.3 Image Data Examples

Image data examples using Oracle Multimedia include the following common operations:

Reference information on the methods used in these examples is presented in Oracle Multimedia Reference.

9.3.1 Scripts for Populating an ORDImage Object from a File Data Source

The scripts presented in this section demonstrate how to populate an Oracle Multimedia ORDImage object from an existing file.

Table 9-3 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-3 Image Scripts

Script Name Operations Performed

create_mediadir.sql

Example 9-18

Creates an image data load directory.

(See Section 9.3.1.1)

create_imgtable.sql

Example 9-19

Creates the image_table table.

(See Section 9.3.1.2)

import_img.sql

Example 9-20

Loads the image data. This script imports the image data from a file into the image_table table using the ORDImage import( ) method.

(See Section 9.3.1.3)

read_image.sql

Example 9-21

Reads the image data from a BLOB using a stored procedure.

(See Section 9.3.1.4)

showprop_img.sql

Example 9-22

Displays the properties of the loaded image data stored in the image_table table.

(See Section 9.3.1.5)

setup_imgsample.sql

Example 9-23

Automates the process by running the previous image scripts in the required order.

(See Section 9.3.1.6)

cleanup_imgsample.sql

Example 9-24

Cleans up the process by removing the sample tables, directories, and procedures from your database.

(See Section 9.3.1.7)


9.3.1.1 Create an Image Data Load Directory

The create_mediadir.sql script creates the image data load directory. This script is shown in Example 9-18.

To successfully load the image data, you must have a media_dir directory created on your system. This directory must contain your sample image media files, img71.gif and img50.gif, which are installed in the <ORACLE_HOME>/ord/img/demo directory. Actually, you can copy any supported image files to the media_dir directory to run this script. You must specify this directory path and disk drive in the CREATE DIRECTORY statement in the create_mediadir.sql file.

Note:

To run this script, you must have the CREATE ANY DIRECTORY privilege. To delete previous instances of the image data load directory, you must also have the DROP ANY DIRECTORY privilege.

You must edit the create_mediadir.sql file to replace the directory path in the CREATE DIRECTORY statement. If you run the create_mediadir.sql script as a different user than the user who ran the other demo scripts, you must also replace the user in the GRANT READ statement. (See Example 9-18 for detailed instructions.)

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

9.3.1.2 Create the image_table Table

The create_imgtable.sql script creates the image_table table. This table contains an ORDImage column. This script creates the image_table table with two columns (id, image). This script is shown in Example 9-19.

Note:

To run this script, you must have the CREATE TABLE privilege.

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;

9.3.1.3 Load the Image Data

The import_img.sql script inserts two rows into the image_table table and imports image data from an image file into the ORDImage column in the image_table table using the ORDImage import( ) method. This script is shown in Example 9-20.

To successfully run this script, you must copy two image files to your media_dir directory using the file names specified in this script, or modify this script to match the file names of your image files.

Note:

Run this script as the user who ran the script in Example 9-19.

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

9.3.1.4 Read the Image Data from the BLOB

The read_image.sql script reads image data from a BLOB by creating the stored procedure read_image. This procedure reads a specified amount of image data from the BLOB attribute, beginning at a particular offset, until all the image data is read. This script is shown in Example 9-21.

Note:

Run this script as the user who ran the scripts in Example 9-19 and Example 9-20.

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
 

To execute the stored procedure, enter the following SQL statements:

SQL> set serveroutput on;
SQL> execute read_image(1);
Content length is: 1124
start position: 1
bytes read 1124
----------------
End of data

PL/SQL procedure successfully completed.

9.3.1.5 Show the Properties of the Loaded Image Data

The showprop_img.sql script displays the properties of the image data loaded into the image_table table. This script is shown in Example 9-22.

Note:

Run this script as the user who ran the scripts in Example 9-19, Example 9-20, and Example 9-21.

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;

The results from running the script showprop_img.sql are the following:

SQL> @showprop_img.sql
Image properties:
image id: 1
image height: 15
image width: 43
image MIME type: image/gif
image file format: GIFF
BLOB Length: 1124
-------------------------------------------
Image properties:
image id: 2
image height: 32
image width: 110
image MIME type: image/gif
image file format: GIFF
BLOB Length: 686
-------------------------------------------
 
PL/SQL procedure successfully completed.
 
 
 ID  HEIGHT   WIDTH MIMETYPE        FILEFORMAT     LENGTH
--- ------- ------- --------------- ---------- ----------
  1      15      43 image/gif       GIFF             1124
  2      32     110 image/gif       GIFF              686
 

9.3.1.6 Automate the ORDImage Examples

The setup_imgsample.sql script runs each of the previous image scripts in the correct order to automate this process. This script is shown in Example 9-23.

Note:

To run this script, you must have CREATE ANY DIRECTORY and CREATE TABLE privileges.

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;

9.3.1.7 Clean Up the ORDImage Examples

The cleanup_imgsample.sql script removes the sample tables, directories, and procedures created by the previous image scripts from your database. This script is shown in Example 9-24.

Note:

To run this script, you must have the DROP ANY DIRECTORY privilege, and you must run it as the user who ran the previous image scripts.

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;

9.3.2 Script for Loading an Image Table from an HTTP Data Source

The import_imghttp.sql script imports the image data from an HTTP data source. This script inserts two rows into the image_table table and loads the image data from the specified HTTP data source (source type HTTP, URL location, and HTTP object name). This script is shown in Example 9-25.

To successfully run this script, you must modify it to point to two images located on your Web site.

Example 9-25 import_imghttp.sql Script

--import_imghttp.sql
-- Import the two HTTP images from a Web site into the database.
-- PreRequisites: 
--    You will need to do the following  before running this script
--        1. Run create_imgdir.sql 
--        2. Run create_imgtable.sql
--        3. Modify the HTTP URL and object name to point to two images on 
--           your own Web site.
 
-- Import two images from HTTP source URLs.
 
 
-- Insert two rows with an empty BLOB.
-- See above section on pre requisites
insert into image_table values (7,ORDImage(
'http','http://your_website/images','image1.jpg'));
 
insert into image_table values (8,ORDImage(
'http','http://your_website/images','image2.gif'));
 
commit;

9.3.3 Addressing Globalization Support Issues

Example 9-26 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 example addresses globalization support issues.

Example 9-26 Address a Globalization Support Issue

ALTER SESSION SET NLS_LANGUAGE = FRENCH;
ALTER SESSION SET NLS_TERRITORY = FRANCE;
DECLARE
myimage ORDImage;
BEGIN
 
SELECT image into myimage from image_table where id=1 for update;
myimage.process('scale=",75"');
UPDATE image_table SET image = myimage where id=1;
COMMIT;
END;
/ 

Run the showprop_img.sql script to see the properties of the scaled image.

9.4 Video Data Examples

See Oracle Multimedia Reference for video data examples.

As an Amazon Associate I earn from qualifying purchases.