Note
This view is available starting with V2.2.30.
Purpose
This view lists the user-defined collection types available in the database. The columns in this view are the same as those in DBA_COLL_TYPES.
Applicability
This view is applicable only to OceanBase Database in Oracle-compatible mode.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the object. |
| TYPE_NAME | VARCHAR2(64) | NO | The name of the type. |
| COLL_TYPE | VARCHAR2(10) | NO | The collection type. Valid values:
|
| UPPER_BOUND | NUMBER(38) | YES | The maximum size of the collection for VARYING. |
| ELEM_TYPE_MOD | VARCHAR2(7) | YES | This column is not supported. The default value is NULL. |
| ELEM_TYPE_OWNER | VARCHAR2(128) | YES | The owner of the collection element type. |
| ELEM_TYPE_NAME | VARCHAR2(324) | YES | The name of the collection element type. |
| LENGTH | NUMBER(38) | YES | The length of the collection element type. |
| NUMBER_PRECISION | NUMBER(38) | YES | The precision of the collection element type. |
| SCALE | NUMBER(38) | YES | The length of the data on the right side of the decimal point of the collection element type. |
| CHARACTER_SET_NAME | CHAR(7) | YES | The character set of the collection element type. |
| ELEM_STORAGE | CHAR(3) | YES | This column is not supported. The default value is NULL. |
| NULLS_STORED | CHAR(1) | YES | This column is not supported. The default value is NULL. |
Sample query
Query the user-defined collection types accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_COLL_TYPES;
The query result is as follows:
+-------+---------------------+-----------+-------------+---------------+-----------------+----------------+--------+------------------+-------+--------------------+--------------+--------------+
| OWNER | TYPE_NAME | COLL_TYPE | UPPER_BOUND | ELEM_TYPE_MOD | ELEM_TYPE_OWNER | ELEM_TYPE_NAME | LENGTH | NUMBER_PRECISION | SCALE | CHARACTER_SET_NAME | ELEM_STORAGE | NULLS_STORED |
+-------+---------------------+-----------+-------------+---------------+-----------------+----------------+--------+------------------+-------+--------------------+--------------+--------------+
| SYS | DBMS_XA_XID_ARRAY | TABLE | -1 | NULL | SYS | DBMS_XA_XID | -1 | -1 | -1 | CHAR_CS | YES | B |
| SYS | ODCIRIDLIST | TABLE | 32767 | NULL | NULL | VARCHAR2 | 5072 | 2 | -1 | CHAR_CS | YES | B |
| SYS | ODCINUMBERLIST | TABLE | 32767 | NULL | NULL | NUMBER | -1 | -1 | -85 | CHAR_CS | YES | B |
| SYS | ODCIVARCHAR2LIST | TABLE | 32767 | NULL | NULL | VARCHAR2 | 4000 | 2 | -1 | CHAR_CS | YES | B |
| SYS | ODCIDATELIST | TABLE | 32767 | NULL | NULL | DATE | -1 | 19 | 0 | CHAR_CS | YES | B |
| SYS | ODCIGRANULELIST | TABLE | 65535 | NULL | NULL | NUMBER | -1 | -1 | -85 | CHAR_CS | YES | B |
| SYS | XMLSEQUENCE | TABLE | 32767 | NULL | SYS | XMLTYPE | -1 | -1 | -1 | CHAR_CS | YES | B |
| SYS | SDO_ELEM_INFO_ARRAY | TABLE | 1048576 | NULL | NULL | NUMBER | -1 | -1 | -85 | CHAR_CS | YES | B |
| SYS | SDO_ORDINATE_ARRAY | TABLE | 1048576 | NULL | NULL | NUMBER | -1 | -1 | -85 | CHAR_CS | YES | B |
+-------+---------------------+-----------+-------------+---------------+-----------------+----------------+--------+------------------+-------+--------------------+--------------+--------------+
9 rows in set
References
Query all user-defined collection types in the current tenant: DBA_COLL_TYPES
Query the user-defined collection types owned by the current user: USER_COLL_TYPES
For more information about collections, see Collections.