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.Oracle TO_CLOB Function

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 initalise 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 initalise 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

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

 

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 functons 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 initalised 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!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Get Your SQL Function Cheat Sheet Now: