CREATE a Table Containing an Object Type with a LOB Attribute

Figure 9-6 Use Case Diagram: Create a Table Containing an Object Type with a LOB Attribute

See: "Use Case Model: Internal Persistent LOBs Basic Operations", or all basic operations having to do with Internal Persistent LOBs.:

Purpose

This procedure describes how to create a table containing an object type with an LOB attribute.

Usage Notes

Not applicable.

Syntax

See the following specific reference for a detailed syntax description:

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

Scenario

As shown in the diagram, you must create the object type that contains LOB attributes before you can proceed to create a table that makes use of that object type.

Our example application includes two ways in which object types can contain LOBs:

• Voiced_typ datatype uses CLOB for script and BFILE for audio: Table Multimedia_tab contains column Voiced_ref that references row objects in the table VoiceOver_tab which is based on the type Voiced_typ. This type contains two kinds of LOBs -- a CLOB to store the script that's read by the actor, and a BFILE to hold the audio recording.

• Map_obj column uses BLOB for storing maps: Table Multimedia_tab contains column Map_obj that contains column objects of the type Map_typ. This type utilizes the BLOB datatype for storing maps in the form of drawings.

  See Also: Chapter 8, "Sample Application" for a description of the multimedia application and table Multimedia_tab.

Figure 9-7 VOICED_TYP As An Example of Creating a Type Containing a LOB

Examples

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

• SQL: Create a Table Containing an Object Type with a LOB Attribute

SQL: Create a Table Containing an Object Type with a LOB Attribute

/* Create type Voiced_typ as a basis for tables that can contain recordings of 
   voice-over readings using SQL DDL: */
CREATE TYPE Voiced_typ AS OBJECT ( 
   Originator      VARCHAR2(30), 
   Script          CLOB, 
   Actor           VARCHAR2(30),  
   Take            NUMBER,
   Recording       BFILE 
);

/* Create table Voiceover_tab Using SQL DDL: */
CREATE TABLE Voiceover_tab of Voiced_typ (
Script DEFAULT EMPTY_CLOB(), 
   CONSTRAINT Take CHECK (Take IS NOT NULL),
   Recording DEFAULT NULL
);

Figure 9-8 MAP_TYP As An Example of Creating a Type Containing a LOB

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

/* Create Type Map_typ using SQL DDL as a basis for the table that will contain 
   the column object: */
CREATE TYPE Map_typ AS OBJECT ( 
   Region          VARCHAR2(30), 
   NW              NUMBER,
   NE              NUMBER,
   SW              NUMBER,
   SE              NUMBER,
   Drawing         BLOB,
   Aerial          BFILE
);

/* Create support table MapLib_tab as an archive of maps using SQL DDL: */
CREATE TABLE MapLib_tab of Map_typ;

See Also: Oracle8i SQL Reference for a complete specification of the syntax for using LOBs in DDL commands CREATE TYPE and ALTER TYPE with BLOB, CLOB, and BFILE attributes.

Note:: NCLOBs cannot be attributes of an object type.