Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

B
The Multimedia Schema


Note:

The Multimedia Sample Schema has been deprecated and is not supplied with Oracle9i. This appendix is included to provide context for examples in this guide that have not yet been migrated to the new Product Media (PM) Sample Schema which replaces the Multimedia schema for most LOB examples.

For more information about sample schemas, see "Examples in This Guide".


This appendix describes the following topics:

A Typical Multimedia Application

Oracle9i supports LOBs, large objects which can hold up to 4 gigabytes of binary or character data. What does this mean to you, the application developer?

Consider the following multimedia scenario.

Multimedia data is used in an increasing variety of media channels -- film, television, Web pages, and CD-ROM being the most prevalent. The media experiences having to do with these different channels vary in many respects (interactivity, physical environment, the structure of information, to name a few). Despite these differences, there is often considerable similarity in the multimedia authoring process, especially with regard to assembling content.

For instance, a television station that creates complex documentaries, an advertising agency that produces advertisements for television, and a software production house that specializes in interactive games for the web could all make good use of a database management system for collecting and organizing the multimedia data. Presumably, they each have sophisticated editing software for composing these elements into their specific products, but the complexity of such projects creates a need for a pre-composition application for organizing the multimedia elements into appropriate groups.

Taking our lead from movie-making, our hypothetical application for collecting content uses the clip as its basic unit of organization. Any clip is able to include one or more of the following media types:

Since this is a pre-editing application, the precise relationship of elements within a clip (such as the synchronization of voice-over audio with a photograph) and between clips (such as the sequence of clips) is not defined.

The application should allow multiple editors working simultaneously to store, retrieve and manipulate the different kinds of multimedia data. We assume that some material is gathered from in-house databases. At the same time, it should also be possible to purchase and download data from professional services.

This Scenario is Only An Example

Our mission in this appendix is not to create this real-life application, but to describe some typical scenarios you may need to know about working with LOBs. Consequently, we only implement the application sufficiently to demonstrate the technology. For example, we deal with only a limited number of multimedia types. We make no attempt to create the client-side applications for manipulating LOBs.

Also we do not deal with deployment issues such as the fact that you should implement disk striping of LOB files, if possible, for best performance.

The Multimedia Schema

Figure B-1 illustrates multimedia schema used for the examples in this manual.

The Multimedia schema is comprised of the following components:

Figure B-1 The Multimedia Schema

Text description of adlob033.gif follows
Text description of the illustration adlob033.gif


Table Multimedia_Tab

Figure B-1, "The Multimedia Schema", shows table Multimedia_tab's structure. Table Multimedia_tab columns are described in the following:

Script for Creating the Multimedia Schema

Here is the script used to create the Multimedia schema:

CONNECT system/manager;
DROP USER samp CASCADE;
DROP DIRECTORY AUDIO_DIR;
DROP DIRECTORY FRAME_DIR;
DROP DIRECTORY PHOTO_DIR;
DROP TYPE InSeg_typ force;
DROP TYPE InSeg_tab;
DROP TABLE InSeg_table;
CREATE USER samp identified by samp;
GRANT CONNECT, RESOURCE to samp;
CREATE DIRECTORY AUDIO_DIR AS '/tmp/';
CREATE DIRECTORY FRAME_DIR AS '/tmp/';
CREATE DIRECTORY PHOTO_DIR AS '/tmp/';
GRANT READ ON DIRECTORY AUDIO_DIR to samp;
GRANT READ ON DIRECTORY FRAME_DIR to samp;
GRANT READ ON DIRECTORY PHOTO_DIR to samp;
CONNECT samp/samp
CREATE TABLE a_table (blob_col BLOB); 
CREATE TYPE Voiced_typ AS OBJECT ( 
   Originator      VARCHAR2(30), 
   Script          CLOB, 
   Actor           VARCHAR2(30),  
   Take            NUMBER,
   Recording       BFILE 
);

CREATE TABLE VoiceoverLib_tab of Voiced_typ (
Script DEFAULT EMPTY_CLOB(),
   CONSTRAINT TakeLib CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);

CREATE TYPE InSeg_typ AS OBJECT ( 
   Segment         NUMBER, 
   Interview_Date  DATE, 
   Interviewer     VARCHAR2(30),  
   Interviewee     VARCHAR2(30),  
   Recording       BFILE, 
   Transcript      CLOB 
);

CREATE TYPE InSeg_tab AS TABLE of InSeg_typ;
CREATE TYPE Map_typ AS OBJECT (
   Region          VARCHAR2(30),
   NW              NUMBER,
   NE              NUMBER,
   SW              NUMBER,
   SE              NUMBER,
   Drawing         BLOB,
   Aerial          BFILE
);
CREATE TABLE Map_Libtab of Map_typ;
CREATE TABLE Voiceover_tab of Voiced_typ (
Script DEFAULT EMPTY_CLOB(),
   CONSTRAINT Take CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);

Since one can use SQL DDL directly to create a table containing one or more LOB columns, it is not necessary to use the DBMS_LOB package.
CREATE TABLE Multimedia_tab ( 
   Clip_ID         NUMBER NOT NULL, 
   Story           CLOB default EMPTY_CLOB(), 
   FLSub           NCLOB default EMPTY_CLOB(), 
   Photo           BFILE default NULL, 
   Frame           BLOB default EMPTY_BLOB(), 
   Sound           BLOB default EMPTY_BLOB(), 
   Voiced_ref      REF Voiced_typ,
   InSeg_ntab      InSeg_tab, 
   Music           BFILE default NULL, 
   Map_obj         Map_typ 
 ) NESTED TABLE    InSeg_ntab STORE AS InSeg_nestedtab; 

See Also:

Chapter 10, "Internal Persistent LOBs", "Creating a Table Containing One or More LOB Columns" .

This script is also located in $HOME Oracle9i "demo" directory in the following files:

See Also:

For further LOB examples:


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback