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:

• lobdemo.sql

• adloci.sql.

See Also: For further LOB examples: Oracle interMedia User's Guide and Reference. Oracle interMedia Audio, Image, and Video Java Classes User's Guide and Reference Oracle interMedia Locator User's Guide and Reference Using Oracle9i interMedia with the Web Oracle9i interMedia Text Migration Oracle9i Text Reference