In this article we look into the difference between CLOB and BLOB datatypes in Oracle/MYSQL. We will also see about the description of each datatype and how to create attributes in a table with these datatypes.
So, CLOB and BLOB are types of Large Object Datatypes (LOB). LOB’s are a set of datatypes used to store or hold large amounts of data to access and manipulate the data efficiently. To learn more about Large Objects refer to Oracle Documentations.
Also Read: Difference Between MySQL and ORACLE
CLOB
CLOB stands for Character Large Object. It is a built in datatype used to store large textual data with variable length holding up to 2,147,483,647 bytes of characters i.e. up to 2 GB long. It can store both single byte or multiple byte character streams. The CLOB datatype is represented using the java.sql.Clob interface in the JDBC API in Java. The CLOB object in JDBC holds a logical pointer to SQL CLOB. In other words, the object points to the location of the data instead of holding its character data.
CLOB Datatype Attribute in Oracle:
Column_Name CLOB
Note: We do not need to specify CLOB Size while declaring the attribute by default size is 2,147,483,647.
MYSQL supports CLOB Datatype Attribute using only TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.
Example:
Column_Name LONGTEXT
BLOB
BLOB stands for Binary Large Object, a built in datatype used to store large data in form of binary streams with a maximum length of 65,535 bytes or 64 KB. It generally stores non-traditional data such as files including voice, images and mixed data etc. The BLOB datatype is represented using the java.sql.Blob interface in the JDBC API in Java. The BLOB object in JDBC holds a logical pointer to SQL BLOB i.e. the object points to the location of the data instead of holding its binary data.
In ORACLE as well as in MYSQL, BLOB datatype is defined as:
Column_Name BLOB
However, MYSQL supports BLOB with following datatypes as well : TINYBLOB, MEDIUMBLOB and, LONGBLOB.
Now, Let us look how these datatypes fit in the schema while creating a table in ORACLE.
CREATE TABLE BOOK_DETAILS ( Book_Id NUMBER, About_Book CLOB, Book_Logo BLOB );
Explanation:
About_Book attribute of table BOOK_DETAILS contains description about a book which may require large textual data so in this case it is more appropriate to use CLOB and field Book_Logo stores the book logo, an image resource so BLOB datatype goes well with this field.
In MYSQL this is implemented as follows:
CREATE TABLE BOOK_DETAILS ( Book_Id NUMBER, About_Book LONGTEXT, Book_Logo BLOB );
Difference between CLOB and BLOB
Now, let’s look at some key differences between the two and compare them.
| CLOB | BLOB |
| 1. The abbreviation stands for Character Large Object. | 1. It stands for Binary Large Object. |
| 2.This datatype stores large textual data in the form of character streams with a maximum length of 2,147,483,647 character bytes i.e. up to 2GB long. | 2. This datatype stores large binary data in the form of binary or bitstreams with a maximum of 65535 characters or 64 KB long. |
| 3. Mainly used to store files with text information such as text files, PDF documents etc. | 3. Generally stores files including media data such as images, videos ,audio files etc. |
4. Oracle supports this using CLOB while MYQL supports this datatype using only alias of TEXT type such as:
|
4. Oracle as well as MYSQL supports this datatype using BLOB after field name. However MYSQL also has various implementations of this such as:
|
| 5. Present in JDBC API as java.sql.Clob interface and implemented using SQL Locator. | 5. Present in JDBC API as java.sql.Blob interface and implemented using SQL Locator. |
That’s all for the article you can create a table with the following attributes shown in the example, add some records and see it in working!
You can ask your queries in the comment section below.