CREATE a Table Containing One or More LOB Columns

Figure 9-4 Use Case Diagram: CREATE a Table Containing a LOB Column

See: "Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs.

Purpose

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

Usage Notes

• The result of using the functions EMPTY_BLOB () and EMPTY_CLOB() means that the LOB is initialized, but not populated with data. LOBs that are empty are not null, and vice versa. This topic is discussed in more detail in "INSERT a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()".

• For information about creating nested tables that have ore or more columns of LOB datatype see "CREATE a Nested Table Containing a LOB"

• The creation of an object column containing one or more LOBs is discussed under the heading "CREATE a Table Containing an Object Type with a LOB Attribute".

  See also: Oracle8i SQL Reference for a complete specification of syntax for using LOBs in CREATE TABLE and ALTER TABLE with: - BLOB, CLOB, NCLOB and BFILE columns - EMPTY_BLOB and EMPTY_CLOB functions - LOB storage clause for internal LOB columns, and LOB attributes of embedded objects

Syntax

Use the following syntax reference:

• SQL: Oracle8i SQL Reference , "Chapter 7, SQL Statements" -- CREATE TABLE

Scenario

The heart of our hypothetical application is the table Multimedia_tab. The varied types which 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.

Figure 9-5 MULTIMEDIA_TAB as an Example of Creating a Table Containing a LOB Column

Examples

Examples that illustrate how to create a table containing a LOB column are provided in SQL:

• SQL: Create a Table Containing One or More LOB Columns

SQL: Create a Table Containing One or More LOB Columns

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

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;