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!