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:

TO_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:

TO_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.

TO_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:

EMPTY_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:

EMPTY_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:

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

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.

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

Result:

FIRST_NAME	CLOB_VALUE
John	John

Example 2

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

CREATE TABLE datatype_test (
nclob_column NCLOB
);

Table created.

INSERT INTO datatype_test (nclob_column)
VALUES ('This is ä tëst');

1 row inserted.

SELECT nclob_column,
TO_CLOB(nclob_column) AS clobvalue
FROM datatype_test;

Result:

NCLOB_COLUMN	CLOB_VALUE
This is ä tëst	This is ä tëst

 

Examples of the TO_LOB Function

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

CREATE TABLE first_table (
  id NUMBER,
  longval LONG
);

INSERT INTO first_table (id, longval) VALUES (1, 120034);

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

CREATE TABLE second_table (
  id NUMBER,
  clobval CLOB
);

Now, let’s insert into this table.

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

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

SELECT id, clobval
FROM second_table;

Result:

ID	CLOBVAL
1	120034

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.

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

Result:

FIRST_NAME	NCLOB_VALUE
John	John

 

Example 2

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

CREATE TABLE datatype_test (
  clob_column CLOB
);

Table created.

INSERT INTO datatype_test (clob_column)
VALUES ('This is ä tëst');

1 row inserted.

SELECT clob_column,
TO_NCLOB(clob_column) AS nclobvalue
FROM datatype_test;

Result:

CLOB_COLUMN	NCLOB_VALUE
This is ä tëst	This is ä tëst

 

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.

CREATE TABLE clobtest (
  clobval CLOB
);

INSERT INTO clobtest (clobval) VALUES ('qwerty');

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

SELECT clobval
FROM clobtest;

Result:

clobval

qwerty

We can now see our CLOB value.

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

UPDATE clobtest
SET clobval = EMPTY_CLOB();

SELECT clobval
FROM clobtest;

Result:

clobval

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

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

UPDATE clobtest
SET clobval = NULL;

SELECT clobval
FROM clobtest;

Result:

clobval

(null)

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.

CREATE TABLE blobtest (
  blobval BLOB
);

INSERT INTO blobtest (blobval) VALUES ('abcde');

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

SELECT blobval
FROM blobtest;

Result:

BLOBVAL

(BLOB)

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

SELECT DBMS_LOB.SUBSTR(blobval)
FROM blobtest;

Result:

DBMS_LOB.SUBSTR(BLOBVAL)

0ABCDE

We can now see our BLOB value.

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

UPDATE blobtest
SET blobval = EMPTY_BLOB();

SELECT DBMS_LOB.SUBSTR(blobval)
FROM blobtest;

Result:

DBMS_LOB.SUBSTR(BLOBVAL)

(null)

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!