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.
Syntax
{ BINARY LARGE OBJECT | BLOB } [ ( n [ K | M | G ] ) ] [ attribute [...] ]
- n
- 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.
- K
- n is specified in kilobytes (KB). When K is specified, n cannot exceed 2047937.
- M
- n is specified in megabytes (Mb). When M is specified, n cannot exceed 1999.
- G
- n is specified in gigabytes (GB). When G is specified, n must be 1.
- attribute
- Appropriate data type, column storage, or column constraint attributes.
ANSI Compliance
BLOB is ANSI SQL:2011 compliant.
External Representation
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. |
Restrictions
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
- BYTES
- Concatenation operator (||)
- SUBSTRING/SUBSTR
- TYPE
- 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;
Related Topics
FOR information on … | SEE … |
---|---|
functions and operators that support BLOB types | Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. |
implementing UDFs and external stored procedures that operate on BLOBs | Teradata Vantage™ - SQL External Routine Programming , B035-1147 . |
implementing stored procedures that use BLOB local variables or parameters |
|