Skip to content
≡

Administrator

Developer

Tools

External Tools

LOB Data Type

LOB Data Type#

Overview#

The LOB (which stands for Large Object) data type is for holding large amounts of data. Up to 4 GB - 1 byte can be stored in one column of LOB data. Unlike other data types, the length of a LOB column does not need to be specified when a table is created. Additionally, more than one LOB type column can be defined in a table.

The LOB data type is divided into the Binary Large Object (BLOB) type, which is for holding binary data such as image and video files, and the Character Large Object (CLOB) type, which is for holding string data.

The Features of Altibase LOB#

The LOB data type provided with Altibase has the following features:

  • Data Storage Functions
  • Partial Read
  • Disk LOB Partitioning

Features of LOB#

The LOB data type provided with Altibase has the following features:

  • Data Storage Functions
  • Partial Read
  • Disk LOB Partitioning

Partial Read#

It is possible to read only a desired portion of LOB data. A specific amount of data, offset a specific distance from the beginning of the file, can be read using the SQLGetLob function in Altibase ODBC.

Disk LOB Partitioning#

Disk LOB data can be stored in a disk tablespace other than the one in which the table is stored. This tablespace can be configured in a method similar to partitioning.

Storing LOB Columns#

In most cases, LOB data are stored in a variable area, away from the rest of the record. However, if a small amount of data is stored in the LOB column, the column can be stored in an area that is contiguous with the rest of the record (i.e. in the fixed area) using the 'in row' option. Note that this is possible for memory tables only; regardless of their size, LOB data in disk tables are always stored in a separate, variable area.

Because the amount of LOB column data that is stored in the variable area is typically very large, storing it in the same tablespace as the rest of the record has a negative impact on the efficiency of usage of space.

In a disk table, LOB column data can be stored in a tablespace other than the one containing the table to which the LOB column belongs. However, in a memory table, LOB column data cannot be stored separately, and thus are stored in the same tablespace as the table.

BLOB#

Syntax Diagram#

Syntax#

BLOB [ VARIABLE ( IN ROW size ) ]

Description#

BLOB is a binary data type that can vary in length up to 4 GB - 1 byte and is intended for use in storing large amounts of binary data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW clauses" sections above.

CLOB#

Syntax Diagram#

Syntax#

CLOB [ VARIABLE ( IN ROW size ) ]

Description#

CLOB is a character data type that can vary in length up to 4 GB - 1 byte and is intended for use in storing large amounts of character data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW clauses" sections above.

Temporary LOB#

Temporary LOB is used to handle large text or binary data. Temporary LOB can be enabled by setting the TEMPORARY_LOB_ENABLE property to 1. Information about currently used Temporary LOBs can be queried through V$TEMPORARY_LOBS.

Features#

  • A Temporary LOB is created in memory at execution time.
  • It exists only during a specific session or transaction and is automatically deleted when that session or transaction ends.

Temporary LOB Types#

Refer to the table below for descriptions of each Temporary LOB type.

  • Transaction Temporary LOB: A Temporary LOB dependent on the transaction lifecycle.

  • Session Temporary LOB : A Temporary LOB dependent on the session lifecycle.

Comparison Transaction Temporary LOB Session Temporary LOB
Lifecycle Transaction Session
Cleanup time Cleaned up when the transaction ends - Cleaned up when the session ends
- Can be explicitly cleaned up using ALTER SESSION SET FREE TEMPORARY LOB
Usage Used in all cases except when a Session Temporary LOB is created, such as:
- TO_CLOB
- TO_BLOB
- SUBSTR with a CLOB argument
- CONCAT with a CLOB argument
- LOB-type variables in PSM		 Used when LOB types are used in the following forms within PSM:
- ASSOCIATIVE ARRAY
- VARRAY
- PACKAGE variables

Examples - Transaction Temporary LOB#

iSQL> CREATE TABLE t1(c1 CLOB);
Create success.

iSQL> AUTOCOMMIT OFF;
Set autocommit off success.

iSQL> INSERT INTO t1 VALUES ('ABCD');
1 row inserted.

iSQL> SELECT c1||'E' from t1;
C1||'E'
-----------------------------------------------------------------------------------
ABCDE
1 row selected.

iSQL> SELECT TYPE, OPEN_COUNT FROM V$TEMPORARY_LOBS;
TYPE                 OPEN_COUNT
---------------------------------------------
0                    1
1 row selected.

iSQL> COMMIT;
Commit success.

iSQL> SELECT TYPE, OPEN_COUNT FROM V$TEMPORARY_LOBS;
TYPE                 OPEN_COUNT
---------------------------------------------
No rows selected.

Example - Session Temporary LOB#

iSQL> CREATE OR REPLACE PACKAGE pkg1
     AS
         V1 CLOB;
         PROCEDURE PROC1;
     END;
     /
Create success.

iSQL> CREATE OR REPLACE PACKAGE BODY pkg1
     AS
         PROCEDURE proc1
         AS
             V2 CLOB;
         BEGIN
             V1 := 'pkg spec session clob';
             V2 := 'pkg body session clob';
             PRINTLN(V1);
            PRINTLN(V2);
         END;
     END;
     /
Create success.

iSQL> SELECT TYPE, OPEN_COUNT FROM V$TEMPORARY_LOBS;
TYPE                 OPEN_COUNT
---------------------------------------------
No rows selected.

iSQL> EXEC pkg1.proc1;
pkg spec session clob
pkg body session clob
Execute success.

iSQL> SELECT TYPE, OPEN_COUNT FROM V$TEMPORARY_LOBS;
TYPE                 OPEN_COUNT
---------------------------------------------
1                    1
1 row selected.

-- Clean up session temporary LOBs in the current session
iSQL> ALTER SESSION SET FREE TEMPORARY LOB;
Alter success.

iSQL> SELECT TYPE, OPEN_COUNT FROM V$TEMPORARY_LOBS;
MANAGER_TYPE         OPEN_COUNT
---------------------------------------------
No rows selected.

Restriction#

  • LOB type columns can't be used with cursors.
  • LOB type columns can't be used in volatile tables or disk temporary tablespaces.
  • LOB columns associated with tables in discarded tablespaces cannot be accessed.
  • LOB type columns cannot be used for partitioning conditions, because in order to partition a column it must be possible to perform comparisons on the data in the column.
  • Indexes cannot be created for LOB columns.