INSERT a Row by Selecting a LOB From Another Table

Figure 9-13 Use Case Diagram: Insert a Row by Selecting a LOB From Another Table

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

Purpose

This procedure describes how to insert a row containing a LOB as SELECT.

Usage Notes

Note: Internal LOB types -- BLOB, CLOB, and NCLOB -- use copy semantics, as opposed to reference semantics that apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another in the same table or a different table, the actual LOB value is copied, not just the LOB locator.

For example, assuming Voiceover_tab and VoiceoverLib_tab have identical schemas, the statement creates a new LOB locator in the table Voiceover_tab, and copies the LOB data from VoiceoverLib_tab to the location pointed to by a new LOB locator which is inserted in table Voiceover_tab.

Syntax

Use the following syntax reference:

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

Scenario

With regard to LOBs, one of the advantages of utilizing an object-relational approach is that you can define a type as a common template for related tables. For instance, it makes sense that both the tables that store archival material and the working tables that use those libraries share a common structure.

The following code fragment is based on the fact that a library table VoiceoverLib_tab is of the same type (Voiced_typ) as Voiceover_tab referenced by the Voiced_ref column of the Multimedia_tab table. It inserts values into the library table, and then inserts this same data into Multimedia_tab by means of a SELECT.

See Also: Chapter 8, "Sample Application" for a description of the multimedia application and table Multimedia_tab.

Examples

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

• SQL: Insert a Row by Selecting a LOB from Another Table

SQL: Insert a Row by Selecting a LOB from Another Table

/* Store records in the archive table VoiceoverLib_tab: */
INSERT INTO VoiceoverLib_tab  
   VALUES ('George Washington', EMPTY_CLOB(), 'Robert Redford', 1, NULL);

/* Insert values into Voiceover_tab by selecting from VoiceoverLib_tab: */
INSERT INTO Voiceover_tab 
   (SELECT * from VoiceoverLib_tab 
       WHERE Take = 1);