BLOB/CLOB Types

Large binary and text data types

Data Type	Size	Size (Not Null)	Synonyms
BLOB	1 byte	0 bytes	BINARY LARGE OBJECT, LONG VARBINARY
CLOB	1 byte	0 bytes	CHARACTER LARGE OBJECT, LONG VARCHAR

The size designations above refer to the memory requirements of in row for managing the BLOB/CLOB type.

Binary Large Object (BLOB)

BLOB (Binary Large Object) is a data type in standard SQL used to store large amounts of data. It is basically a binary string of variable length, stored as a sequence of bytes or octets. BLOB data type is generally used to store large files such as images, media files such as video and audio clips in the database.

For BLOB data types, the size that is returned by BLOB functions is always in number of bytes. Also, positioning is always in bytes, as well.

BLOB data type size is stored in a UINT64 which means that the only limitation to the size of a stored BLOB is the amount of disk space available for the database.

BLOB Examples

Example 1:

create table imgStore (
    id char(20) primary key,
    category char(5) references catagoryTable,
    image blob
);

Example 2:

create table imgStore (
    id char(20) primary key,
    category char(5) references catagoryTable,
    image long varbinary
);

Character Large Object (CLOB)

CLOB stands for Character Large Object in general, an SQL CLOB data type is used to store large amount of textual data in UTF-8 encoding.

For CLOB data types, the size that is returned by CLOB functions is always in number of characters. Also, positioning is always in characters, as well.

CLOB data type size is stored in a UINT64 which means that the only limitation to the size of a stored CLOB is the amount of disk space available for the database.

CLOB Examples

Example 1:

create table poemStore (
    title char(50) primary key,
    author id references authorTable,
    category char(5) references catagoryTable,
    poem clob
);

Example 2:

create table poemStore (
    title char(50) primary key,
    author id references authorTable,
    category char(5) references catagoryTable,
    poem long varchar
);