This chapter provides examples that show common operations with Oracle Multimedia.
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 rich media content under Multimedia Code Samples.
Note:
To run these examples on your system, use the files on the Oracle Multimedia Sample Code Web page. Do not attempt to compile and run the code in this chapter.This chapter includes these sections:
Audio data examples using Oracle Multimedia include common operations on audio data, such as using audio types with object views and using a set of scripts for populating an ORDAudio object with BLOB data stored in the database. The following subsections describe these operations:
See Also:
Oracle Multimedia Reference for reference information about the methods used in these examples
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 shown 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 Also:
See Oracle Database Concepts for more information about defining, using, and updating object views.
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 following sections.
| Script Name | Operations Performed |
|---|---|
|
(See Example 9-3) |
Creates an audio data load directory. (See Section 9.1.2.1) |
|
(See Example 9-4) |
Creates and populates the (See Section 9.1.2.2) |
|
(See Example 9-5) |
Creates the (See Section 9.1.2.3) |
|
(See Example 9-6) |
Loads the audio data. This script imports the audio data from an audio file into the (See Section 9.1.2.4) |
|
(See Example 9-7) |
Copies the BLOB data from the (See Section 9.1.2.5) |
|
(See Example 9-8) |
Displays the properties of the loaded audio data stored in the (See Section 9.1.2.6) |
|
(See Example 9-9) |
Automates the process by running the previous audio scripts in the required order. (See Section 9.1.2.7) |
|
(See Example 9-10) |
Cleans up by removing the sample tables, directories, and procedures from your database. (See Section 9.1.2.8) |
The create_mediadir.sql script creates the audio data load directory. This script is shown in Example 9-3. (See Section 9.2.1.1 and Section 9.3.1.1, respectively, for information about how to use this script to create the load directories for media data and image data.)
To load the audio data successfully, you must create a database directory object that points to a file directory on your system. Example 9-3 uses the media_dir directory, which points to the file directory C:\media_dir. You can edit the create_mediadir.sql script to replace the directory path in the CREATE OR REPLACE DIRECTORY statement with your directory specification.
This directory specified in the create_mediadir.sql script must contain your sample audio files. The audio examples use the sample file aud1.wav, which is installed in the <ORACLE_HOME>/ord/aud/demo directory. You can copy any supported audio files to the C:\media_dir directory to run the scripts in these examples.
Before running the create_mediadir.sql script, ensure that you have these privileges:
CREATE ANY DIRECTORY (to specify the directory specification for your audio files)
DROP ANY DIRECTORY (to delete previous instances of the audio data load directory)
Note:
If you run thecreate_mediadir.sql script as a different user than the user who ran the other audio scripts, you must perform these steps:
Uncomment the GRANT READ ON DIRECTORY statement.
Replace the string <USER> in this statement with the new user (for example: SCOTT).
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>;
The create_soundtable.sql script creates and populates the soundtable table. This table contains a BLOB column; it is created to demonstrate how to populate a table with an Oracle Multimedia ORDAudio column from a table with a BLOB column. This script is shown in Example 9-4.
This script creates the soundtable table, inserts a row with an empty BLOB, loads the BLOB with audio data, and then checks the length of the BLOB data. You can replace the name of the data file in the create_soundtable.sql script with the name of the data file you plan to use.
Before running this script, ensure that you have the CREATE TABLE privilege.
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;
/
The create_audtable.sql script creates the audio_table table with the two columns id and audio. The audio column is defined as type ORDAudio. This script is shown in Example 9-5.
Before running this script, ensure that you have the CREATE TABLE privilege.
The import_aud.sql script inserts a row into the audio_table table, and imports audio data from an audio file into the audio column in the audio_table table using the ORDAudio import( ) method. This script is shown in Example 9-6.
To run this script successfully, you must copy one audio clip to your media_dir directory using the name 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 previous audio scripts.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;
/
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 audio 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 previous audio scripts.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;
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 into two rows in the audio_table table. This script verifies that the audio data that was loaded using the ORDAudio import( ) method matches the audio data that was 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 previous audio scripts.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;
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
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.
Before running this script, ensure that you have these privileges:
CREATE ANY DIRECTORY
CREATE TABLE
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;
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.
Before running this script, ensure that you have the DROP ANY DIRECTORY privilege.
Note:
Run this script as the user who ran the previous audio scripts.Media data examples using Oracle Multimedia include common operations on heterogeneous data, such as using a set of scripts for populating an ORDDoc object from a file data source. The following subsection describes this operation:
See Also:
Oracle Multimedia Reference for reference information about the methods used in these examples
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 following sections.
| Script Name | Operations Performed |
|---|---|
|
(See Example 9-3) |
Creates a media data load directory. (See Section 9.2.1.1) |
|
(See Example 9-11) |
Creates the (See Section 9.2.1.2) |
|
(See Example 9-12) |
Loads the media data. This script imports the media data from a file into the (See Section 9.2.1.3) |
|
(See Example 9-13) |
Reads the media data from a BLOB using a stored procedure. (See Section 9.2.1.4) |
|
(See Example 9-14) |
Displays the properties of the loaded media data stored in the (See Section 9.2.1.5) |
|
(See Example 9-15) |
Automates the process by running the previous media scripts in the required order. (See Section 9.2.1.6) |
|
(See Example 9-16) |
Cleans up by removing the sample tables, directories, and procedures from your database. (See Section 9.2.1.7) |
The create_mediadir.sql script creates the media data load directory. This script is shown in Example 9-3.
To load the media data successfully, you must create a database directory object that points to a file directory on your system. Example 9-3 uses the media_dir directory, which points to the file directory C:\media_dir. You can edit the create_mediadir.sql script to replace the directory path in the CREATE OR REPLACE DIRECTORY statement with your directory specification.
This directory specified in the create_mediadir.sql script must contain your sample media files. The media examples use the sample files aud1.wav and aud2.mp3, which are installed in the <ORACLE_HOME>/ord/aud/demo directory. You can copy any supported media files to the C:\media_dir directory to run the scripts in these examples.
Before running the create_mediadir.sql script, ensure that you have these privileges:
CREATE ANY DIRECTORY (to specify the directory specification for your media files)
DROP ANY DIRECTORY (to delete previous instances of the media data load directory)
Note:
If you run thecreate_mediadir.sql script as a different user than the user who ran the other media scripts, you must perform these steps:
Uncomment the GRANT READ ON DIRECTORY statement.
Replace the string <USER> in this statement with the new user (for example: SCOTT).
The create_doctable.sql script creates the doc_table table with the two columns id and document. The document column is defined as type ORDDoc. This script is shown in Example 9-11.
Before running this script, ensure that you have the CREATE TABLE privilege.
The import_doc.sql script inserts two rows into the doc_table table, and imports media data from a media file into the document column in the doc_table table using the ORDDoc import( ) method. This script is shown in Example 9-12.
To run this script successfully, 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 previous media scripts.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');
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-13.
Note:
Run this script as the user who ran the previous media scripts.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
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.
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-14.
Note:
Run this script as the user who ran the previous media scripts.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;
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
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-15.
Before running this script, ensure that you have these privileges:
CREATE ANY DIRECTORY
CREATE TABLE
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;
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-16.
Before running this script, ensure that you have the DROP ANY DIRECTORY privilege.
Note:
Run this script as the user who ran the previous media scripts.Image data examples using Oracle Multimedia include common operations on image data, such as using a set of scripts for populating an ORDImage object from a file data source, using a set of scripts for loading an image table from an HTTP data source, and addressing issues related to globalization support. The following subsections describe these operations:
See Also:
Oracle Multimedia Reference for reference information about the methods used in these examples
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 following sections.
| Script Name | Operations Performed |
|---|---|
|
(See Example 9-3) |
Creates an image data load directory. (See Section 9.3.1.1) |
|
(See Example 9-17) |
Creates the (See Section 9.3.1.2) |
|
(See Example 9-18) |
Loads the image data. This script imports the image data from a file into the (See Section 9.3.1.3) |
|
(See Example 9-19) |
Reads the image data from a BLOB using a stored procedure. (See Section 9.3.1.4) |
|
(See Example 9-20) |
Displays the properties of the loaded image data stored in the (See Section 9.3.1.5) |
|
(See Example 9-21) |
Automates the process by running the previous image scripts in the required order. (See Section 9.3.1.6) |
|
(See Example 9-22) |
Cleans up by removing the sample tables, directories, and procedures from your database. (See Section 9.3.1.7) |
The create_mediadir.sql script creates the image data load directory. This script is shown in Example 9-3.
To load the image data successfully, you must create a database directory object that points to a file directory on your system. Example 9-3 uses the media_dir directory, which points to the file directory C:\media_dir. You can edit the create_mediadir.sql script to replace the directory path in the CREATE OR REPLACE DIRECTORY statement with your directory specification.
This directory specified in the create_mediadir.sql script must contain your sample image files. The image examples use the sample files img71.gif and img50.gif, which are installed in the <ORACLE_HOME>/ord/img/demo directory. You can copy any supported image files to the C:\media_dir directory to run the scripts in these examples.
Before running the create_mediadir.sql script, ensure that you have these privileges:
CREATE ANY DIRECTORY (to specify the directory specification for your image files)
DROP ANY DIRECTORY (to delete previous instances of the image data load directory)
Note:
If you run thecreate_mediadir.sql script as a different user than the user who ran the other image scripts, you must perform these steps:
Uncomment the GRANT READ ON DIRECTORY statement.
Replace the string <USER> in this statement with the new user (for example: SCOTT).
The create_imgtable.sql script creates the image_table table with the two columns id and image. The image column is defined as type ORDImage. This script is shown in Example 9-17.
Before running this script, ensure that you have the CREATE TABLE privilege.
The import_img.sql script inserts two rows into the image_table table, and imports image data from an image file into the image column in the image_table table using the ORDImage import( ) method. This script is shown in Example 9-18.
To run this script successfully , 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 previous image scripts.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');
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-19.
Note:
Run this script as the user who ran the previous image scripts.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
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.
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-20.
Note:
Run this script as the user who ran the previous image scripts.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;
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
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-21.
Before running this script, ensure that you have these privileges:
CREATE ANY DIRECTORY
CREATE TABLE
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;
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-22.
Before running this script, ensure that you have the DROP ANY DIRECTORY privilege.
Note:
Run this script as the user who ran the previous image scripts.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-23.
To run this script successfully, you must modify it to point to two images located on your Web site, as described in Example 9-23.
Example 9-23 Import Image Data from an HTTP Data Source
-- import_imghttp.sql
--
-- Import the two HTTP images from a Web site into the database.
-- Prerequisites
-- Follow these steps 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 Web site.
-- Insert two rows with an empty BLOB.
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;
DECLARE
obj ORDSYS.ORDIMAGE;
ctx RAW(64) := NULL;
BEGIN
-- This imports the image file image1.gif from the HTTP source URL
-- (srcType=HTTP), and automatically sets the properties.
select Image into obj from image_table where id = 7 for update;
obj.import(ctx);
update image_table set image = obj where id = 7;
commit;
-- This imports the image file image2.gif from the HTTP source URL
-- (srcType=HTTP), and automatically sets the properties.
select Image into obj from image_table where id = 8 for update;
obj.import(ctx);
update image_table set image = obj where id = 8;
commit;
END;
/
The globalization.sql script demonstrates how to address issues related to globalization support. It shows how to use the process( ) 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. Thus,",75" is specified as the scale factor. This script is shown in Example 9-24.
Example 9-24 Address a Globalization Support Issue
-- globalization.sql
--
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 (Example 9-20) to see the properties of the scaled image.
See Oracle Multimedia Reference for more information about ensuring the correct globalization support interpretation when using the process( ) method.
Video data examples using Oracle Multimedia are not available in this chapter.
See Also:
Oracle Multimedia Reference for reference information and video data examples