The CREATE TYPE (VARRAY) statement defines a VARRAY data
type.
Invocation
This statement can be executed
from the DB2® command line processor
(CLP), any supported interactive SQL interface, an application, or
a routine.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following:
- If the schema name of the VARRAY type does not exist, IMPLICIT_SCHEMA
authority on the database
- If the schema name of the VARRAY type refers to an existing schema,
CREATEIN privilege on the schema
- DBADM authority
Syntax
>>-CREATE--+------------+--TYPE--varraytype--+-IS-+------------->
'-OR REPLACE-' '-AS-'
>--VARRAY--(--n--)--OF--datatype-------------------------------><
Description
- OR REPLACE
- Indicates that if a user-defined data type with the same name
already exists in the schema, the new data type is to replace the
existing one. If this option is not specified, the new data type cannot
replace an existing one with the same name in the same schema.
- varraytype
- Specifies an identifier for the VARRAY type. The unqualified form
of varraytype is an SQL identifier with a maximum
length of 128. The value of the CURRENT SCHEMA special register is
used to qualify an unqualified object name. The qualified form of varraytype is
a schema name followed by a period character and an SQL identifier.
If a two-part name is specified, the schema name cannot begin with
'SYS'; otherwise, an error is returned (SQLSTATE 42939). The name
(including an implicit or explicit qualifier) must not identify a
user-defined data type that is described in the catalog (SQLSTATE
42723). The unqualified name is unique within its schema, but does
not need to be unique across schemas.
- n
- Specifies the maximum number of elements in the array type. The
maximum cardinality of an array on a given system is limited by the
total amount of memory that is available to DB2 applications. As such, although arrays of
large cardinalities (up to 2,147,483,647) can be created, not all
elements might be available for use.
- datatype
- A supported data type, such as NUMBER, VARCHAR2,
RECORD, VARRAY, or associative array type. The %TYPE attribute and
the %ROWTYPE attribute are also supported.
Example
The following example creates a
VARRAY data type with a maximum of 10 elements, where each element
has the data type NUMBER:
CREATE TYPE NUMARRAY1 AS VARRAY (10) OF NUMBER