Represents a large binary string of raw bytes. A binary large object (BLOB) column can store binary objects, such as graphics, video clips, files, and documents.
- The number of bytes to allocate for the BLOB column. The maximum number of bytes is 2097088000, which is the default if n is not specified.
- n is specified in kilobytes (KB). When K is specified, n cannot exceed 2047937.
- n is specified in megabytes (Mb). When M is specified, n cannot exceed 1999.
- n is specified in gigabytes (GB). When G is specified, n must be 1.
- Appropriate data type, column storage, or column constraint attributes.
- A BLOB column supports the following attributes:
- NOT NULL
- For details on NOT NULL, see Default Value Control Phrases. For details on FORMAT and TITLE, see Data Type Formats and Format Phrases.
BLOB is ANSI SQL:2011 compliant.
The following table lists the client representations for the Teradata SQL BLOB data type.
Define the length of the BLOB string as k, where 0 <= k <= n.
|Client CPU Architecture||Client Internal Data Format|
|IBM mainframe||Eight bytes (16-bit SMALLINT) length count k followed by k bytes of BYTE data.|
||Eight bytes (16-bit SMALLINT) length count k followed by k bytes of BYTE data.|
A table can have a maximum of 32 LOB columns.
A LOB column cannot be a component of an index. Because of this restriction, a table must define at least one non-LOB column.
Queue tables cannot have BLOB columns.
Functions That Operate on BLOBs
The following are some functions and operators that support BLOB types:
- Concatenation operator (||)
- Explicit data type conversion (CAST and Teradata conversion syntax)
- User-defined functions (UDFs)
- Stored procedures
- External stored procedures
Example: BLOB Data Type
The following example creates a table that defines a BLOB column named blarge:
CREATE TABLE t1 (id INTEGER ,blarge BLOB(128K));
Example: Inserting BLOB Data
The following example shows a stored procedure that inserts part of a BLOB into one table and the remaining part of the BLOB into another table:
CREATE TABLE LocalData(ld_ID INTEGER, ld_DATA BLOB); CREATE TABLE GlobalData (gd_ID INTEGER, gd_DATA BLOB); CREATE PROCEDURE DataSplitter(IN local_ID INTEGER, IN global_ID INTEGER, IN all_DATA BLOB) BEGIN INSERT LocalData (local_ID, SUBSTRING(all_DATA FROM 1 FOR 128546)); INSERT GlobalData (global_ID, SUBSTRING(all_DATA FROM 128547)); END;
|FOR information on …||SEE …|
|functions and operators that support BLOB types||SQL Functions, Operators, Expressions, and Predicates.|
|implementing UDFs and external stored procedures that operate on BLOBs||SQL External Routine Programming.|
|implementing stored procedures that use BLOB local variables or parameters||