Note
This view is available starting with V4.3.5 for V4.3.x.
This view is available starting with V4.2.5 for V4.2.x.
Purpose
The DBA_PLSQL_COLL_TYPES view displays general information about collection types defined in the current tenant's package. The types in the package are displayed in this view only after the package is successfully compiled.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | YES | The owner of the PL/SQL collection type. |
| TYPE_NAME | VARCHAR2(128) | NO | The name of the PL/SQL collection type. |
| PACKAGE_NAME | VARCHAR2(128) | YES | The name of the package to which the PL/SQL collection type belongs. |
| COLL_TYPE | VARCHAR2(18) | NO | The type of the collection. |
| UPPER_BOUND | NUMBER(38) | NO | The upper bound of the collection. |
| ELEM_TYPE_OWNER | VARCHAR2(128) | NO | The owner of the element type. |
| ELEM_TYPE_NAME | VARCHAR2(136) | NO | The name of the element type. |
| ELEM_TYPE_PACKAGE | VARCHAR2(128) | NO | The name of the package to which the element type belongs. |
| LENGTH | NUMBER(38) | NO | The length of the collection. |
| PRECISION | NUMBER(38) | NO | The precision. |
| SCALE | NUMBER(38) | NO | The scale. |
| CHARACTER_SET_NAME | VARCHAR2(44) | NO | The name of the character set. |
| ELEM_STORAGE | VARCHAR2(7) | NO | The storage type of the element. |
| NULLS_STORED | VARCHAR2(3) | NO | Indicates whether to store NULL values. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether to use the character data type. |
| INDEX_BY | VARCHAR2(14) | NO | The indexing method. |
| ELEM_TYPE_MOD | VARCHAR2(7) | NO | The modification of the element type. |
Sample query
Execute the following command to query general information about collection types defined in the current tenant's package:
obclient> SELECT * FROM DBA_PLSQL_COLL_TYPES;
The query result is as follows:
+-------+-----------------------+--------------+--------------------+-------------+-----------------+----------------+-------------------+-----------+-----------+-------+--------------------+--------------+--------------+-----------+----------------+---------------+
| OWNER | TYPE_NAME | PACKAGE_NAME | COLL_TYPE | UPPER_BOUND | ELEM_TYPE_OWNER | ELEM_TYPE_NAME | ELEM_TYPE_PACKAGE | LENGTH | PRECISION | SCALE | CHARACTER_SET_NAME | ELEM_STORAGE | NULLS_STORED | CHAR_USED | INDEX_BY | ELEM_TYPE_MOD |
+-------+-----------------------+--------------+--------------------+-------------+-----------------+----------------+-------------------+-----------+-----------+-------+--------------------+--------------+--------------+-----------+----------------+---------------+
| SYS | <ASSOC_ARRAY_1> | STANDARD | PL/SQL INDEX TABLE | 1 | NULL | CHAR | NULL | 1 | 2 | -1 | CHAR_CS | NULL | YES | C | VARCHAR2 | NULL |
| SYS | SYS$INT_V2TABLE | STANDARD | PL/SQL INDEX TABLE | NULL | NULL | NUMBER | NULL | -1 | 38 | 0 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | <COLLECTION_1> | STANDARD | TABLE | NULL | NULL | CHAR | NULL | 1 | 2 | -1 | CHAR_CS | NULL | YES | C | NULL | NULL |
| SYS | <TABLE_1> | STANDARD | TABLE | NULL | NULL | CHAR | NULL | 1 | 2 | -1 | CHAR_CS | NULL | YES | C | NULL | NULL |
| SYS | <V2_TABLE_1> | STANDARD | PL/SQL INDEX TABLE | NULL | NULL | CHAR | NULL | 1 | 2 | -1 | CHAR_CS | NULL | YES | C | BINARY_INTEGER | NULL |
| SYS | <VARRAY_1> | STANDARD | VARYING ARRAY | 1 | NULL | CHAR | NULL | 1 | 2 | -1 | CHAR_CS | NULL | YES | C | NULL | NULL |
| SYS | DBMSOUTPUT_LINESARRAY | DBMS_OUTPUT | VARYING ARRAY | 2147483647 | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | CHARARR | DBMS_OUTPUT | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | BINARY_DOUBLE_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | BINARY_DOUBLE | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | BINARY_FLOAT_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | BINARY_FLOAT | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | UROWID_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | UROWID(4000) | NULL | 4000 | -1 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | CLOB_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | CLOB | NULL | 536870911 | -1 | 0 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | BLOB_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | BLOB | NULL | 536870911 | -1 | 0 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | DATE_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | DATE | NULL | -1 | 19 | 0 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | VARCHAR2_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 4000 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | NUMBER_TABLE | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | NUMBER | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | VARCHAR2S | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 256 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | VARCHAR2A | DBMS_SQL | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | NUMBER_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | NUMBER | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | INDEX_TABLE_TYPE | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | NUMBER | NULL | -1 | 11 | 0 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | DBLINK_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 128 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | MAXNAME_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | LNAME_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 4000 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | QUOTED_NAME_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 130 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | NAME_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 128 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | UNCL_ARRAY | DBMS_UTILITY | PL/SQL INDEX TABLE | NULL | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | BINARY_INTEGER | NULL |
| SYS | DBLARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | BINARY_DOUBLE | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | FLTARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | BINARY_FLOAT | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | RAWARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | RAW | NULL | 2000 | -1 | -1 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | CHARARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | VARCHAR2 | NULL | 4000 | 2 | -1 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | DATEARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | DATE | NULL | -1 | 19 | 0 | CHAR_CS | NULL | YES | B | NULL | NULL |
| SYS | NUMARRAY | DBMS_STATS | VARYING ARRAY | 2050 | NULL | NUMBER | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | NULL | NULL |
+-------+-----------------------+--------------+--------------------+-------------+-----------------+----------------+-------------------+-----------+-----------+-------+--------------------+--------------+--------------+-----------+----------------+---------------+
32 rows in set (0.141 sec)
References
- Query general information about collection types defined in a package: ALL_PLSQL_COLL_TYPES.
- Query general information about collection types defined in the current user's package: USER_PLSQL_COLL_TYPES.