8.2.1.1 Internal and external LOBs

There are two categories of LOBs, depending upon their location with respect to the physical database:

• Internal LOBs (of datatypes BLOB, CLOB, and NCLOB) are stored in the database and can participate in transactions. Changes to internal LOB values can be rolled back or committed. A cursor can select an internal LOB FOR UPDATE. Uncommitted changes to an internal LOB are not seen by a separate session.

• External LOBs (of datatype BFILE) are stored outside of the database in operating system files and cannot participate in transactions. Instead, the underlying operating system provides the data integrity. Access to external LOBs is read-only.

8.2.1.2 The BFILE datatype

The BFILE datatype is used to store large binary objects (up to four gigabytes) in files outside of the database.

A BFILE could be a PL/SQL variable,

DECLARE    my_book_file BFILE;

or a column in a table,

CREATE TABLE my_book_files    ( file_descr VARCHAR2(40),  book_file BFILE  );

or an attribute in a TYPE,

CREATE OR REPLACE PACKAGE blobby IS    TYPE adpage_rectype IS RECORD (       customer_id NUMBER,       persuasive_picture BFILE       ),

or a parameter/RETURN type in a PL/SQL procedure or function:

CREATE OR REPLACE FUNCTION blended_image (    old_image IN BFILE, new_image IN BFILE)    RETURN BFILE IS BEGIN    ... END;

In each case, the BFILE value points to an operating-system file residing on the server and outside of the database.

8.2.1.3 The BLOB datatype

The BLOB datatype is used to store large binary objects within the database; the objects can be up to four gigabytes and may consist of unstructured raw data. A BLOB could be a PL/SQL variable,

DECLARE    corporate_logo BLOB;

or a column in a table,

CREATE TABLE my_book_diagrams    ( chapter_descr VARCHAR2(40),      diagram_no INTEGER,      diagram BLOB );

or an attribute in a TYPE,

CREATE OR REPLACE PACKAGE chapter IS    TYPE diagram_rectype IS RECORD (       chapter_num NUMBER,       diagram BLOB       ),

or a parameter/RETURN type in a PL/SQL procedure or function:

CREATE OR REPLACE PROCEDURE show_blob (    blob_in IN BLOB) IS BEGIN    ... END;

A BLOB is an internal LOB, and therefore can participate in database transactions. In other words, changes made to a BLOB can be rolled back or committed along with other changes during a transaction. BLOBs cannot span transactions.

8.2.1.4 The CLOB datatype

The CLOB datatype is used to store large blocks within the database. The blocks can be up to four gigabytes of single-byte character data. A CLOB could be a PL/SQL variable,

DECLARE    gettysburg_address_text   CLOB;

or a column in a table,

CREATE TABLE my_book_text    ( chapter_descr VARCHAR2(40), chapter_text CLOB );

or an attribute in a TYPE,

CREATE OR REPLACE PACKAGE speechifying IS    TYPE poll_results_rectype IS RECORD (       speech_num NUMBER,       speech_txt CLOB       ),

or a parameter/RETURN type in a PL/SQL procedure or function:

CREATE OR REPLACE PROCEDURE edit_speech  (   text_in IN CLOB) IS BEGIN    ... END;

A CLOB is an internal LOB and therefore can participate in database transactions. In other words, changes made to a CLOB can be rolled back or committed along with other changes during a transaction. CLOBs cannot span transactions, and do not support variable-width character sets.

8.2.1.5 The NCLOB datatype

The NCLOB datatype is used to store large blocks within the database. The blocks can be up to four gigabytes of single-byte or multiple-byte fixed-width character data. A NCLOB could be a PL/SQL variable,

DECLARE    gettysburg_address_in_japanese NCLOB;

or a column in a table:

CREATE TABLE my_book_in_japanese    ( chapter_no INTEGER,      chapter_in_japanese NCLOB );

You may also use the NCLOB datatype in the parameter of a PL/SQL program or the RETURN type for a function. However, you may not use NCLOB as the datatype of an attribute in a TYPE statement.

A NCLOB is an internal LOB, and therefore can participate in database transactions. In other words, changes made to a NCLOB can be rolled back or committed along with other changes during a transaction. NCLOBs cannot span transactions, and do not support variable-width character sets.

8.2.1.6 The LOB locator

The value held in a LOB column or variable is not the actual binary data, but a "locator" or pointer to the physical location of the large object.

For internal LOBs, since one LOB value can be up to four gigabytes in size, the binary data will be stored "out of line" (i.e., physically separate) from the other column values of a row (unless otherwise specified; see the next paragraph). This allows the physical size of an individual row to be minimized for improved performance (the LOB column contains only a pointer to the large object). Operations involving multiple rows, such as full table scans, can be performed more efficiently.

A user can specify that the LOB value be stored in the row itself. This is usually done when working with small LOB values. This approach decreases the time needed to obtain the LOB value. However, the LOB data is migrated out of the row when it gets too big.

For external LOBs, the BFILE value represents a filename and an operating system directory, which is also a pointer to the location of the large object.

8.2.2.1 The DIRECTORY object

A BFILE locator consists of a directory alias and a filename. The directory alias is an Oracle8 database object that allows references to operating system directories without hard-coding directory pathnames. This statement creates a directory:

CREATE DIRECTORY IMAGES AS 'c:\images';

To refer to the c:\images directory within SQL, you can use the IMAGES alias, rather than hard-coding the actual directory pathname.

To create a directory, you need the CREATE DIRECTORY or CREATE ANY DIRECTORY privilege. To reference a directory, you must be granted the READ privilege, as in:

GRANT READ ON DIRECTORY IMAGES TO SCOTT;

8.2.2.2 Populating a BFILE locator

The Oracle8 built-in function BFILENAME can be used to populate a BFILE locator. BFILENAME is passed a directory alias and filename and returns a locator to the file. In the following block, the BFILE variable corporate_logo is assigned a locator for the file named ourlogo.bmp located in the IMAGES directory:

DECLARE    corporate_logo     BFILE; BEGIN    corporate_logo := BFILENAME ( 'IMAGES', 'ourlogo.bmp' ); END;

The following statements populate the my_book_files table; each row is associated with a file in the BOOK_TEXT directory:

INSERT INTO my_book_files ( file_descr, book_file )      VALUES ( 'Chapter 1', BFILENAME('BOOK_TEXT', 'chapter01.txt') ); UPDATE  my_book_files    SET  book_file = BFILENAME( 'BOOK_TEXT', 'chapter02rev.txt' )  WHERE  file_descr = 'Chapter 2';

Once a BFILE column or variable is associated with a physical file, read operations on the BFILE can be performed using the DBMS_LOB package. Remember that access to physical files via BFILEs is read-only, and that the BFILE value is a pointer. The contents of the file remain outside of the database, but on the same server on which the database resides.

8.2.3.1 Retaining the LOB locator

The following statement populates the my_book_text table, which contains CLOB column chapter_text:

INSERT INTO my_book_text ( chapter_descr, chapter_text )      VALUES ( 'Chapter 1', 'It was a dark and stormy night.' );

Programs within the DBMS_LOB package require a LOB locator to be passed as input. If you want to insert the preceding row and then call a DBMS_LOB program using the row's CLOB value, you must retain the LOB locator created by your INSERT statement. You could do this as in the following block, which inserts a row, selects the inserted LOB locator, and then calls the DBMS_LOB.GETLENGTH program to get the size of the CLOB chapter_text column. Note that the GETLENGTH program expects a LOB locator.

DECLARE    chapter_loc          CLOB;    chapter_length       INTEGER; BEGIN    INSERT INTO my_book_text ( chapter_descr, chapter_text )         VALUES ( 'Chapter 1', 'It was a dark and stormy night.' );    SELECT  chapter_text      INTO  chapter_loc      FROM  my_book_text     WHERE  chapter_descr = 'Chapter 1';    chapter_length := DBMS_LOB.GETLENGTH( chapter_loc );    DBMS_OUTPUT.PUT_LINE( 'Length of Chapter 1: ' || chapter_length ); END; /

This is the output of the script:

Length of Chapter 1: 31

8.2.3.2 The RETURNING clause

You can avoid the second trip to the database (i.e., the SELECT of the LOB locator after the INSERT)by using a RETURNING clause in the INSERT statement. Using this feature, perform the INSERT operation and the LOB locator value for the new row in a single operation.

DECLARE    chapter_loc        CLOB;    chapter_length     INTEGER; BEGIN     INSERT INTO my_book_text ( chapter_descr, chapter_text )         VALUES ( 'Chapter 1', 'It was a dark and stormy night.' )      RETURNING chapter_text INTO chapter_loc;     chapter_length := DBMS_LOB.GETLENGTH( chapter_loc );     DBMS_OUTPUT.PUT_LINE( 'Length of Chapter 1: ' || chapter_length );  END; /

This is the output of the script:

Length of Chapter 1: 31

The RETURNING clause can be used in both INSERT and UPDATE statements.

8.2.3.3 NULL LOB locators can be a problem

Programs in the DBMS_LOB package expect to be passed a LOB locator that is not NULL. For example, the GETLENGTH program raises an exception when passed a LOB locator that is NULL.

DECLARE    chapter_loc        CLOB;    chapter_length     INTEGER;  BEGIN       UPDATE  my_book_text          SET  chapter_text = NULL        WHERE  chapter_descr = 'Chapter 1'    RETURNING  chapter_text INTO chapter_loc;     chapter_length := DBMS_LOB.GETLENGTH( chapter_loc );     DBMS_OUTPUT.PUT_LINE( 'Length of Chapter 1: ' || chapter_length );  EXCEPTION    WHEN OTHERS    THEN       DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);  END; /

This is the output of the script:

OTHERS Exception ORA-00600: internal error code, arguments: ...

When a BLOB, CLOB, or NCLOB column is set to NULL, both the LOB binary data and its LOB locator are NULL; this NULL LOB locator should not be passed to a program in the DBMS_LOB package.

8.2.3.4 NULL versus "empty" LOB locators

Oracle8 provides the built-in functions EMPTY_BLOB and EMPTY_CLOB to set BLOB, CLOB, and NCLOB columns to "empty." For example:

INSERT INTO my_book_text ( chapter_descr, chapter_text )      VALUES ( 'Table of Contents', EMPTY_CLOB() );

The LOB data is set to NULL. However, the associated LOB locator is assigned a valid locator value, which points to the NULL data. This LOB locator can then be passed to DBMS_LOB programs.

DECLARE    chapter_loc      CLOB;    chapter_length   INTEGER;  BEGIN    INSERT INTO my_book_text (chapter_descr, chapter_text)         VALUES ( 'Table of Contents', EMPTY_CLOB() )      RETURNING chapter_text INTO chapter_loc;     chapter_length := DBMS_LOB.GETLENGTH( chapter_loc );     DBMS_OUTPUT.PUT_LINE       ('Length of Table of Contents: ' || chapter_length);  EXCEPTION    WHEN OTHERS    THEN       DBMS_OUTPUT.PUT_LINE( 'OTHERS Exception ' || sqlerrm);  END; /

This is the output of the script:

Length of Table of Contents: 0

Note that EMPTY_CLOB can be used to populate both CLOB and NCLOB columns. EMPTY_BLOB and EMPTY_CLOB can be called with or without empty parentheses.

NOTE: Do not populate BLOB, CLOB, or NCLOB columns with NULL values. Instead, use the EMPTY_BLOB or EMPTY_CLOB functions, which will populate the columns with a valid LOB locator and set the associated data to NULL.