Note
This view is available starting with V4.3.5 for V4.3.x versions.
For V4.2.x versions, this view is available starting with V4.2.5.
Purpose
The ALL_PLSQL_COLL_TYPES view displays overall information about collection types defined in a package. Only if the package is compiled successfully, types in the package can be displayed in this view.
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 types. |
| TYPE_NAME | VARCHAR2(128) | NO | The name of the PL/SQL collection type. |
| PACKAGE_NAME | VARCHAR2(128) | YES | The name of the package in which the PL/SQL collection type is defined. |
| COLL_TYPE | VARCHAR2(18) | NO | The type of the collection. |
| UPPER_BOUND | NUMBER(38) | NO | The upper limit 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 character set name. |
| ELEM_STORAGE | VARCHAR2(7) | NO | The element storage type. |
| NULLS_STORED | VARCHAR2(3) | NO | Whether NULL values are stored. |
| CHAR_USED | VARCHAR2(1) | NO | Whether the character data type is used. |
| INDEX_BY | VARCHAR2(14) | NO | The index method. |
| ELEM_TYPE_MOD | VARCHAR2(7) | NO | The modification of the element type. |
Sample query
Execute the following command to query information about all PL/SQL collection types in the database:
obclient [SYS]> SELECT OWNER,TYPE_NAME,PACKAGE_NAME FROM ALL_PLSQL_COLL_TYPES;
The query result is as follows:
+-------+-----------------------+--------------+
| OWNER | TYPE_NAME | PACKAGE_NAME |
+-------+-----------------------+--------------+
| SYS | <ASSOC_ARRAY_1> | STANDARD |
| SYS | SYS$INT_V2TABLE | STANDARD |
| SYS | <COLLECTION_1> | STANDARD |
| SYS | <TABLE_1> | STANDARD |
| SYS | <V2_TABLE_1> | STANDARD |
| SYS | <VARRAY_1> | STANDARD |
| SYS | DBMSOUTPUT_LINESARRAY | DBMS_OUTPUT |
| SYS | CHARARR | DBMS_OUTPUT |
| SYS | BINARY_DOUBLE_TABLE | DBMS_SQL |
| SYS | BINARY_FLOAT_TABLE | DBMS_SQL |
| SYS | UROWID_TABLE | DBMS_SQL |
| SYS | CLOB_TABLE | DBMS_SQL |
| SYS | BLOB_TABLE | DBMS_SQL |
| SYS | DATE_TABLE | DBMS_SQL |
| SYS | VARCHAR2_TABLE | DBMS_SQL |
| SYS | NUMBER_TABLE | DBMS_SQL |
| SYS | VARCHAR2S | DBMS_SQL |
| SYS | VARCHAR2A | DBMS_SQL |
| SYS | NUMBER_ARRAY | DBMS_UTILITY |
| SYS | INDEX_TABLE_TYPE | DBMS_UTILITY |
| SYS | DBLINK_ARRAY | DBMS_UTILITY |
| SYS | MAXNAME_ARRAY | DBMS_UTILITY |
| SYS | LNAME_ARRAY | DBMS_UTILITY |
| SYS | QUOTED_NAME_ARRAY | DBMS_UTILITY |
| SYS | NAME_ARRAY | DBMS_UTILITY |
| SYS | UNCL_ARRAY | DBMS_UTILITY |
| SYS | DBLARRAY | DBMS_STATS |
| SYS | FLTARRAY | DBMS_STATS |
| SYS | RAWARRAY | DBMS_STATS |
| SYS | CHARARRAY | DBMS_STATS |
| SYS | DATEARRAY | DBMS_STATS |
| SYS | NUMARRAY | DBMS_STATS |
+-------+-----------------------+--------------+
32 rows in set (0.101 sec)