DB2 Version 10.1 for Linux, UNIX, and Windows

CREATE TYPE (VARRAY) statement (PL/SQL)

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

Read syntax diagramSkip visual syntax diagram
>>-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