CREATE a Table with a Nested Table Containing a BFILE

Figure 11-5 Use Case Diagram: CREATE a Table with a Nested Table Containing a BFILE

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 with nested table containing a BFILE.

Usage Notes

As shown in the use case diagram, you must create the object type that contains BFILE attributes before you create a nested table that uses that object type.

Syntax

Use the following syntax references:

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

Scenario

In our example, Multimedia_tab contains a nested table Inseg_ntab that includes type InSeg_typ. This type makes use of two LOB datatypes -- a BFILE for audio recordings of the interviews, and a CLOB for transcripts of the recordings.

We have already described how to create a table with BFILE columns (see "CREATE a Table Containing One or More BFILE Columns"), so here we only describe the SQL syntax for creating the underlying object type.

Examples

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

• SQL: Create a Table with a Nested Table Containing a BFILE

SQL: Create a Table with a Nested Table Containing a BFILE

Because you use SQL DDL directly to create a table, the DBMS_LOB package is not relevant.

CREATE TYPE InSeg_typ AS OBJECT
(  Segment            NUMBER,
   Interview_Date     DATE,
   Interviewer        VARCHAR2(30),
   Interviewee        VARCHAR2(30),
   Recording          BFILE,
   Transcript         CLOB
);

Embedding the nested table is accomplished when the structure of the containing table is defined. In our example, this is done by the following statement when Multimedia_tab is created:

NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;