CREATE a Nested Table Containing a LOB

Figure 9-9 Use Case Diagram: Create a Nested Table Containing a LOB

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

Purpose

This procedure creates a nested table containing a LOB.

Usage Notes

Not applicable.

Syntax

Use the following syntax reference:

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

Scenario

Create the object type that contains the LOB attributes before you create a nested table based on that object type. In our example, table Multimedia_tab contains nested table Inseg_ntab that has type InSeg_typ. This type uses two LOB datatypes:

• BFILE for audio recordings of the interviews

• CLOB should the user wish to make transcripts of the recordings

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

Figure 9-10 INTERVIEWSEGMENTS_NTAB as an Example of Creating a Nested Table Containing a LOB

Examples

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

• SQL: Create a Nested Table Containing a LOB

SQL: Create a Nested Table Containing a LOB

/* Create a type InSeg_typ as the base type for the nested table containing 
   a LOB: */
DROP TYPE InSeg_typ force; 
DROP TYPE InSeg_tab; 
DROP TABLE InSeg_table; 
CREATE TYPE InSeg_typ AS OBJECT ( 
   Segment         NUMBER, 
   Interview_Date  DATE, 
   Interviewer     VARCHAR2(30),  
   Interviewee     VARCHAR2(30),  
   Recording       BFILE, 
   Transcript      CLOB 
);

/* Type created, but need a nested table of that type to embed in
 multi_media_tab; so: */
CREATE TYPE InSeg_tab AS TABLE of Inseg_typ; 
CREATE TABLE InSeg_table ( 
   id number, 
   InSeg_ntab Inseg_tab) 
NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;
 

The actual embedding of the nested table is accomplished when the structure of the containing table is defined. In our example, this is effected by means of the NESTED TABLE statement at the time that Multimedia_tab is created.