|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Internal Persistent LOBs, 6 of 43
"Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.
This procedure creates a nested table containing a LOB.
Use the following syntax reference:
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 "Creating a Table Containing One or More LOB Columns"), so here we only describe the syntax for creating the underlying object type:
The example is provided in SQL and applies to all the programmatic environments:
/* 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 the
NESTED TABLE statement at the time that
Multimedia_tab is created.