The one-dimensional ARRAY/VARRAY type is defined as a variable-length ordered list of values. It has a maximum number of values that you specify when you create the type. You can access each element value in a one-dimensional ARRAY/VARRAY type using a numeric index value. For more information, see Teradata Vantage™ - Data Types and Literals, B035-1143.
- BLOB
- CLOB
- LOB UDTs (both distinct and structured types)
- Geospatial
Note that you also cannot specify a one-dimensional ARRAY/VARRAY type as the element type of an ARRAY/VARRAY type.
- VARIANT_TYPE
- TD_ANYTYPE
You can specify a one-dimensional ARRAY/VARRAY type for a table column or for a parameter to a UDF, method, external stored procedure, or SQL stored procedure. You can also use a one-dimensional ARRAY/VARRAY type to declare a local variable inside of an SQL stored procedure. The database supports ANSI-style DML syntax to enable you to access and manipulate the individual elements of a one-dimensional ARRAY/VARRAY value.
- If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java external procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
- The Oracle-compatible CREATE TYPE syntax for VARRAY does not support the REPLACE TYPE statement.
Neither does the VARRAY syntax support the optional NOT NULL clause for designating elements as not being null-constrained.
- As is true for structured and distinct UDTs, the database stores any one-dimensional ARRAY/VARRAY type that you create in the SYSUDTLIB database, along with its autogenerated constructor UDF, by default. This means that you must have either the UDTTYPE or UDTMETHOD privilege on SYSUDTLIB in order to create a one-dimensional ARRAY or VARRAY UDT.
- You can use the optional DEFAULT NULL clause to initialize all the elements of a one-dimensional ARRAY/VARRAY type to null at the time the type is created.
This clause is particularly useful for one-dimensional ARRAY/VARRAY types that are expected to be fully populated.
When you do this, the action prevents all subset operations such as average, update, or count for one-dimensional arrays from aborting and returning an error because they refer to an element that is not initialized.
- The one-dimensional ARRAY/VARRAY type only supports a lower bound of 1. This means that every one-dimensional ARRAY/VARRAY type that is created has a first array element indexed by the number 1. This rule permits the CREATE TYPE one-dimensional ARRAY/VARRAY syntax to be compatible with competitor syntax for arrays, and also to be compliant with the ANSI SQL:2011 standard for the ARRAY type structure.
Note that this is different from multidimensional ARRAY/VARRAY types, which support an optional user-specified lower bound. The CREATE TYPE syntax for one-dimensional ARRAY/VARRAY types is consistent with this rule because it only supports a size value rather than optional explicit lower and upper bounds.
- The maximum size of a one-dimensional ARRAY/VARRAY type, and its autogenerated transform string must not exceed 64 KB because the database stores its data within the row and because the maximum size of its autogenerated transform string is limited to the maximum size of a VARCHAR type, which is 64,000 Teradata Latin bytes.
Although there is no restriction with regards to the number of table columns that can be defined as a one-dimensional ARRAY/VARRAY type, the expectation is that a table will generally not contain more than one column with a one-dimensional ARRAY/VARRAY type, and unless the table is defined as a NoPI table, it will contain other columns, some of which might have smaller data types.
Additionally, the row header for each row consumes an additional set of bytes, so the maximum size of a one-dimensional ARRAY/VARRAY data type is unlikely to exceed approximately 70% of the maximum row size. To enable the greatest degree of freedom for creators of one-dimensional ARRAY/VARRAY types, the only size restriction that the database enforces is that of the row size limit.
For example, if n is the cardinality of a one-dimensional ARRAY A of integer type, then A[n] cannot have more than (16,384 - x) elements (1≤n ≤ (16,384 - x)), where x represents the number of extra bytes required for in-row storage of the ARRAY type to enable direct access to the individual elements of the array, as well as overhead for displaying the autogenerated transform string for the ARRAY.
- The following actions take place on the data dictionary on a CREATE TYPE request for a one-dimensional ARRAY/VARRAY. The actions listed are in addition to dictionary updates that normally occur for a CREATE TYPE request.
- The row that is already inserted into to DBC.UDTInfo for a CREATE TYPE request also populate the columns DBC.UDTInfo.ArrayNumDimensions and DBC.UDTInfo.ArrayScope.
See Teradata Vantage™ - Data Dictionary, B035-1092 for details about the values inserted into this row for a one-dimensional ARRAY type.
- One row is inserted into DBC.TVFields to record information about the element type specified for the one-dimensional ARRAY type. The field name for the element type is recorded as the predefined value _ARRAYELEMENT, with a field ID of 1025. If the element type specified for the one-dimensional ARRAY type is a UDT, then the name of the UDT element type and its TypeID are also recorded.
See Teradata Vantage™ - Data Dictionary, B035-1092 for details about the values inserted into this row for a one-dimensional ARRAY type.
- The row that is already inserted into to DBC.UDTInfo for a CREATE TYPE request also populate the columns DBC.UDTInfo.ArrayNumDimensions and DBC.UDTInfo.ArrayScope.