In-Depth

An Introduction to Working with Large Objects in Oracle SQL

This overview discusses Oracle's large object data types and explains how to use those designed to store character data.

• 02/23/2010

Oracle Database provides large object (LOB) data types that can be used to store images, sound, video, PDFs, and large amounts of text. In this excerpt from chapter 18 of Murach’s Oracle SQL and PL/SQL (murach.com), you’ll get an overview of the LOB data types and see how to use those that are designed to store character data.

The LOB types

Figure 1 presents the four LOB types that were introduced with Oracle Database 8. The CLOB (Character Large Object) and NCLOB (National Character Large Object) types can store character data. These data types are commonly used to store large text and XML files. The primary difference between these types is that the CLOB type uses 1 byte per character to store characters in the ASCII character set while the NCLOB type uses 2 or 3 bytes per character to store characters in the Unicode character set.

The BLOB (Binary Large Object) type can store data in binary format. It can be used to store binary files such as PDF files, and it can be used to store image, sound, and video files.

The BFILE (Binary File) type stores a pointer to a binary file that’s stored outside of the database. These binary files can be stored anywhere that’s accessible through the host computer’s file system.

This figure also presents the old RAW, LONG, and LONG RAW data types for storing large objects that were used prior to Oracle Database 8. These data types are provided primarily for backward compatibility. Fortunately, it’s easy to migrate to the new types if you want to do that.

The data types for large objects that were introduced in Oracle Database 8

Type	Description
CLOB	Character large object. Stores up to 8 terabytes of character data inside the database.
NCLOB	National character large object. Stores up to 8 terabytes of national character data inside the database.
BLOB	Binary large object. Stores up to 8 terabytes of binary data inside the database.
BFILE	Binary file. Stores a pointer to a large binary file stored outside the database in the file system of the host computer.

The older data types for large objects

Type	Description
RAW(size)	Stores up to 2000 bytes of binary data that is not intended to be converted by Oracle when moving data between different systems.
LONG	Stores up to 2 gigabytes of character data.
LONG RAW	Stores up to 2 gigabytes of row binary data that is not intended to be converted.

Some of the APIs for working with large objects

• Java
• .NET Framework
• C++
• COBOL
• PL/SQL

Description

• The large object (LOB) data types can store large amounts of binary and character data.

• The CLOB, NCLOB, and BLOB types are sometimes referred to as internal LOB types, and the BFILE type is sometimes referred to as an external LOB type.

• Oracle provides APIs for many programming languages that you can use to work with LOB types.

APIs for working with LOBs

In most cases, LOBs are used by client-side applications. For example, a client-side application may want to get an image from a database and display it. Conversely, a client-side application may want to upload an image from a user’s hard drive and store it in the database.

To allow communication between a client-side application and the database, Oracle provides APIs for working with LOBs. Figure 1 lists some of the most commonly used APIs provided by Oracle. For example, it’s common to use Java or a .NET language such as Visual Basic or C# to work with LOBs. In addition, Oracle provides an API for using PL/SQL to work with LOBs. Since PL/SQL doesn’t provide any capabilities for viewing images or playing music, it’s mainly used to perform server-side processing that can be called by a client-side application via a stored procedure or function.

How to work with CLOBs

If you want to work with LOBs, you need to store them in the database, and you need to retrieve them from the database. As you might expect, you can use the basic SQL statements to accomplish these tasks.

Figure 2 shows how to work with CLOBs. To start, the first example shows how to create a table named Product_Reviews that stores data of the CLOB type. Here, the first column in the table contains a NUMBER value that uniquely identifies each row. Then, the second column in the table contains a CLOB value.

The second example shows a script that inserts three rows into the table. Here, the first INSERT statement uses the TO_CLOB function to convert a string literal to a CLOB value. This accomplishes two tasks: (1) it initializes a LOB locator that points to a CLOB value and (2) it inserts the characters in the string literal into the CLOB value. In contrast, the second INSERT statement uses the EMPTY_CLOB function to initialize a LOB locator that points to a CLOB value, but it doesn’t fill the CLOB value with any data. Then, the third INSERT statement stores a NULL value, which doesn’t initialize a LOB locator. Finally, the COMMIT statement commits the changes to the database.

The third example shows a SELECT statement that retrieves data from the table. To start, this statement retrieves both of the columns of the table. Then, it creates a third column by using the LENGTH function to return the number of characters in the CLOB value that’s stored in the database.

The result set that’s returned by the SELECT statement shows how SQL Developer displays a CLOB value. To start, it displays “(CLOB)” to indicate that the column contains a LOB locator that has been initialized. Then, it displays any characters that have been stored in the CLOB. However, if the LOB locator hasn’t been initialized, the result set displays a NULL value.

Of course, if you’re using another tool such as SQL*Plus, the display may be different. For example, by default, SQL*Plus only displays the first 80 characters of a CLOB value.

Figure 2: How to work with CLOBs

A statement that creates a table that can store CLOBs

CREATE TABLE product_reviews
(
  product_id      NUMBER    PRIMARY KEY,
  product_review  CLOB
);

A script that inserts three rows into the table

INSERT INTO product_reviews VALUES
(1, TO_CLOB('Imagine this is a long string of characters.'));

INSERT INTO product_reviews VALUES
(2, EMPTY_CLOB());

INSERT INTO product_reviews VALUES
(3, NULL);

COMMIT;

A statement that displays the values in the table

SELECT product_id, product_review,
         LENGTH(product_review) AS clob_length
FROM product_reviews;

The result set is:

Result Set

Description

• When you create a table, you can use the CLOB or NCLOB data types just as you would use any other data type.

• You can use the TO_CLOB and TO_NCLOB functions to convert a string to the CLOB or NCLOB types.

• You can use the EMPTY_CLOB function to return a LOB locator for an empty CLOB or NCLOB.

• You can use the LENGTH function to return the number of characters that are stored in a CLOB or NCLOB.

How to Work with NCLOBs

Once you understand how to work with CLOBs, you should be able to apply most of those skills to NCLOBs. For example, to convert Figure 2 so it works with NCLOBs, you just specify the NCLOB type in the table definition and use the TO_NCLOB function in the first INSERT statement. The rest of the code works the same.

This article is an excerpt from chapter 18 of Murach’s Oracle SQL and PL/SQL. The rest of the chapter shows you how to use SQL to handle BLOBs and BFILEs; how to migrate to the new LOB data types; how to use a programming language (specifically, Java) to work with large objects; and how to use Oracle’s PL/SQL to work with large objects.

Joel Murach is the author of Murach’s Oracle SQL and PL/SQL, and he is the co-author of Murach’s SQL Server 2008 for Developers, as well as books on Java and C#. When he’s not working on programming or books, he can be found strumming a guitar in the cafes and clubs of the San Francisco Bay area.