Creating a Table Containing One or More BFILE Columns

Figure 12-3 Use Case Diagram: Creating a Table Containing One or More BFILE Columns

Text description of the illustration adl12b23.gif

See Also: "Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

Purpose

This procedure describes how to create a table containing one or more BFILE columns.

Usage Notes

Not applicable.

Syntax

Use the following syntax references:

• SQL (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- CREATE TABLE

Scenario

The heart of our hypothetical application is table Multimedia_tab. The varied types that make up the columns of this table make it possible to collect together the many different kinds multimedia elements used in the composition of clips.

Examples

The following example is provided in SQL and applies to all programmatic environments:

• SQL: Creating a Table Containing One or More BFILE Columns

SQL: Creating a Table Containing One or More BFILE Columns

You may need to set up the following data structures for certain examples in this chapter to work:

CONNECT system/manager;
DROP USER samp CASCADE;
DROP DIRECTORY AUDIO_DIR;
DROP DIRECTORY FRAME_DIR;
DROP DIRECTORY PHOTO_DIR;

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;

CREATE TABLE VoiceoverLib_tab of Voiced_typ
( Script DEFAULT EMPTY_CLOB(),
   CONSTRAINT TakeLib CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);
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 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);

Because you 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;