INSERT a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()

Figure 9-12 Use Case Diagram: INSERT a Row Using EMPTY_CLOB() or EMPTY_BLOB()

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 LOB value using EMPTY_CLOB() or EMPTY_BLOB().

Usage Notes

Making a LOB Column Non-Null

Before you write data to an internal LOB, make the LOB column non-null; that is, the LOB column must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB().

You can also initialize a LOB column with a character or raw string less than 4,000 bytes in size. For example:

INSERT INTO Multimedia_tab (clip_id, story)
          VALUES (1,'This is a One Line Story');

You can perform this initialization during CREATE TABLE (see "CREATE a Table Containing One or More LOB Columns") or, as in this case, by means of an INSERT.

Syntax

See Chapter 3, "LOB Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

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

• C (OCI): There is no applicable syntax reference for this use case.

• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.

• C/C++ (Pro*C/C++): There is no applicable syntax reference for this use case.

• Visual Basic (OO4O): There is no applicable syntax reference for this use case.

• Java (JDBC): Oracle8i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle8i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

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

Examples

Examples are provided in the following programmatic environments:

• SQL: Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

• C (OCI): No example is provided with this release.

• C/C++ (Pro*C): No example is provided with this release.

• COBOL (Pro*COBOL): No example is provided with this release.

• Visual Basic (OO4O): No example is provided with this release.

• Java (JDBC): Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

SQL: Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

These functions are available as special functions in Oracle8 SQL DML, and are not part of the DBMS_LOB package.

/* In the new row of table Multimedia_tab, 
   the columns STORY and FLSUB are initialized using EMPTY_CLOB(), 
   the columns FRAME and SOUND are initialized using EMPTY_BLOB(),
   the column TRANSSCRIPT in the nested table is initialized using EMPTY_CLOB(),
   the column DRAWING in the column object is initialized using EMPTY_BLOB(): */   
INSERT INTO Multimedia_tab 
   VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), 
   NULL, InSeg_tab(InSeg_typ(1, NULL, 'Ted Koppell', 'Jimmy Carter', NULL, 
   EMPTY_CLOB())), NULL, Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(),
   NULL));

/* In the new row of table Voiceover_tab, the column SCRIPT is initialized using
   EMPTY_CLOB(): */ 
INSERT INTO Voiceover_tab 
    VALUES ('Abraham Lincoln', EMPTY_CLOB(), 'James Earl Jones', 1, NULL);

Java (JDBC): Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

Statement stmt = conn.createStatement() ;  
   try {  
stmt.execute ("insert into lobtable values (empty_blob())");  
   }  
   catch{ ...}  
    

  
  Util.trySQL, Util.doSQl below does the stmt.execute() ;   
    
  Util.trySQL (conn, "drop table lobtran_table");  
  Util.doSQL (conn, "create table lobtran_table (b1 blob, b2 blob, c1 clob,
                 c2 clob, f1 bfile, f2 bfile)");  
  
  Util.doSQL (conn, "insert into lobtran_table values
                  ('010101010101010101010101010101', empty_blob(),
                   'onetwothreefour', empty_clob(), 
                    bfilename ('TEST_DIR', 'tkpjobLOB11.dat'),
                    bfilename ('TEST_DIR', 'tkpjobLOB12.dat'))");