Clob Datatype in Oracle Database

by
use clob datatype in sql developer tool

In this Oracle tutorial, we will learn about the Clob datatype in the oracle database. Also, we will illustrate how to use it to create Clob columns for a table in oracle.

Introduction to Oracle Clob datatype

The CLOB (Character Large Object) datatype in Oracle 21c is used to store character data that can be larger in size than other data types such as varchar2 or char. This type of data is stored as text and can be used to store things like long descriptions, notes, and comments.

Advantages of Clob datatype

  • Large data storage: CLOB datatype can store character data up to 4GB in size, making it suitable for storing large amounts of text-based data.
  • Text-based data storage: CLOB data is stored as text, making it more efficient for storing and retrieving text data than storing it as binary data.
  • Compatibility with other data types: CLOB data can be converted to and from other data types, making it easy to work with other data in your database.

Also, check: How to Check Oracle Database Version

Oracle Clob datatype Syntax

The syntax for declaring a column with the CLOB data type in Oracle 21c is as follows:

column_name CLOB;

Oracle Clob datatype Example

In this example, we will create a table named “employees” with three columns. The “description” column is of the CLOB data type and can be used to store character data.

CREATE TABLE employees (
  emp_id NUMBER,
  emp_name VARCHAR2(30),
  description CLOB
);

The “description ” column in this example has a CLOB data type.

Now we will insert the values in a given table

INSERT INTO employees (emp_id, emp_name, description)
VALUES (782, 'Micheal', 'This is a description of item 1');
Using clob datatype in oracle 21c
Using clob datatype in oracle 21c

Read: How to drop database in Oracle

Example

Now, we will discuss how to use the clob datatype in the SQL developer tool. First, we will create a table of students and use the clob datatype for it.

use clob datatype in sql developer tool
use clob datatype in an SQL developer tool

Now we will insert the values into a students name table

INSERT INTO students VALUES (892, 'I studied in a 7th class');
insert the values into a student table
insert the values into a student table

Also, check: Oracle Create Index

Storage and performance

In terms of performance, the CLOB data type can be slow when compared to other data types, as it requires additional processing time to retrieve the data from a separate location.

However, the CLOB data type offers better performance when it comes to handling large amounts of text data, as it is optimized for storing and processing large amounts of text data.

Conclusion

So, in this Oracle tutorial, we understood how to define and use the clob Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.