Inserting a Row by Selecting a LOB From Another Table

Figure 10-13 Use Case Diagram: Inserting a Row by Selecting a LOB From Another Table

Text description of the illustration adl10p12.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

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 table Voiceover_tab. It also copies the LOB data from VoiceoverLib_tab to the location pointed to by the new LOB locator inserted in table Voiceover_tab.

Syntax

Use the following syntax reference:

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

Scenario

For LOBs, one of the advantages of using 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 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 table Multimedia_tab. It inserts values into the library table, and then inserts this same data into Multimedia_tab by means of a SELECT.

See Also: Appendix B, "The Multimedia Schema Used for Examples in This Manual", 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: Inserting a Row by Selecting a LOB from Another Table

SQL: Inserting 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);