Mimer SQL and LOBs
Categories: Database features, SQL

A new feature of Mimer SQL 9 is support for Binary Large Objects (BLOBs) and Character Large Objects (CLOBs). Support for National Character Large Objects (NCLOBs) was added in Mimer SQL 9.2.


A BLOB can be used to store all kinds of unformatted binary data, e.g. pictures, sounds or even video. CLOBs can be used for storing large documents or other large text objects. NCLOBs are used for storing large documents using Unicode character sets.

The BLOB/CLOB/NCLOB functionality in Mimer SQL 9 is totally compliant with the SQL-99 standard from ISO. The maximum size for a BLOB/CLOB/NCLOB data element when stored in a Mimer SQL 9 database is 8 TB. Usually the maximum length of a LOB is determined by the amount of disk space available for its storage.

The implementation of BLOB/CLOB/NCLOB support in Mimer SQL is optimized for extremely efficient access and storage and is totally integrated in the transaction management.


The BLOB/CLOB/NCLOB data types stores binary string values, character string values and national character string values, respectively. The length can vary up to the maximum specified as the large object length (n[K|M|G]). The large object length is n, optionally multipled by K|M|G.

LOBs can be used as follows:
  • Retrieving LOBs with simple column references in the SELECT clause of a SELECT statement

  • Assigning LOBs using INSERT statements with a VALUES clause

  • Assigning LOBs using UPDATE statements

  • Adding LOB columns using CREATE TABLE or ALTER TABLE

  • Dropping LOB columns using ALTER TABLE

  • Altering LOB column data types using ALTER TABLE

There are some restrictions associated with using LOBs. The only comparison supported for LOB values is using the NULL predicate. A LOB column may not be part of any primary key, index, unique constraint or primary key constraint.

The comparison restrictions also prevent BLOB columns from being used in DISTINCT, GROUP BY and ORDER BY clauses and UNION statements.

You can currently not: use LOBs in stored procedures, define triggers for tables that contain LOB columns or add LOB columns to tables that have triggers.


Read more about BLOB/CLOB/NCLOB in the Mimer SQL Documentation Set, PDF-format. (See the Reference Manual.)

Last updated: 2005-04-26


Powered by Mimer SQL

Powered by Mimer SQL