Creating a Table Containing One or More LOB Columns

Figure 10-4 Use Case Diagram: Creating a Table Containing one or More LOB Columns

Text description of the illustration adl10p41.gif

See: Use Case Model: Internal Persistent LOBs Operations, for all Internal Persistent LOB operations.

Purpose

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

Usage Notes

When you use functions, EMPTY_BLOB () and EMPTY_CLOB(), the resulting LOB is initialized, but not populated with data. LOBs that are empty are not null, and vice versa. This is discussed in more detail in "Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()".

• For information about creating nested tables that have one or more columns of LOB datatype, see "Creating a Nested Table Containing a LOB".

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

  See also: Oracle9i 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: Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- CREATE TABLE

Scenario

Figure 10-5 illustrates table Multimedia_tab. This table is the heart of the multimedia schema used in most of this manual's examples. The column types in this table make it possible to collect the different kinds multimedia elements used in the composition of video clips.

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

Text description of the illustration adl81034.gif

Examples

How to create a table containing a LOB column is illustrated with the following example, 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 in this manual 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 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;