Release 8.1.7
Part Number A85336-01
 Library |
 Product |
 Contents |
 Index |
| Oracle interMedia Audio, Image, and Video User's Guide and Reference Release 8.1.7 Part Number A85336-01 |
|
This chapter provides examples that show common operations with Oracle interMedia. Examples are presented by audio, image, and video data groups followed by a section that describes how to extend interMedia to support a new data source.
interMedia audio examples include the following common operations:
The audio data examples in this section use a table of songs and a table of CDs. For each song, the following information is stored: a CDRef (REF into the CD table), a song ID, song title, artist, awards, time period of song, duration of song, clipRef (REF into the audio clips table or music video), text content, and the audio source containing lyrics. (A REF refers to row objects with globally unique object IDs that capture references between row objects; row objects are automatically indexed for fast access.) For each CD, the following are stored: an item ID, CD DB ID, CD title, CD artist, CD category, copyright, name of producer, awards, time period, rating, duration, text content, cover image, and the list of songs on the CD.
Reference information on the methods used in these examples is presented in Chapter 4.
Example 2-1 describes how to define a Song object.
CREATE TYPE songObject as OBJECT ( cdRef REF CdObject, -- REF into the cd table songId VARCHAR2(20), title VARCHAR2(4000), artist VARCHAR2(4000), awards VARCHAR2(4000), timePeriod VARCHAR2(20), duration INTEGER, clipRef REF clipObject, -- REF into the clips table (music video) txtcontent CLOB, audioSource ORDSYS.ORDAUDIO );
Example 2-2 describes how to create an object table named SongsTable.
CREATE TABLE SongsTable of songObject (UNIQUE (songId), songId NOT NULL);
Example 2-3 describes how to create a list object containing a list of references to songs.
CREATE TYPE songNstType AS TABLE of REF songObject; CREATE TYPE songList AS OBJECT (songs songNstType, MEMBER PROCEDURE addSong(s IN REF songObject));
Example 2-4 describes how to define the implementation of the songList object.
CREATE TYPE BODY songList AS MEMBER PROCEDURE addSong(s IN REF songObject) IS pos INTEGER := 0; BEGIN IF songs IS NULL THEN songs := songNstType(NULL); pos := 0; ELSE pos := songs.count; END IF; songs.EXTEND; songs(pos+1) := s; END; END;
This section describes how to create a CD object and a CD table of audio clips that includes, for each audio clip, the following information:
Example 2-5 creates a CD object named CdObject, and a CD table named CdTable that contains the CD information.
CREATE TYPE CdObject as OBJECT ( itemId INTEGER, cddbID INTEGER, title VARCHAR2(4000), artist VARCHAR2(4000), category VARCHAR2(20), copyright VARCHAR2(4000), producer VARCHAR2(4000), awards VARCHAR2(4000), timePeriod VARCHAR2(20), rating VARCHAR2(256), duration INTEGER, txtcontent CLOB, coverImg REF ORDSYS.ORDImage, songs songList); CREATE TABLE CdTable OF CdObject (UNIQUE(itemId), itemId NOT NULL) NESTED TABLE songs.songs STORE AS song_store_table;
Example 2-6 describes how to insert a song into the SongsTable table.
-- Insert a song into the songs table INSERT INTO SongsTable VALUES (NULL, '00', 'Under Pressure', 'Queen', 'no awards', '80-90', 243, NULL, EMPTY_CLOB(), ORDSYS.ORDAudio,init()); -- Check songs insertion SELECT s.title FROM SongsTable s WHERE songId = '00';
Example 2-7 describes how to insert a CD into the CdTable table.
-- insert a cd into the cd table INSERT INTO CdTable VALUES (1, 23232323, 'Queen Classics', 'Queen', 'rock', 'BMV Company', 'BMV', 'Grammy', '80-90', 'no rating', 4000, -- in seconds EMPTY_CLOB(), NULL, songList(NULL)); -- Check cd insertion SELECT cd.title FROM Cdtable cd;
Example 2-8 describes how to load a song into the SongsTable table. This example requires an AUDDIR directory to be defined; see the comments in the example.
-- Load a Song into the SongsTable -- Create your directory specification below -- CREATE OR REPLACE DIRECTORY AUDDIR AS '/audio/'; DECLARE audioObj ORDSYS.ORDAUDIO; ctx RAW(4000) := NULL; BEGIN SELECT S.audioSource INTO audioObj FROM SongsTable S WHERE S.songId = '00' FOR UPDATE; audioObj.setSource('FILE', 'AUDDIR', 'UnderPressure.au'); audioObj.setMimeType('audio/basic'); audioObj.import(ctx); audioObj.setProperties(ctx); UPDATE SongsTable S SET S.audioSource = audioObj WHERE S.songId = '00'; COMMIT; END; -- Check song insertion DECLARE audioObj ORDSYS.ORDAUDIO; ctx RAW(4000) := NULL; BEGIN SELECT S.audioSource INTO audioObj FROM SongsTable S WHERE S.songId = '00'; dbms_output.put_line('Content Length: ' || audioObj.getContentLength(ctx)); dbms_output.put_line('Content MimeType: ' || audioObj.getMimeType()); END;
Example 2-9 describes how to insert a reference to a song object into the songs list in the CdTable table.
-- Insert a reference to a SongObject into the Songs List in the CdTable Table DECLARE songRef REF SongObject; songListInstance songList; BEGIN SELECT REF(S) into songRef FROM SongsTable S where S.songId = '00'; SELECT C.songs INTO songListInstance FROM CdTable C WHERE C.itemId = 1 FOR UPDATE; songListInstance.addSong(songRef); UPDATE CdTable C SET C.songs = songListInstance WHERE C.itemId = 1; COMMIT; END; -- Check insertion of ref -- This example works for the first entry inserted in the songList DECLARE song SongObject; songRef REF SongObject; songListInstance songList; songType songNstType; BEGIN SELECT C.songs INTO songListInstance FROM CdTable C WHERE C.itemId = 1; SELECT songListInstance.songs INTO songType FROM DUAL; songRef := songType(1); SELECT DEREF(songRef) INTO song FROM DUAL; dbms_output.put_line('Song Title: ' || song.title); END;
Example 2-10 describes how to add a CD reference to a song.
-- Adding a cd reference to a song DECLARE songCdRef REF CdObject; BEGIN SELECT S.cdRef INTO songCdRef FROM SongsTable S WHERE S.songId = '00' FOR UPDATE; SELECT REF(C) INTO songCdRef FROM CdTable C WHERE C.itemId = 1; UPDATE SongsTable S SET S.cdRef = songCdRef WHERE S.songId = '00'; COMMIT; END; -- Check cd Ref DECLARE cdRef REF CdObject; cd CdObject; BEGIN SELECT S.cdRef INTO cdRef FROM SongsTable S WHERE S.songId = '00'; SELECT DEREF(cdRef) INTO cd FROM DUAL; dbms_output.put_line('Cd Title: ' || cd.title); END;
Example 2-11 describes how to retrieve audio data from a song in a CD.
FUNCTION retrieveAudio(itemID IN INTEGER, songId IN INTEGER) RETURN BLOB IS obj ORDSYS.ORDAudio; BEGIN select S.audioSource into obj from SongsTable S where S.songId = songId; return obj.getContent; END;
To support a new audio data format, implement the required interfaces in the ORDX_<format>_AUDIO package in the ORDPLUGINS schema (where <format> represents the name of the new audio data format). See Section 4.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_AUDIO package. Use the package body example in Section 4.4.2 as a template to create the audio package body. Then set the new format parameter in the setFormat call to the appropriate format value to indicate to the audio object that package ORDPLUG-INS.ORDX_<format>_AUDIO is available as a plug-in.
See Section F.1 for more information on installing your own format plug-in and running the sample scripts provided. See the fplugins.sql and fpluginb.sql files that are installed in the $ORACLE_HOME/ord/aud/demo/ directory. These are demonstration (demo) plug-ins that you can use as a guideline to write any format plug-in that you want to support. See the auddemo.sql file in this same directory to learn how to install your own format plug-in.
This section describes how to extend Oracle interMedia with a new object type.
You can use any of the interMedia objects types as the basis for a new type of your own creation.
See Example 2-3 and Example 2-4 for brief examples. See Example 2-26 for a more complete example and description.
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 from data -- of either built-in or user-defined types -- 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 2-12, consider the following relational table (containing no ORDAudio objects).
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, audioclip RAW(2000) );
You can create an object view on the relational table shown in Example 2-12 as follows in Example 2-13.
create or replace view object_audio_v as select id, ordsys.ORDAudio( T.description, T.localData, T.comments, T.format, T.encoding, T.numberOfChannels, T.samplingRate, T.sampleSize, T.compressionType, T.audioDuration, T.audioclip) AUDIO 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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://technet.oracle.com/ as an end-to-end script that creates and populates an audio table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.
The following set of scripts:
The fifth script (setup_audschema.sql) automates this entire process by running each script in the required order. The last script (readaudio.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of audio data from the BLOB, beginning at a particular offset, until all the audio data is read. To successfully load the audio data, you must have an auddir directory created on your system. This directory contains the aud1.wav and aud2.mp3 files, which are installed in <ORACLE_HOME>/ord/aud/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_auduser.sql file.
This script creates the auddemo tablespace. It contains a data file named auddemo.dbf of 200MB in size, an initial extent of 64K, and a next extent of 128K, and turns on table logging. Next, the auddemo user is created and given connect, resource, create library, and create directory privileges followed by creating the audio data load directory. Before running this script, you must change the create directory line to point to your data load directory location.
-- create_auduser.sql -- Connect as admin connect system/<system password>; -- Edit this script and either enter your system password here -- to replace <system password> or comment out this connect -- statement and connect as system before running this script. set serveroutput on set echo on -- Need system manager privileges to delete a user. -- Note: There is no need to delete auddemo user if you do not delete -- the auddemo tablespace, therefore comment out the next line. -- drop user auddemo cascade; -- Need system manager privileges to delete a directory. If there is no need to -- delete it, then comment out the next line. -- drop directory auddir; -- Delete then create tablespace. -- Note: It is better to not delete and create tablespaces, -- so comment this next line out. The create tablespace statement -- will fail if it already exists. -- drop tablespace auddemo including contents; -- If you uncomment the preceding line and really want to delete the -- auddemo tablespace, remember to manually delete the auddemo.dbf -- file to complete this operation. Otherwise, you cannot create -- the auddemo tablespace again because the auddemo.dbf file -- already exists. Therefore, it might be best to create this tablespace -- once and not delete it. create tablespace auddemo datafile 'auddemo.dbf' size 200M minimum extent 64K default storage (initial 64K next 128K) logging; -- Create auddemo user. create user auddemo identified by auddemo default tablespace auddemo temporary tablespace temp; -- Note: If you do not have a temp tablespace already defined, you will have to -- create it first for this script to work. grant connect, resource, create library to auddemo; grant create any directory to auddemo; -- Note: If this user already exists, you get an error message -- when you try and create this user again. -- Connect as auddemo. connect auddemo/auddemo -- Create the auddemo load directory; this is the directory where the audio -- files are residing. create or replace directory auddir as 'e:\oracle\ord\aud\demo'; grant read on directory auddir to public with grant option; -- Note: If this directory already exists, an error message -- is returned stating the operation will fail; ignore the message.
This script creates the audio table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.
--create_audtable.sql connect auddemo/auddemo; set serveroutput on set echo on drop table audtable; create table audtable (id number, Audio ordsys.ordAudio); -- Insert a row with empty BLOB. insert into audtable values(1,ORDSYS.ORDAudio.init()); -- Insert a row with empty BLOB. insert into audtable values(2,ORDSYS.ORDAudio.init()); commit;
This script performs a SELECT FOR UPDATE operation to load the audio data by first setting the source for loading the audio data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two audio clips to your AUDDIR directory using the names specified in this script, or modify this script to match the file names of your audio clips.
-- importaud.sql set serveroutput on set echo on -- Import two files into the database. DECLARE obj ORDSYS.ORDAUDIO; ctx RAW(4000) := NULL; BEGIN -- This imports the audio file aud1.wav from the auddir directory -- on a local file system (srcType=FILE) and sets the properties. select Audio into obj from audtable where id = 1 for update; obj.setSource('FILE','AUDDIR','aud1.wav'); obj.import(ctx); obj.setProperties(ctx); update audtable set audio = obj where id = 1; commit; -- This imports the audio file aud2.mp3 from the auddir directory -- on a local file system (srcType=FILE) and sets the properties. select Audio into obj from audtable where id = 2 for update; obj.setSource('FILE','AUDDIR','aud2.mp3'); obj.import(ctx); obj.setProperties(ctx); update audtable set audio = obj where id = 2; commit; END; /
This script performs a SELECT operation of the rows of the audio table, then gets the audio characteristics of the BLOB data to check that the BLOB data is in fact loaded.
--chkprop.sql set serveroutput on; --Connect auddemo/auddemo --Query audtable for ORDSYS.ORDAudio. DECLARE audio ORDSYS.ORDAudio; idnum integer; properties_match BOOLEAN; ctx RAW(4000) := NULL; BEGIN FOR I IN 1..2 LOOP SELECT id, audio into idnum, audio from audtable where id=I; dbms_output.put_line('audio id: '|| idnum); properties_match := audio.checkProperties(ctx); IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); END IF; dbms_output.put_line('audio encoding: '|| audio.getEncoding); dbms_output.put_line('audio number of channels: '|| audio.getNumberOfChannels); dbms_output.put_line('audio MIME type: '|| audio.getMimeType); dbms_output.put_line('audio file format: '|| audio.getFormat); dbms_output.put_line('BLOB Length: '|| TO_CHAR(audio.getContentLength(ctx))); dbms_output.put_line('----------------------------------------------'); END loop; END;
Results from running the script chkprop.sql are the following:
SQL> @chkprop.sql audio id: 1 Check Properties Succeeded audio encoding: MS-PCM audio number of channels: 1 audio MIME type: audio/x-wav audio file format: WAVE BLOB Length: 93594 ---------------------------------------------- audio id: 2 Check Properties Succeeded audio encoding: LAYER3 audio number of channels: 1 audio MIME type: audio/mpeg audio file format: MPGA BLOB Length: 51537 ---------------------------------------------- PL/SQL procedure successfully completed.
This script runs each of the previous four scripts in the correct order to automate this entire process.
--setup_audschema.sql -- Create auddemo user, tablespace, and load directory to -- hold the audio files: @create_auduser.sql -- Create Audio table: @create_audtable.sql --Import 2 audio clips and set properties: @importaud.sql --Check the properties of the audio clips: @chkprop.sql --exit;
This script creates a stored procedure that performs a SELECT operation to read a specified amount of audio data from the BLOB, beginning at a particular offset, until all the audio data is read.
--readaudio.sql set serveroutput on set echo on create or replace procedure readaudio as obj ORDSYS.ORDAudio; buffer RAW (32767); numBytes BINARY_INTEGER := 32767; startpos integer := 1; read_cnt integer := 1; ctx RAW(4000) := NULL; BEGIN Select audio into obj from audtable where id = 1; LOOP obj.readFromSource(ctx,startPos,numBytes,buffer);
DBMS_OUTPUT.PUT_LINE('BLOB Length: ' || TO_CHAR(obj.getContentLength(ctx)));
DBMS_OUTPUT.PUT_LINE('start position: '|| startPos); DBMS_OUTPUT.PUT_LINE('doing read: ' || read_cnt); startpos := startpos + numBytes; read_cnt := read_cnt + 1; END LOOP; -- Note: Add your own code here to process the audio data being read; -- this routine just reads the data into the buffer 32767 bytes -- at a time, then reads the next chunk, overwriting the first -- buffer full of data. EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data '); WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION caught'); END; / show errors
To execute the stored procedure, enter the following SQL statements:
SQL> set serveroutput on; SQL> execute readaudio Content Length: 93594 start position: 1 doing read: 1 start position: 32768 doing read: 2 start position: 65535 doing read: 3 ---------------- End of data PL/SQL procedure successfully completed.
interMedia image examples include the following common operations:
Suppose you have an existing table named 'emp' with the following columns:
ename VARCHAR2(50) salary NUMBER job VARCHAR2(50) department INTEGER
To add a new column to the 'emp' table called 'photo' using the ORDImage type, issue the statement in Example 2-14.
Example 2-14 adds a new column of type ORDImage to the emp table.
ALTER TABLE emp ADD (photo ORDSYS.ORDImage);
Suppose you are creating a new table called 'emp' with the following information:
The column for the badge photograph (maybe a thumbnail image cropped and scaled from the large personnel photograph) uses the ORDImage type, and the column 'large_photo' also uses the ORDImage type. The statement in Example 2-15 creates the table and adds ORDImage types to the new table.
CREATE TABLE emp ( ename VARCHAR2(50), salary NUMBER, job VARCHAR2(50), department INTEGER, photo ORDSYS.ORDImage, large_photo ORDSYS.ORDImage);
To insert a row into a table that has storage for image content using the ORDImage type, you must populate the type with an initializer. Note that this is different from NULL. Attempting to use the ORDImage type with a NULL value results in an error.
Example 2-16 describes how to insert rows into the table using the ORDImage type. Assume you have a table 'emp' with the following columns:
ename VARCHAR2(50) salary NUMBER job VARCHAR2(50) department INTEGER photo ORDImage
If you are going to store image data in the database (in a binary large object (BLOB)), you must populate the ORDSource.localData attribute with a value and initialize storage for the localData attribute with an empty_blob( ) constructor. To insert a row into the table with empty data in the 'photo' column, issue the statement in Example 2-16.
Example 2-16 inserts a row into a table with empty data in the ORDImage type column.
INSERT INTO emp VALUES ( 'John Doe', 24000, 'Technical Writer', 123, ORDSYS.ORDImage.init());
Prior to updating a BLOB value, you must lock the row containing the BLOB locator. This is usually done using a SELECT FOR UPDATE statement in SQL and
PL/SQL programs, or using an Oracle Call Interface (OCI) pin or lock function in OCI programs.
Example 2-17 populates a row with ORDImage BLOB data. See Section 2.1.15 for another set of examples for populating rows using BLOB images.
DECLARE -- applicaition variables Image ORDSYS.ORDImage; ctx RAW(4000) := NULL; BEGIN INSERT INTO emp VALUES ( 'John Doe', 24000, 'Technical Writer', 123, ORDSYS.ORDImage.init()); -- Select the newly inserted row for update SELECT photo INTO Image FROM emp WHERE ename = 'John Doe' for UPDATE; -- Can use the getContent method to get the LOB locator. -- Populate the data with DBMS LOB calls or write an OCI program to -- fill in the image BLOB. -- This example imports the image file test.gif from the IMGDIR -- directory on a local file system -- (srcType=FILE) and automatically sets the properties. Image.setSource('FILE','IMGDIR','test.gif'); Image.import(ctx); UPDATE emp SET photo = Image WHERE ename = 'John Doe'; COMMIT; -- Continue processing END;
An UPDATE statement is required to update the property attributes. If you do not use the UPDATE statement now, you can still commit, and the change to the image will be reflected in the BLOB attribute, but not in the properties. See Oracle8i Application Developer's Guide - Large Objects (LOBs) for more information on BLOBs.
To insert a row into a table that has storage for image content in external files using the ORDImage type, you must populate the type with an initializer. Note that this is different from NULL. Attempting to use the ORDImage type with a NULL value results in an error.
Example 2-18 describes how to insert rows into the table using the ORDImage type. Assume you have a table 'emp' with the following columns:
ename VARCHAR2(50) salary NUMBER job VARCHAR2(50) department INTEGER large_photo ORDImage
If you are going to use the ORDImage type column, you must first populate the column with a value. To populate the value of the ORDImage type column with an image stored externally in a file, you must populate the row with a file constructor.
Example 2-18 inserts a row into the table with an image called 'jdoe.gif' from the ORDIMGDIR directory.
INSERT INTO emp VALUES ( 'John Doe', 24000, 'Technical Writer', 123, ORDSYS.ORDImage.init('file','ORDIMGDIR','jdoe.gif'));
For a description of row insertion into an object type, see Chapter 5, and the Oracle8i Application Developer's Guide - Large Objects (LOBs) manual.
The sourceLocation argument 'ORDIMGDIR' is a directory referring to a file system directory. Note that the directory name must be in uppercase. The following sequence creates a directory named ORDIMGDIR:
-- Make a directory referring to a file system directory create directory ORDIMGDIR as '<MYIMAGEDIRECTORY>'; grant read on directory ORDIMGDIR to <user-or-role>;
<MYIMAGEDIRECTORY> is the file system directory, and <user-or-role> is the specific user to whom to grant read access.
Example 2-19 populates the row with ORDImage data stored externally in files.
DECLARE Image ORDSYS.ORDImage; BEGIN INSERT INTO emp VALUES ('John Doe', 24000, 'Technical Writer', 123, ORDSYS.ORDImage.init('file','ORDIMGDIR','jdoe.gif')); -- Select the newly inserted row for update SELECT large_photo INTO Image FROM emp WHERE ename = 'John Doe' FOR UPDATE; -- Set property attributes for the image data Image.setProperties; UPDATE emp SET large_photo = Image WHERE ename = 'John Doe'; COMMIT; -- Continue processing END;
Example 2-20 and Example 2-21 assume you have this table:
create table emp ( ename VARCHAR2(50), salary NUMBER, job VARCHAR2(50), department INTEGER, photo ORDSYS.ORDImage, large_photo ORDSYS.ORDImage);
Example 2-20 queries the emp (employees) table for the name John Doe and the ORDImage data for rows with minimum photo widths (greater than 32 pixels).You must create a table alias (E in this example) when you refer to a type in a SELECT statement.
SELECT ename, E.large_photo.getWidth() FROM emp E WHERE ename = 'John Doe' and E.large_photo.getWidth() > 32;
Example 2-21 queries the emp (employees) table for the name John Doe and the ORDImage data for rows with minimum photo widths (greater than 32 pixels) and a minimum content length (greater than 10000 bytes).
SELECT ename, E.large_photo.getCompressionFormat() FROM emp E WHERE ename = 'John Doe' and E.large_photo.getWidth() > 32 and E.large_photo.getContentLength() > 10000;
To import an image from an external file into the database, use the ORDImage.import method. Example 2-22 imports image data from an external file into the database. The source type, source location, and source name must be set prior to calling the import( ) method.
DECLARE Image ORDSYS.ORDImage; ctx RAW(4000) := NULL; BEGIN SELECT large_photo INTO Image FROM emp WHERE ename = 'John Doe' FOR UPDATE; -- Import the image into the database Image.import(ctx); UPDATE emp SET large_photo = IMAGE WHERE ename = 'John Doe'; COMMIT; END;
To copy an image, use the ORDImage.copy method. Example 2-23 copies image data.
DECLARE Image_1 ORDSYS.ORDImage; Image_2 ORDSYS.ORDImage; BEGIN SELECT photo INTO Image_1 FROM emp WHERE ename = 'John Doe'; SELECT photo INTO Image_2 FROM emp WHERE ename = 'Also John Doe' FOR UPDATE; -- Copy the data from Image_1 to Image_2 Image_1.copy(Image_2); -- Continue processing UPDATE emp SET photo = Image_2 WHERE ename = 'Also John Doe'; COMMIT; END;
To convert the image data into a different format, use the process( ) method.
Example 2-24 converts the image data to the TIFF image file format.
DECLARE Image ORDSYS.ORDImage; BEGIN SELECT photo INTO Image FROM emp WHERE ename = 'John Doe' FOR UPDATE; -- Convert the image to TIFF (in place) Image.process('fileFormat=TIFF'); UPDATE emp SET photo = Image WHERE ename = 'John Doe'; COMMIT; END;
To make a copy of the image and convert it in one step, use the processCopy( ) method.
Example 2-25 creates a thumbnail image, converts the image data to the TIFF image file format, copies it to a BLOB, and leaves the original image intact.
DECLARE Image_1 ORDSYS.ORDImage; Image_2 ORDSYS.ORDImage; BEGIN SELECT photo, large_photo INTO Image_2, Image_1 FROM emp WHERE ename = 'John Doe' FOR UPDATE; -- Convert the image to a TIFF thumbnail image and store the -- result in Image_2 Image_1.processCopy('fileFormat=TIFF fixedScale=32 32', Image_2); -- Continue processing UPDATE emp SET photo = Image_2 WHERE ename = 'John Doe'; COMMIT; END;
Changes made by the processCopy( ) method can be rolled back. This technique may be useful for a temporary format conversion.
You can use the ORDImage type as the basis for a new type of your own creation as shown in Example 2-26.
CREATE TYPE AnnotatedImage AS OBJECT ( image ORDSYS.ORDImage, description VARCHAR2(2000), MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage), MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage), MEMBER PROCEDURE ProcessCopy(command IN VARCHAR2, dest IN OUT AnnotatedImage) ); / CREATE TYPE BODY AnnotatedImage AS MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage) IS BEGIN SELF.image.setProperties; SELF.description := 'This is an example of using Image object as a subtype'; END SetProperties; MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage) IS BEGIN SELF.image.copy(dest.image); dest.description := SELF.description; END Copy; MEMBER PROCEDURE ProcessCopy(command IN VARCHAR2, dest IN OUT AnnotatedImage) IS BEGIN SELF.Image.processCopy(command,dest.image); dest.description := SELF.description; END ProcessCopy; END; /
After creating the new type, you can use it as you would any other type. For example:
create or replace directory TEST_DIR as 'C:\TESTS'; CREATE TABLE my_example(id NUMBER, an_image AnnotatedImage); INSERT INTO my_example VALUES (1, AnnotatedImage( ORDSYS.ORDImage.init('file','ORDIMGDIR','jdoe.gif')); COMMIT; DECLARE myimage AnnotatedImage; BEGIN SELECT an_image INTO myimage FROM my_example; myimage.SetProperties; DBMS_OUTPUT.PUT_LINE('This image has a description of '); DBMS_OUTPUT.PUT_LINE(myimage.description); UPDATE my_example SET an_image = myimage; END; /
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 from data-- of either built-in or user-defined types -- 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 2-27, consider the following relational table (containing no ORDImage objects):
CREATE TABLE flat( id NUMBER, localData BLOB, srcType VARCHAR2(4000), srcLocation VARCHAR2(4000), srcName VARCHAR2(4000), updateTime DATE, local NUMBER, height INTEGER, width INTEGER, contentLength INTEGER, fileFormat VARCHAR2(4000), contentFormat VARCHAR2(4000), compressionFormat VARCHAR2(4000), mimeType VARCHAR2(4000) );
You can create an object view on the relational table shown in Example 2-27 as follows in Example 2-28.
CREATE OR REPLACE VIEW object_images_v AS SELECT id, ORDSYS.ORDImage( ORDSYS.ORDSource( T.localData, T.srcType, T.srcLocation, T.srcName, T.updateTime, T.local), T.height, T.width, T.contentLength, T.fileFormat, T.contentFormat, T.compressionFormat, T.mimeType ) IMAGE FROM flat T;
Object views provide the flexibility of looking at the same relational or object data in more than one way. Thus you can use different in-memory object representations for different applications without changing the way you store the data in the database. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://technet.oracle.com/ as end-to-end scripts that create and populate an image table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.
The following set of scripts:
The fifth script (setup_imgschema.sql) automates this entire process by running each script in the required order. The last script (readimage.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of image data from the BLOB beginning at a particular offset until all the image data is read. To successfully load the image data, you must have an imgdir directory created on your system containing the img71.gif and img50.gif files, which are installed in the <ORACLE_HOME>/ord/img/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_imguser.sql file.
This script creates the imgdemo tablespace with a data file named imgdemo.dbf of 200MB in size, with an initial extent of 64K, a next extent of 128K, and turns on table logging. Next, the imgdemo user is created and given connect, resource, create library, and create directory privileges, followed by creating the image data load directory.
-- create_imguser.sql -- Connect as admin. connect system/<system password>; -- Edit this script and either enter your system password here -- to replace <system password> or comment out this connect -- statement and connect as system before running this script. set serveroutput on set echo on -- Need system manager privileges to delete a user. -- Note: There is no need to delete imgdemo user if you do not delete the -- imgdemo tablespace, therefore comment out the next line. -- drop user imgdemo cascade; -- Need system manager privileges to delete a directory. If threre is -- no need to really delete it, then comment out the next line. -- drop directory imgdir; -- Delete then create the tablespace. -- Note: It is better to not delete and create tablespaces, -- so comment this next line out. The create tablespace statement -- will fail if it already exists. -- drop tablespace imgdemo including contents; -- If you uncomment the preceding line and really want to delete the -- imgdemo tablespace, remember to manually delete the imgdemo.dbf -- file to complete the operation. Otherwise, you cannot create -- the imgdemo tablespace again because the imgdemo.dbf file -- already exists. Therefore, it might be best to create this -- tablespace once and not delete it. -- Create tablespace. create tablespace imgdemo datafile 'imgdemo.dbf' size 200M minimum extent 64K default storage (initial 64K next 128K) logging; -- Create imgdemo user. create user imgdemo identified by imgdemo default tablespace imgdemo temporary tablespace temp; -- Note: If you do not have a temp tablespace already defined, you will -- have to create it first for this script to work. grant connect, resource, create library to imgdemo; grant create any directory to imgdemo; -- Note: If this user already exists, you get an error message when you -- try and create this user again. -- Connect as imgdemo. connect imgdemo/imgdemo -- Create the imgdemo load directory; this is the directory where the image -- files are residing. create or replace directory imgdir as 'e:\oracle\ord\img\demo'; grant read on directory imgdir to public with grant option; -- Note: If this directory already exists, an error message -- is returned stating the operation will fail; ignore the message.
This script creates the image table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.
-- create_imgtable.sql connect imgdemo/imgdemo; set serveroutput on set echo on drop table imgtable; create table imgtable (id number, Image ordsys.ordImage); -- Insert a row with empty BLOB. insert into imgtable values(1,ORDSYS.ORDImage.init()); -- Insert a row with empty BLOB. insert into imgtable values(2,ORDSYS.ORDImage.init()); commit;
This script performs a SELECT FOR UPDATE operation to load the image data by first setting the source for loading the image data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two image files to your IMGDIR directory using the names specified in this script, or modify this script to match the file names of your image files.
--importimg.sql set serveroutput on set echo on -- Import the two files into the database. DECLARE obj ORDSYS.ORDIMAGE; ctx RAW(4000) := NULL; BEGIN -- This imports the image file img71.gif from the IMGDIR directory -- on a local file system (srcType=FILE) and sets the properties. select Image into obj from imgtable where id = 1 for update; obj.setSource('FILE','IMGDIR','img71.gif'); obj.import(ctx); update imgtable set image = obj where id = 1; commit; -- This imports the image file img50.gif from the IMGDIR directory -- on a local file system (srcType=FILE) and sets the properties. select Image into obj from imgtable where id = 2 for update; obj.setSource('FILE','IMGDIR','img50.gif'); obj.import(ctx); update imgtable set image = obj where id = 2; commit; END; /
This script performs a SELECT operation of the rows of the image table, then gets the image characteristics of the BLOB data to check that the BLOB data is in fact loaded.
-- chkprop.sql set serveroutput on; --connect imgdemo/imgdemo --Query imgtable for ORDSYS.ORDImage. DECLARE image ORDSYS.ORDImage; idnum integer; properties_match BOOLEAN; BEGIN FOR I IN 1..2 LOOP SELECT id into idnum from imgtable where id=I; dbms_output.put_line('image id: '|| idnum); SELECT Image into image from imgtable where id=I; properties_match := image.checkProperties; IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); END IF; dbms_output.put_line('image height: '|| image.getHeight); dbms_output.put_line('image width: '|| image.getWidth); dbms_output.put_line('image MIME type: '|| image.getMimeType); dbms_output.put_line('image file format: '|| image.getFileFormat);
dbms_output.put_line('BLOB Length: '|| TO_CHAR(image.getContentLength));
dbms_output.put_line('-------------------------------------------'); END loop; END; /
Results from running the script chkprop.sql are the following:
SQL> @chkprop.sql image id: 1 Check Properties Succeeded image height: 15 image width: 43 image MIME type: image/gif image file format: GIFF BLOB Length: 1124 ------------------------------------------- image id: 2 Check Properties Succeeded image height: 32 image width: 110 image MIME type: image/gif image file format: GIFF BLOB Length: 686 ------------------------------------------- PL/SQL procedure successfully completed.
This script runs each of the previous four scripts in the correct order to automate this entire process.
-- setup_imgschema.sql -- Create imgdemo user, tablespace, and load directory to -- hold image files: @create_imguser.sql -- Create image table: @create_imgtable.sql --Import 2 images and set properties: @importimg.sql --Check the properties of the images: @chkprop.sql --exit;
This script performs a SELECT operation to read a specified amount of image data from the BLOB, beginning at a particular offset until all the image data is read.
-- readimage.sql set serveroutput on set echo on create or replace procedure readimage as -- Note: ORDImage has no readFromSource method like ORDAudio -- and ORDVideo; therefore, you must use the DBMS_LOB package to -- read image data from a BLOB. buffer RAW (32767); src BLOB; obj ORDSYS.ORDImage; amt BINARY_INTEGER := 32767; pos integer := 1; read_cnt integer := 1; BEGIN Select t.image.getcontent into src from imgtable t where t.id = 1; Select image into obj from imgtable t where t.id = 1;
DBMS_OUTPUT.PUT_LINE('Content length is: '|| TO_CHAR(obj.getContentLength));
LOOP DBMS_LOB.READ(src,amt,pos,buffer); DBMS_OUTPUT.PUT_LINE('start position: '|| pos); DBMS_OUTPUT.PUT_LINE('doing read '|| read_cnt); pos := pos + amt; read_cnt := read_cnt + 1; -- Note: Add your own code here to process the image data being read; -- this routine just reads data into the buffer 32767 bytes -- at a time, then reads the next chunk, overwriting the first -- buffer full of data. END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('----------------'); DBMS_OUTPUT.PUT_LINE('End of data '); END; / show errors
To execute the stored procedure, enter the following SQL statements:
SQL> set serveroutput on; SQL> execute readimage(1); Content length is: 1124 start position: 1 doing read 1 ---------------- End of data PL/SQL procedure successfully completed.
The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://technet.oracle.com/ as end-to-end scripts that create and populate an image table from an HTTP data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.
|
Note: Before you run the importimg.sql script described in this section to load image data from an HTTP data source, check to ensure you have already run the create_imguser.sql and create_imgtable.sql scripts described in Section 2.2.14. |
The following set of scripts performs a row insert operation and an import operation, then checks the properties of the loaded images to ensure that the images are really loaded.
This script inserts two rows into the imgtable table, initializing the object column for each row to empty with a locator, and indicating the HTTP source information (source type (HTTP), URL location, and HTTP object name). Within a SELECT FOR UPDATE statement, an import operation loads each image object into the database followed by an UPDATE statement to update the object attributes for each image, and finally a COMMIT statement to commit the transaction.
To successfully run this script, you must modify this script to point to two images located on your own Web site.
--importimghttp.sql -- Import the two HTTP images from a Web site into the database. -- Running this script assumes you have already run the -- create_imguser.sql and create_imgtable.sql scripts. -- Modify the HTTP URL and object name to point to two images -- on your own Web site. set serveroutput on set echo on -- Import two images from HTTP source URLs. connect imgdemo/imgdemo; -- Insert two rows with empty BLOB. insert into imgtable values (7,ORDSYS.ORDImage.init( 'http','your.web.site.com/intermedia','image1.gif')); insert into imgtable values (8,ORDSYS.ORDImage.init( 'http','your.web.site.com/intermedia','image2.gif')); DECLARE obj ORDSYS.ORDIMAGE; ctx RAW(4000) := 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 imgtable where id = 7 for update; obj.import(ctx); update imgtable 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 imgtable where id = 8 for update; obj.import(ctx); update imgtable set image = obj where id = 8; commit; END; /
This script performs a SELECT operation of the rows of the image table, then gets the image characteristics of the BLOB data to check that the BLOB data is in fact loaded.
--chkprop.sql set serveroutput on; --connect imgdemo/imgdemo --Query imgtable for ORDSYS.ORDImage. DECLARE image ORDSYS.ORDImage; idnum integer; properties_match BOOLEAN; BEGIN FOR I IN 7..8 LOOP SELECT id into idnum from imgtable where id=I; dbms_output.put_line('image id: '|| idnum); SELECT Image into image from imgtable where id=I for update; properties_match := image.checkProperties; IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); END IF; dbms_output.put_line('image height: '|| image.getHeight); dbms_output.put_line('image width: '|| image.getWidth); dbms_output.put_line('image MIME type: '|| image.getMimeType); dbms_output.put_line('image file format: '|| image.getFileFormat); dbms_output.put_line('BLOB length: '|| TO_CHAR(image.getContentLength)); dbms_output.put_line('-------------------------------------------'); END loop; END; /
Example 2-29 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 the ",75" specified as the scale factor. This application addresses National Language Support issues.
ALTER SESSION SET NLS_LANGUAGE = FRENCH; ALTER SESSION SET NLS_TERRITORY = FRANCE; DECLARE myimage ORDSYS.ORDImage; mylargeimage ORDSYS.ORDImage; BEGIN SELECT photo, large_photo INTO myimage, mylargeimage FROM emp FOR UPDATE; myimage.setProperties; myimage.ProcessCopy('scale=",75"', mylargeimage); UPDATE emp SET photo = myimage, large_photo = mylargeimage; COMMIT; END; /
interMedia video examples include the following common operations:
The video examples in this section use a table of video clips and a table of videos. For each video clip the following are stored: a videoRef (REF into the video table), clip ID, title, director, category, copyright, producer, awards, time period, rating, duration, cdRef (REF into CdObject for sound tracks), text content (indexed by CONTEXT), cover image (REF into the image table), and video source. For each video the following are stored: an item ID, duration, text content (indexed by CONTEXT), cover image (REF into the image table), and a list of clips on the video.
Reference information on the methods used in these examples is presented in Chapter 6.
Example 2-30 describes how to define a clip object.
CREATE TYPE clipObject as OBJECT ( videoRef REF VideoObject, -- REF into the video table clipId VARCHAR2(20), -- Id inside of the clip table title VARCHAR2(4000), director VARCHAR2(4000), category VARCHAR2(20), copyright VARCHAR2(4000), producer VARCHAR2(4000), awards VARCHAR2(4000), timePeriod VARCHAR2(20), rating VARCHAR2(256), duration INTEGER, cdRef REF CdObject, -- REF into a CdObject(soundtrack) txtcontent CLOB, coverImg REF ORDSYS.ORDImage, -- REF into the ImageTable videoSource ORDSYS.ORDVideo);
Example 2-31 describes how to create an object table named ClipsTable.
CREATE TABLE ClipsTable of clipObject (UNIQUE (clipId), clipId NOT NULL);
Example 2-32 describes how to create a list object containing a list of clips.
CREATE TYPE clipNstType AS TABLE of REF clipObject; CREATE TYPE clipList AS OBJECT (clips clipNstType, MEMBER PROCEDURE addClip(c IN REF clipObject));
Example 2-33 describes how to define the implementation of the clipList object.
CREATE TYPE BODY clipList AS MEMBER PROCEDURE addClip(c IN REF clipObject) IS pos INTEGER := 0; BEGIN IF clips IS NULL THEN clips := clipNstType(NULL); pos := 0; ELSE pos := clips.count; END IF; clips.EXTEND; clips(pos+1) := c; END; END;
This section describes how to create a video object and a video table of video clips that includes, for each video clip, the following information:
Example 2-34 creates a video object named videoObject and a video table named VideoTable that contains the video information.
CREATE TYPE VideoObject as OBJECT ( itemId INTEGER, duration INTEGER, txtcontent CLOB, coverImg REF ORDSYS.ORDImage, clips clipList); CREATE TABLE VideoTable OF VideoObject (UNIQUE(itemId),itemId NOT NULL) NESTED TABLE clips.clips STORE AS clip_store_table;
Example 2-35 describes how to insert a video clip into the ClipsTable table.
-- Insert a Video Clip into the ClipsTable insert into ClipsTable values (NULL, '11', 'Oracle Commercial', 'Larry Ellison', 'commercial', 'Oracle Corporation', '', 'no awards', '90s' 'no rating', 30, NULL, EMPTY_CLOB(), NULL, ORDSYS.ORDVIDEO.init('Oracle Commercial 1 Video Clip'), 'QuickTime File Format', 'video/quicktime', 160, 120, 72, 15, 30, 450, 'Cinepak', 256, 15000));
Example 2-36 describes how to insert a row into the VideoTable table.
-- Insert a row into the VideoTable insert into VideoTable values (11, 30, NULL, NULL, clipList(NULL));
Example 2-37 describes how to load a video into the ClipsTable table. This example requires a VIDDIR directory to be defined; see the comments in the example.
-- Load a Video into a clip -- Create your directory specification below -- CREATE OR REPLACE DIRECTORY VIDDIR AS '/video/'; DECLARE videoObj ORDSYS.ORDVIDEO; ctx RAW(4000) := NULL; BEGIN SELECT C.videoSource INTO videoObj FROM ClipsTable C WHERE C.clipId = '11' FOR UPDATE; videoObj.setDescription('Under Pressure Video Clip'); videoObj.setSource('FILE', 'VIDDIR', 'UnderPressure.mov'); videoObj.import(ctx); videoObj.setProperties(ctx) UPDATE ClipsTable C SET C.videoSource = videoObj WHERE C.clipId = '11'; COMMIT; END; -- Check video insertion DECLARE videoObj ORDSYS.ORDVideo; ctx RAW(4000) := NULL; BEGIN SELECT C.videoSource INTO videoObj FROM ClipsTable C WHERE C.clipId = '11'; dbms_output.put_line('Content Length: ' || videoObj.getContentLength(ctx)); dbms_output.put_line('Content MimeType: ' || videoObj.getMimeType()); END;
Example 2-38 describes how to insert a reference to a clip object into the clips list in the VideoTable table.
-- Insert a reference to a ClipObject into the Clips List in the VideoTable DECLARE clipRef REF ClipObject; clipListInstance clipList; BEGIN SELECT REF(C) into clipRef FROM ClipsTable C where C.clipId = '11'; SELECT V.clips INTO clipListInstance FROM VideoTable V WHERE V.itemId = 11 FOR UPDATE; clipListInstance.addClip(clipRef); UPDATE VideoTable V SET V.clips = clipListInstance WHERE V.itemId = 11; COMMIT; END; -- Check insertion of clip ref DECLARE clip ClipObject; clipRef REF ClipObject; clipListInstance clipList; clipType clipNstType; BEGIN SELECT V.clips INTO clipListInstance FROM VideoTable V WHERE V.itemId = 11; SELECT clipListInstance.clips INTO clipType FROM DUAL; clipRef := clipType(1); SELECT DEREF(clipRef) INTO clip FROM DUAL; dbms_output.put_line('Clip Title: ' || clip.title); END;
Example 2-39 describes how to insert a reference to a video object into the clip.
-- Insert a reference to a video object into the clip DECLARE aVideoRef REF VideoObject; BEGIN -- Make a VideoRef an obj to use for update SELECT Cp.videoRef INTO aVideoRef FROM ClipsTable Cp WHERE Cp.clipId = '11' FOR UPDATE; -- Change its value SELECT REF(V) INTO aVideoRef FROM VideoTable V WHERE V.itemId = 11; -- Update database UPDATE ClipsTable C SET C.videoRef = aVideoRef WHERE C.clipId = '11'; COMMIT; END;
Example 2-40 describes how to retrieve a video clip from the VideoTable table and return it as a BLOB. The program segment performs these operations:
FUNCTION retrieveVideo(clipId IN INTEGER) RETURN BLOB IS obj ORDSYS.ORDVideo; BEGIN -- Select the desired video clip from the ClipTable table. SELECT C.videoSource INTO obj from ClipTable C WHERE C.clipId = clipId; return obj.getContent; END;
This section describes how to extend Oracle interMedia to support a new video data format.
To support a new video data format, implement the required interfaces in the ORDX_<format>_VIDEO package in the ORDPLUGINS schema (where <format> represents the name of the new video data format). See Section 6.4.1 for a complete description of the interfaces for the ORDX_DEFAULT_VIDEO package. Use the package body example in Section 6.4.2 as a template to create the video package body.
Then set the new format parameter in the setFormat call to the appropriate format value to indicate to the video object that package ORDPLUGINS.ORDX_<format> _VIDEO is available as a plug-in.
See Section F.3 for more information on installing your own format plug-in and running the sample scripts provided. See the fplugins.sql and fpluginb.sql files that are installed in the$ORACLE_HOME/ord/vid/demo/ directory. These are demonstration (demo) plug-ins that you can use as a guideline to write any format plug-in that you want to support. See the viddemo.sql file in this same directory to learn how to install your own format plug-in.
This section describes how to extend Oracle interMedia with a new object type.
You can use the ORDVideo type as the basis for a new type of your own creation.
See Example 2-32 and Example 2-33 for brief examples. See Example 2-26 for a more complete example and description.
This section describes how to use video 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 from data -- of either built-in or user-defined types -- 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 2-41, consider the following relational table (containing no ORDVideo objects).
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, width INTEGER, height INTEGER, frameResolution INTEGER, frameRate INTEGER, videoDuration INTEGER, numberOfFrames INTEGER, compressionType VARCHAR2(4000), numberOfColors INTEGER, bitRate INTEGER, videoclip RAW(2000) );
You can create an object view on the relational table shown in Example 2-41as follows in Example 2-42.
create or replace view object_video_v as select id, ordsys.ORDVideo( T.description, T.localData, T.comments, T.format, T.width, T.height, T.frameResolution, T.frameRate, T.videoDuration, T.numberofFrames, T.compressionType, T.numberOfColors, T.bitRate, T.videoclip) VIDEO 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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
The following scripts can be found on the Oracle Technology Network (OTN) Web site: http://technet.oracle.com/ as end-to-end scripts that create and populate a video table from a BFILE data source. You can get to this site by selecting the Oracle interMedia Plugins and Utilities page and from the interMedia page, select Sample Code.
The following set of scripts:
The fifth script (setup_vidschema.sql) automates this entire process by running each script in the required order. The last script (readvideo.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of video data from the BLOB, beginning at a particular offset, until all the video data is read. To successfully load the video data, you must have a viddir directory created on your system containing the vid1.mov and vid2.mov files, which are installed in the <ORACLE_HOME>/ord/vid/demo directory; this directory path and disk drive must be specified in the CREATE DIRECTORY statement in the create_viduser.sql file.
This script creates the viddemo tablespace with a data file named viddemo.dbf of 200MB in size, with an initial extent of 64K, a next extent of 128K, and turns on table logging. Next, the viddemo user is created and given connect, resource, create library, and create directory privileges followed by creating the video data load directory.
-- create_viduser.sql -- Connect as admin. connect system/<system password>; -- Edit this script and either enter your system password here
-- to replace <system password> or comment out this connect
-- statement and connect as system before running this script. set serveroutput on set echo on -- Need system manager privileges to delete a user. -- Note: There is no need to delete viddemo user if you do not -- delete the viddemo tablespace, therefore comment out the next line. -- drop user viddemo cascade; -- Need system manager privileges to delete a directory. If there is no -- need to really delete it, then comment out the next line. -- drop directory viddir; -- Delete then create tablespace. -- Note: It is better to not delete and create tablespaces, -- so comment this next line out. The create tablespace statement -- will fail if it already exists. -- drop tablespace viddemo including contents; -- If you uncomment the previous line and want to delete the -- viddemo tablespace, remember to manually delete the viddemo.dbf -- file to complete the operation. Otherwise, you cannot create -- the viddemo tablespace again because the viddemo.dbf file -- already exists. Therefore, it might be best to create this -- tablespace once and not delete it. -- Create tablespace. create tablespace viddemo datafile 'viddemo.dbf' size 200M minimum extent 64K default storage (initial 64K next 128K) logging; -- Create viddemo user. create user viddemo identified by viddemo default tablespace viddemo temporary tablespace temp; -- Note: If you do not have a temp tablespace already defined, you -- will have to create it first for this script to work. grant connect, resource, create library to viddemo; grant create any directory to viddemo; -- Note: If this user already exists, you get an error message -- when you try and create this user again. -- Connect as viddemo. connect viddemo/viddemo -- Create the viddemo load directory; this is the directory where the video -- files are residing. create or replace directory viddir as 'e:\oracle\ord\vid\demo'; grant read on directory viddir to public with grant option; -- Note: If this directory already exists, an error message -- is returned stating the operation will fail; ignore the message.
This script creates the video table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for populating each row with BLOB data in a subsequent data load operation.
--create_vidtable.sql connect viddemo/viddemo; set serveroutput on set echo on drop table vidtable; create table vidtable (id number, Video ordsys.ordVideo); -- Insert a row with empty BLOB. insert into vidtable values(1,ORDSYS.ORDVideo.init()); -- Insert a row with empty BLOB. insert into vidtable values(2,ORDSYS.ORDVideo.init()); commit;
This script performs a SELECT FOR UPDATE operation to load the video data by first setting the source for loading the video data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two video clips to your VIDDIR directory using the names specified in this script, or modify this script to match the file names of your video clips.
-- importvid.sql set serveroutput on set echo on -- Import the two files into the database. DECLARE obj ORDSYS.ORDVIDEO; ctx RAW(4000) := NULL; BEGIN -- This imports the video file vid1.mov from the VIDDIR directory -- on a local file system (srcType=FILE) and sets the properties. select Video into obj from vidtable where id = 1 for update; obj.setSource('FILE','VIDDIR','vid1.mov'); obj.import(ctx); obj.setProperties(ctx); update vidtable set video = obj where id = 1; commit; -- This imports the video file vid2.mov from the VIDDIR directory -- on a local file system (srcType=FILE) and sets the properties. select Video into obj from vidtable where id = 2 for update; obj.setSource('FILE','VIDDIR','vid2.mov'); obj.import(ctx); obj.setProperties(ctx); update vidtable set video = obj where id = 2; commit; END; /
This script performs a SELECT operation of the rows of the video table, then gets the video characteristics of the BLOB data to check that the BLOB data is in fact loaded.
--chkprop.sql set serveroutput on; --connect viddemo/viddemo --Query vidtable for ORDSYS.ORDVideo. DECLARE video ORDSYS.ORDVideo; idnum integer; properties_match BOOLEAN; ctx RAW(4000) := NULL; width integer; height integer; BEGIN FOR I IN 1..2 LOOP SELECT id, video into idnum, video from vidtable where id=I; dbms_output.put_line('video id: '|| idnum); properties_match := video.checkProperties(ctx); IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); END IF;
--dbms_output.put_line('video frame rate: '|| video.getFrameRate(ctx)); --dbms_output.put_line('video width & height: '|| video.getFrameSize(ctx,width,height); dbms_output.put_line('video MIME type: '|| video.getMimeType); dbms_output.put_line('video file format: '|| video.getFormat(ctx)); dbms_output.put_line('BLOB Length: '|| TO_CHAR(video.getContentLength(ctx))); dbms_output.put_line('----------------------------------------------');
END loop; END; /
Results from running the script chkprop.sql are the following:
SQL> @chkprop.sql video id: 1 Check Properties Succeeded video MIME type: video/quicktime video file format: MOOV BLOB Length: 4958415 ---------------------------------------------- video id: 2 Check Properties Succeeded video MIME type: video/quicktime video file format: MOOV BLOB Length: 2891247 ----------------------------------------------
This script runs each of the previous four scripts in the correct order to automate this entire process.
-- setup_vidschema.sql -- Create viddemo user, tablespace, and load directory to -- hold the video files: @create_viduser.sql -- Create Video table: @create_vidtable.sql --Import 2 video clips and set properties: @importvid.sql --Check the properties of the video clips: @chkprop.sql --exit;
This script creates a stored procedure that performs a SELECT operation to read a specified amount of video data from the BLOB, beginning at a particular offset, until all the video data is read.
-- readvideo.sql set serveroutput on set echo on create or replace procedure readvideo as obj ORDSYS.ORDVideo; buffer RAW (32767); numbytes BINARY_INTEGER := 32767; startpos integer := 1; read_cnt integer := 1; ctx RAW(4000) := NULL; BEGIN Select video into obj from vidtable where id = 1; LOOP obj.readFromSource(ctx,startpos,numbytes,buffer);
DBMS_OUTPUT.PUT_LINE('Content length is: '|| TO_CHAR(obj.getContentLength));
DBMS_OUTPUT.PUT_LINE('start position: '|| startpos); DBMS_OUTPUT.PUT_LINE('doing read '|| read_cnt); startpos := startpos + numbytes; read_cnt := read_cnt + 1; -- Note: Add your own code here to process the video 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 '); DBMS_OUTPUT.PUT_LINE('----------------'); WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION caught'); END; / show errors
To execute the stored procedure, enter the following SQL statements:
SQL> set serveroutput on; SQL> execute readvideo Content Length: 4958415 start position: 1 doing read 1 start position: 32768 doing read 2 start position: 65535 . . . doing read 151 start position: 4947818 doing read 152 ---------------- End of data PL/SQL procedure successfully completed.
This section describes how to extend Oracle interMedia to support a new data source.
To support a new data source, implement the required interfaces in the ORDX_<srcType>_SOURCE package in the ORDPLUGINS schema (where <srcType> represents the name of the new external source type). See Section 7.3.1 and Section 7.3.2 for a complete description of the interfaces for the ORDX_FILE_SOURCE and ORDX_HTTP_SOURCE packages. See Section 7.3.4 for an example of modifying the package body listing that is provided. Then set the source type parameter in the setSourceInformation call to the appropriate source type to indicate to the video object that package ORDPLUGINS.ORDX_<srcType>_SOURCE is available as a plug-in.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|