You can create a table containing one or more LOB columns.

When you use functions, EMPTY_BLOB() and EMPTY_CLOB(), the resulting LOB is initialized, but not populated with data. Also note that LOBs that are empty are not NULL.

Scenario

These examples use the following Sample Schemas:

• Human Resources (HR)

• Order Entry (OE)

• Product Media (PM)

/*  Setup script for creating Print_media, 
    Online_media and associated structures 
*/

DROP USER pm CASCADE;
DROP DIRECTORY ADPHOTO_DIR;
DROP DIRECTORY ADCOMPOSITE_DIR;
DROP DIRECTORY ADGRAPHIC_DIR;
DROP INDEX onlinemedia CASCADE CONSTRAINTS;
DROP INDEX printmedia CASCADE CONSTRAINTS;
DROP TABLE online_media CASCADE CONSTRAINTS;
DROP TABLE print_media CASCADE CONSTRAINTS;
DROP TYPE textdoc_typ;
DROP TYPE textdoc_tab;
DROP TYPE adheader_typ;
DROP TABLE adheader_typ;
CREATE USER pm identified by password;
GRANT CONNECT, RESOURCE to pm;

CREATE DIRECTORY ADPHOTO_DIR AS '/tmp/';
CREATE DIRECTORY ADCOMPOSITE_DIR AS '/tmp/';
CREATE DIRECTORY ADGRAPHIC_DIR AS '/tmp/';
CREATE DIRECTORY media_dir AS '/tmp/';
GRANT READ ON DIRECTORY ADPHOTO_DIR to pm;
GRANT READ ON DIRECTORY ADCOMPOSITE_DIR to pm;
GRANT READ ON DIRECTORY ADGRAPHIC_DIR to pm;
GRANT READ ON DIRECTORY media_dir to pm;

CONNECT pm/password  (or &pass);
COMMIT;

CREATE TABLE a_table (blob_col BLOB); 

CREATE TYPE adheader_typ AS OBJECT ( 
   header_name    VARCHAR2(256),  
   creation_date  DATE,  
   header_text    VARCHAR(1024), 
   logo           BLOB );

CREATE TYPE textdoc_typ AS OBJECT ( 
   document_typ   VARCHAR2(32), 
   formatted_doc  BLOB);

CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;

CREATE TABLE adheader_tab of adheader_typ (
Ad_finaltext DEFAULT EMPTY_CLOB(), CONSTRAINT 
Take CHECK (Take IS NOT NULL),  DEFAULT NULL);

CREATE TABLE online_media
( product_id  NUMBER(6),
product_photo ORDSYS.ORDImage,
product_photo_signature ORDSYS.ORDImageSignature,
product_thumbnail ORDSYS.ORDImage, 
product_video ORDSYS.ORDVideo,
product_audio ORDSYS.ORDAudio, 
product_text CLOB,
product_testimonials ORDSYS.ORDDoc);

CREATE UNIQUE INDEX onlinemedia_pk
  ON online_media (product_id);
  
ALTER TABLE online_media
ADD (CONSTRAINT onlinemedia_pk
PRIMARY KEY (product_id), CONSTRAINT loc_c_id_fk
FOREIGN KEY (product_id)  REFERENCES oe.product_information(product_id)
);

CREATE TABLE print_media
(product_id NUMBER(6), 
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB, 
ad_finaltext CLOB,
ad_fktextn NCLOB,
ad_testdocs_ntab textdoc_tab,
ad_photo BLOB, 
ad_graphic BFILE,
ad_header adheader_typ,
press_release LONG) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

CREATE UNIQUE INDEX printmedia_pk
  ON print_media (product_id, ad_id);

ALTER TABLE print_media
ADD (CONSTRAINT printmedia_pk
PRIMARY KEY (product_id, ad_id), 
CONSTRAINT printmedia_fk FOREIGN KEY (product_id) 
REFERENCES oe.product_information(product_id)
);

You can create a nested table containing a LOB.

You must create the object type that contains the LOB attributes before you create a nested table based on that object type. In the example that follows, table Print_media contains nested table ad_textdoc_ntab that has type textdoc_tab. This type uses two LOB data types:

• BFILE - an advertisement graphic

• CLOB - an advertisement transcript

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 when the Print_media table is created as shown in the following example:

/* Create type textdoc_typ as the base type 
   for the nested table textdoc_ntab, 
   where textdoc_ntab contains a LOB: 
*/
CREATE TYPE textdoc_typ AS OBJECT
( 
   document_typ    VARCHAR2(32),  
   formatted_doc   BLOB 
);
/

/* The type has been created. Now you need a */
/* nested table of that type to embed in */
/* table Print_media, so: */
CREATE TYPE textdoc_ntab AS TABLE of textdoc_typ; 
/

CREATE TABLE textdoc_ntable ( 
   id NUMBER, 
   ntab_col textdoc_ntab) 
NESTED TABLE ntab_col STORE AS textdoc_nestedtab;

DROP TYPE textdoc_typ force;
DROP TYPE textdoc_ntab;
DROP TABLE textdoc_ntable;

You can insert a row containing a LOB as SELECT.

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.

For example, assuming Print_media and Online_media have identical schemas. The statement creates a new LOB locator in table Print_media. It also copies the LOB data from Online_media to the location pointed to by the new LOB locator inserted in table Print_media.

The following code fragment is based on the fact that the table Online_media is of the same type as Print_media referenced by the ad_textdocs_ntab column of table Print_media. It inserts values into the library table, and then inserts this same data into Print_media by means of a SELECT.

/* Store records in the archive table Online_media: */
INSERT INTO Online_media  
   VALUES (3060, NULL, NULL, NULL, NULL, 
           'some text about this CRT Monitor', NULL);

/* Insert values into Print_media by selecting from Online_media: */
INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
   (SELECT product_id, 11001, product_text
          FROM Online_media WHERE product_id = 3060);

You can insert a LOB value using EMPTY_CLOB() or EMPTY_BLOB().

Usage Notes

Here are guidelines for inserting LOBs:

Before Inserting Make the LOB Column Non-Null

Before you write data to a persistent 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 value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column 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 4000 bytes in size. For example:

INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
     VALUES (1, 1, 'This is a One Line Advertisement');

Note that you can also perform this initialization during the CREATE TABLE operation.

These functions are special functions in Oracle SQL, and are not part of the DBMS_LOB package.

/* In the new row of table Print_media, 
   the columns ad_sourcetext and ad_fltextn are initialized using EMPTY_CLOB(), 
   the columns ad_composite and ad_photo are initialized using EMPTY_BLOB(),
   the column formatted-doc in the nested table is initialized using
   EMPTY_BLOB(),
   the column logo in the column object is initialized using EMPTY_BLOB(): */   
INSERT INTO Print_media 
   VALUES (3060,11001, EMPTY_BLOB(), EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_CLOB(), 
   textdoc_tab(textdoc_typ ('HTML', EMPTY_BLOB())), EMPTY_BLOB(), NULL,
   adheader_typ('any header name', <any date>, 'ad header text goes here',
   EMPTY_BLOB()),
 'Press release goes here');

• SQL: Oracle Database SQL Language Reference, the INSERT statement

• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions"

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — INSERT.

• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide INSERT

• Java (JDBC):Oracle Database JDBC Developer's Guide "Working With LOBs" — Creating and Populating a BLOB or CLOB Colum

You can UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB().

Preconditions

Before you write data to a persistent 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 value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column 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 4000 bytes in size. For example:

UPDATE Print_media 
          SET ad_sourcetext = 'This is a One Line Story'
          WHERE product_id = 2268;

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

The following example shows a series of updates using the EMPTY_CLOB operation to different data types.

UPDATE Print_media SET ad_sourcetext = EMPTY_CLOB() 
       WHERE product_id = 3060 AND ad_id = 11001;

UPDATE Print_media SET ad_fltextn = EMPTY_CLOB() 
       WHERE product_id = 3060 AND ad_id = 11001;

UPDATE Print_media SET ad_photo = EMPTY_BLOB()
       WHERE product_id = 3060 AND ad_id = 11001;

You can use the SQL UPDATE AS SELECT statement to update a row containing a LOB column by selecting a LOB from another table.

To use this technique, you must update by means of a reference. For example, the following code updates data from online_media:

Rem Updating a row by selecting a LOB from another table (persistent LOBs)

UPDATE Print_media SET ad_sourcetext = 
   (SELECT * product_text FROM online_media WHERE product_id = 3060);
    WHERE product_id = 3060 AND ad_id = 11001;