Oracle LOB Functions FAQ, Guide, and Examples

In this article, we’ll look at the different LOB functions available in Oracle, learn what they do, and see some examples.

Purpose of the Oracle LOB Functions

There are a few LOB-related functions in Oracle.

What are they all for?

TO_CLOB

The TO_CLOB function converts a value in an NCLOB column (the national character set) to a CLOB (in the database character set).

TO_LOB

The purpose of the TO_LOB function is to convert LONG or LONG RAW values to LOB values.

TO_NCLOB

The purpose of the TO_NCLOB function is to convert a value from a LOB to an NCLOB value.

You may not use it very often unless you’re working with LOB data. I haven’t used it a lot myself, but it’s good to know that it’s there if I need to.

EMPTY_CLOB

The EMPTY_CLOB function is used to initialise a CLOB column to EMPTY. It can be used in several places:

• In an INSERT statement
• In an UPDATE statement
• Initialising a LOB variable in PL/SQL

It’s similar to the EMPTY_BLOB function, but this function is used on CLOB columns.

EMPTY_BLOB

The EMPTY_BLOB function is used to initialise a LOB column to EMPTY. It can be used in several places:

• In an INSERT statement
• In an UPDATE statement
• Initialising a LOB variable in PL/SQL

Oracle TO_CLOB Function Syntax and Parameters

The syntax of the TO_CLOB function is quite simple:

1TO_CLOB ( input_string )

The parameters of the TO_CLOB function are:

• input_string (mandatory): This is the string to convert to a CLOB data type. It can be a CHAR, VARCHAR2, NCHAR, NVARHCAR2, CLOB, or NCLOB data type.

The return type of this function is a CLOB value.

Oracle TO_LOB Function Syntax and Parameters

The syntax of the TO_LOB function is:

1TO_LOB ( long_value )

The parameters of the TO_LOB function are:

• long_value (mandatory): This is the value to convert to LOB. It must be a LONG or LONG RAW type.

Oracle TO_NCLOB Function Syntax and Parameters

The syntax for the TO_NCLOB function is quite simple.

1TO_NCLOB ( lob_value )

The parameters of the TO_NCLOB function are:

• lob_value (mandatory): This is the value to convert into an NCLOB data type.

The lob_value parameter can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Oracle EMPTY_CLOB Function Syntax and Parameters

The syntax of the EMPTY_CLOB column is simple:

1EMPTY_CLOB ()

There are no parameters for the EMPTY_CLOB function.

Oracle EMPTY_BLOB Function Syntax and Parameters

The syntax of the EMPTY_BLOB column is quite simple:

1EMPTY_BLOB ()

There are no parameters for the EMPTY_BLOB function.

When Can I Use the TO_LOB Function?

You can use a TO_LOB function:

• Within a SELECT subquery inside an INSERT statement
• In an UPDATE statement
• In a CREATE TABLE AS SELECT statement

For example:

1INSERT INTO table (col1, col2) (
2SELECT col1, TO_LOB(original_col2) FROM another_table
3);

If you want to convert LONG values, the column in your table should be a CLOB datatype. Or, if you want to convert LONG RAW values, the column in your table should be a BLOB data type.

Why Would You Set a CLOB or BLOB to Empty?

Setting a CLOB or BLOB to empty means you have initialised it but not given it a value.

I haven’t used this function very often, but it’s good to know it exists if you need it.

Examples of the TO_CLOB Function

Here are some examples of the TO_CLOB function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example performs a conversion on a VARCHAR2 value.

1SELECT first_name, TO_CLOB(first_name) AS clob_value
2FROM student
3WHERE student_id = 1;

Result:

Example 2

This example converts an NCLOB data type to a CLOB data type.

 1CREATE TABLE datatype_test (
 2nclob_column NCLOB
 3);
 4
 5Table created.
 6
 7INSERT INTO datatype_test (nclob_column)
 8VALUES ('This is ä tëst');
 9
101 row inserted.
11
12SELECT nclob_column,
13TO_CLOB(nclob_column) AS clobvalue
14FROM datatype_test;

Result:

Examples of the TO_LOB Function

First, let’s create a table that has some LONG values.

1CREATE TABLE first_table (
2  id NUMBER,
3  longval LONG
4);
5
6INSERT INTO first_table (id, longval) VALUES (1, 120034);

Now, let’s create a table that has a CLOB data type.

1CREATE TABLE second_table (
2  id NUMBER,
3  clobval CLOB
4);

Now, let’s insert into this table.

1INSERT INTO second_table (id, clobval) (
2SELECT id, TO_LOB(longval) FROM first_table
3);

Now, let’s select from the second_table to see what was inserted.

1SELECT id, clobval
2FROM second_table;

Result:

As you can see, the LONG value was converted to a CLOB data type using the Oracle TO_LOB function. Well, you may not be able to see it’s a CLOB data type, but that’s what the TO_LOB function does.

Examples of the TO_NCLOB Function

Here are some examples of the TO_NCLOB function.

Example 1

This example performs a conversion on a VARCHAR2 value.

1SELECT first_name,
2TO_NCLOB(first_name) AS nclob_value
3FROM student
4WHERE student_id = 1;

Result:

Example 2

This example converts a CLOB data type to an NCLOB data type.

 1CREATE TABLE datatype_test (
 2  clob_column CLOB
 3);
 4
 5Table created.
 6
 7INSERT INTO datatype_test (clob_column)
 8VALUES ('This is ä tëst');
 9
101 row inserted.
11
12SELECT clob_column,
13TO_NCLOB(clob_column) AS nclobvalue
14FROM datatype_test;

Result:

Examples of the EMPTY_CLOB Function

Here are some examples of the EMPTY_CLOB function. I find that examples are the best way for me to learn about code, even with the explanation above.

Let’s start by creating a table with a CLOB value.

1CREATE TABLE clobtest (
2  clobval CLOB
3);
4
5INSERT INTO clobtest (clobval) VALUES ('qwerty');

Now, let’s see what’s in the table.

1SELECT clobval
2FROM clobtest;

Result:

We can now see our CLOB value.

Now, let’s set it to EMPTY_CLOB, and select the record again.

1UPDATE clobtest
2SET clobval = EMPTY_CLOB();
3
4SELECT clobval
5FROM clobtest;

Result:

You can see that it is now empty. This is different from NULL.

Let’s set it to NULL and see what is displayed.

1UPDATE clobtest
2SET clobval = NULL;
3
4SELECT clobval
5FROM clobtest;

Result:

As you can see, the value is now NULL, which is different from the result after setting the value to empty.

Examples of the EMPTY_BLOB Function

Here is an example of the EMPTY_BLOB function.

Let’s start by creating a table with a BLOB value.

1CREATE TABLE blobtest (
2  blobval BLOB
3);
4
5INSERT INTO blobtest (blobval) VALUES ('abcde');

Now, let’s see what’s in the table.

1SELECT blobval
2FROM blobtest;

Result:

It doesn’t show us any readable value. This is because we need to convert it to be able to read it.

1SELECT DBMS_LOB.SUBSTR(blobval)
2FROM blobtest;

Result:

We can now see our BLOB value.

Now, let’s set it to EMPTY_BLOB, and select the record again.

1UPDATE blobtest
2SET blobval = EMPTY_BLOB();
3
4SELECT DBMS_LOB.SUBSTR(blobval)
5FROM blobtest;

Result:

You can see that it is now empty.

Similar Functions

Some functions which are similar to these CLOB functions are:

• TO_CHAR: This function converts a value to a CHAR data type.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!