Creating a Table Containing an Object Type with a LOB Attribute

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

Text description of the illustration adl10p43.gif

See: Figure 10-1, "Use Case Model Diagram: Internal Persistent LOBs (part 1 of 2)", for all Internal Persistent LOB operations.:

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

Scenario

As shown in the diagram Figure 10-6, 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.

The example application illustrates 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 table, VoiceOver_tab.Table VoiceOver_tab is based on 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 type, Map_typ. This type uses a BLOB for storing maps as drawings.

  See Also: Appendix B, "The Multimedia Schema Used for Examples in This Manual" for a description of the multimedia application and table Multimedia_tab.

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

Text description of the illustration adl81036.gif Examples

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

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

SQL: Creating 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 10-8 MAP_TYP As An Example of Creating a Type Containing a LOB

Text description of the illustration adl81035.gif
/* 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: Oracle9i 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.