Creating a Table of an Object Type with a BFILE Attribute

Figure 12-4 Use Case Diagram: Creating a Table Containing a BFILE

Text description of the illustration adl12b25.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 of an object type with a BFILE attribute.

Usage Notes

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

Syntax

Use the following syntax references:

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

Note that NCLOBs cannot be attributes of an object type.

Scenario

Our example application contains examples of two different ways in which object types can contain BFILEs:

• Multimedia_tab contains a 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.

• Multimedia_tab contains column Map_obj that contains column objects of the type Map_typ. This type utilizes the BFILE datatype for storing aerial pictures of the region.

Examples

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

• SQL: Creating a Table of an Object Type with a BFILE Attribute

SQL: Creating a Table of an Object Type with a BFILE 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
);

/* 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 Map_tab of MapLib_typ;