Release 8.1.5
A67299-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i interMedia Audio, Image, and Video User's Guide and Reference Release 8.1.5 A67299-01 |
|
This chapter provides examples that show common operations with Oracle8i 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 3.
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(NULL, ORDSYS.ORDSource(EMPTY_BLOB(),NULL,NULL,NULL,NULL,NULL), NULL, NULL, EMPTY_CLOB(), NULL, NULL, NULL, NULL, NULL, NULL)); -- 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 add comments about a song.
-- Adding comments about a song DECLARE audioObj ORDSYS.ORDAUDIO; comments VARCHAR2(256); BEGIN SELECT S.audioSource into audioObj FROM SongsTable S WHERE S.songId = '00' FOR UPDATE; comments := 'I like this song when I am Under Pressure'; audioObj.writeToComments(1, 41, comments); UPDATE SongsTable S SET S.audioSource = audioObj WHERE S.songId = '00'; COMMIT; END;
Example 2-12 describes how to retrieve audio data from a song in a CD.
FUNCTION retrieveAudio(cdId 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;
Example 2-13 describes how to display all comments for a song in a CD.
-- Displaying comments about a song DECLARE audioObj ORDSYS.ORDAUDIO; comments VARCHAR2(256); BEGIN SELECT S.audioSource into audioObj FROM SongsTable S WHERE S.songId = '00'; comments := audioObj.readFromComments(1, 41); dbms_output.put_line('Comments: ' || comments); 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 3.3.1 for a complete description of the interfaces for the ORDX_DEFAULT_AUDIO package. Use the package body example in Section 3.3.6 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 E.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 Oracle8i 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 a brief example. 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.
Consider the following non-object audio table:
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 flat table as follows:
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. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
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(ORDSYS.ORDSource(empty_blob(),NULL,NULL,NULL,SYSDATE,1), NULL,NULL,NULL,NULL,NULL,NULL,NULL));
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.
DECLARE -- applicaition variables Image ORDSYS.ORDImage; BEGIN INSERT INTO emp VALUES ( 'John Doe', 24000, 'Technical Writer', 123, ORDSYS.ORDImage(ORDSYS.ORDSource(empty_blob(), NULL,NULL,NULL,SYSDATE,1), NULL,NULL,NULL,NULL,NULL,NULL,NULL)); -- select the newly inserted row for update SELECT photo INTO Image FROM emp WHERE ename = 'John Doe' for UPDATE; --BEGIN -- 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. --END; -- set property attributes for the image data Image.setProperties; UPDATE emp SET photo = Image WHERE ename = 'John Doe'; -- continue processing END;
An UPDATE statement is required to update the property attributes. If you do not use the setProperties( ) method and 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(ORDSYS.ORDSource(empty_blob(),'file','ORDIMGDIR', 'jdoe.gif',SYSDATE,0), NULL,NULL,NULL,NULL,NULL,NULL,NULL));
For a description of row insertion into an object type, see Chapter 4 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:
connect internal -- make a directory referring to a file system directory create directory ORDIMGDIR as '<MYIMAGEDIRECTORY>'; grant read on directory ORDIMGDIR to <user-or-role>;
where <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(ORDSYS.ORDSource(empty_blob(),'file','ORDIMGDIR', 'jdoe.gif',SYSDATE,0), NULL,NULL,NULL,NULL,NULL,NULL,NULL)); -- 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'; -- 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 a row that has ORDImage data.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 a row that has ORDImage data.
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. The program in 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'; END;
To copy an image, use the ORDImage.copy method. The program in 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'; END;
To convert the image data into a different format, use the process( ) method.
The program in Example 2-24 converts the image data to the TIFF 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'; END;
To make a copy of the image and convert it into one step, use the processCopy( ) method.
The program in 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 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'; 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 shown in Example 2-26 as the basis for a new type of your own creation.
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( ORDSYS.ORDSource(empty_blob(),'file','ORDIMGDIR', 'jdoe.gif',SYSDATE,0), NULL,NULL,NULL,NULL,NULL,NULL,NULL), NULL)); 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.
Consider the following non-object image table:
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) );
Example 2-27 creates an object view on the flat table.
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. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
Example 2-28 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; 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, casting, 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 5 and Appendix F.
Example 2-29 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), casting ORDSYS.ORDAnnotations, -- Pairs of characters/actors 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);
See Appendix F for a description of the ORDAnnotations type and its methods.
Example 2-30 describes how to create an object table named clipsTable.
CREATE TABLE ClipsTable of clipObject (UNIQUE (clipId), clipId NOT NULL) NESTED TABLE casting.annotations STORE AS annot_store_table2;
Example 2-31 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-32 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-33 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-34 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', ORDSYS.ORDAnnotations(NULL), 'commercial', 'Oracle Corporation', '', 'no awards', '90s' 'no rating', 30, NULL, EMPTY_CLOB(), NULL, ORDSYS.ORDVIDEO('Oracle Commercial 1 Video Clip', ORDSYS.ORDSource(EMPTY_BLOB(),NULL,NULL,NULL,NULL,NULL), 'QuickTime File Format', 'video/quicktime', EMPTY_CLOB(), 160, 120, 72, 15, 30, 450, 'Cinepak', 256, 15000));
See Appendix F for a description of the ORDAnnotations object type and methods.
Example 2-35 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-36 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.setMimeType('video/quicktime'); videoObj.setFormat('QuickTime File Format'); videoObj.setFrameSize(160, 120); videoObj.setFrameResolution(72); videoObj.setFrameRate(15); videoObj.setVideoDuration(30); videoObj.setNumberOfFrames(450); videoObj.setCompressionType('Cinepak'); videoObj.setNumberOfColors(256); videoObj.setSource('FILE', 'VIDDIR', 'UnderPressure.mov'); videoObj.import(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-37 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-38 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-39 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 Oracle8i 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 5.3.1 for a complete description of the interfaces for the ORDX_DEFAULT_VIDEO package. Use the package body example in Section 5.3.6 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 E.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 Oracle8i interMedia with a new object type.
You can use the ORDSource, ORDVideo, and ORDAnnotations types as the basis for a new type of your own creation.
See Example 2-31 and Example 2-32 for a brief example. 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.
Consider the following non-object video table:
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 flat table as follows:
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. See the Oracle8i Concepts manual for more information on defining, using, and updating object views.
This section describes how to extend Oracle8i 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 6.3.1 and Section 6.3.2 for a complete description of the interfaces for the ORDX_FILE_SOURCE and ORDX_HTTP_SOURCE packages. See Section 6.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.
