Creating a Table with LOB Columns

2.1 Creating a Table with LOB Columns

You can use the CREATE TABLE statement or an ALTER TABLE ADD column statement to create a new LOB column. This section introduces basic DDL operations on LOBs to get you started quickly.

Following is an example of creating a table with columns of various LOB types, including LOBs in Object Types and nested tables:

CREATE USER pm identified by password;
GRANT CONNECT, RESOURCE to pm IDENTIFIED BY pm;
CONNECT pm/pm

-- Create an object type with a LOB
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 a nested table type of Object type containing a LOB
CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;

-- Create a table of Object type, and specify a default value for LOB column
CREATE TABLE adheader_tab of adheader_typ (
    logo DEFAULT EMPTY_BLOB(),
    CONSTRAINT header_name CHECK (header_name IS NOT NULL),
     header_text DEFAULT NULL);
-- Create a table with columns of different LOB types, 
-- and of object type with LOBs, and nested table containing LOB
CREATE TABLE print_media
(product_id NUMBER(6), 
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB, 
ad_finaltext CLOB,
ad_fltextn 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);

Figure 2-1 print_media table

You can also perform advanced DDL operations, like the following, on LOBs:

Specify LOB storage parameters: You can override the default LOB storage settings by specifying parameters like SECUREFILE/BASICFILE, TABLESPACE where the LOB data will be stored, ENABLE/DISABLE STORAGE IN ROW, RETENTION, caching, logging, etc. You can also specify SecureFile specific parameters like COMPRESSION, DEDUPLICATION and ENCRYPTION.
Alter an existing LOB column: You can use the ALTER TABLE MODIFY LOB syntax to change any LOB storage parameters that don't require LOB data movement and the ALTER TABLE MOVE LOB syntax to change any LOB storage parameters that require LOB data movement.
Create indexes on LOB columns: You can build a functional or a domain index on a LOB column. You cannot build a B-tree or bitmap index on a LOB column.
Partition a table containing LOB columns: All partitioning schemes supported by Oracle are fully supported on LOBs.
Use LOBs in Index-Organized tables.