Difference between CLOB and BLOB from DB2 and Oracle Perspective?

database oracle db2 blob clob

209,984

Solution 1

BLOB is for binary data (videos, images, documents, other)

CLOB is for large text data (text)

Maximum size on MySQL 2GB

Maximum size on Oracle 128TB

Solution 2

BLOB primarily intended to hold non-traditional data, such as images,videos,voice or mixed media. CLOB intended to retain character-based data.

Solution 3

They can be considered as equivalent. The limits in size are the same:

Maximum length of CLOB (in bytes or OCTETS)) 2 147 483 647
Maximum length of BLOB (in bytes) 2 147 483 647

There is also the DBCLOBs, for double byte characters.

References:

LOB definition in DB2: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008473.html
SQL and XML limits: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html

209,984

The Dark Knight

#SOreadytohelp I am a computer science engineer and a programming enthusiast with 10+ yrs of experience in the IT industry . I like any technical challenge thrown to me and if i don't know it, i like to delve deeper into the technical aspect of that thing to know it better . I don't think that people with experience know everything . I have been blogging for sometime now, where i like to share with people what i know . I also have a low tech web site. You can visit my blog here : http://thinkfree-thinkfree.blogspot.in/ And my web site here : http://kunalbhowmick.page.tl/Home.htm For the CSS: http://forum.jquery.com/topic/jquery-how-to-prevent-third-party-css-code-override-our-css Best Solution : https://www.tinfoilsecurity.com/blog/protect-your-website-from-embedded-content-iframe-security Stop Iphone Zoom In/Out : Android viewport setting "user-scalable=no" breaks width / zoom level of viewport

Updated on February 07, 2020

Comments

The Dark Knight over 4 years

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows :

BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such as voice or mixed media. BLOB strings are not associated with a character set, as with FOR BIT DATA strings.

CLOB : Variable-length character large object string that can be up to 2GB (2,147,483,647) long. A CLOB can store single-byte character strings or multibyte, character-based data. A CLOB is considered a character string.

What I don't know, is whether there is any difference between the two from DB2 and Oracle perspective? I mean, what are the differences between DB2 CLOB and Oracle CLOB, also between DB2 BLOB and Oracle BLOB? What is the maximum size of both in DB2 and Oracle? Is it just 2 GB ?
underscore_d over 8 years

"They can be considered as equivalent" in size. The behaviour on different types of contained data may be very different and should probably have been included in your answer. The hint is in the first letter of the acronym.
mckenzm about 8 years

The difference is apparent when you have to re-encode or transmit them. Binary objects should not be translated in transport. Character objects can contain mixed-width characters. Binary objects are possibly non-printable. All character is binary, the converse is not necessarily true once you leave the database, nor again when leaving the platform.