Note
This view was introduced in OceanBase Database V4.2.5. Data types defined in a package are displayed in this view only if the package is successfully compiled.
Purpose
The USER_PLSQL_COLL_TYPES view displays the overall information about the COLLECTION data types defined in packages of the current user.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| TYPE_NAME | VARCHAR2(128) | NO | The name of the PL/SQL COLLECTION data type. |
| PACKAGE_NAME | VARCHAR2(128) | YES | The name of the package to which the PL/SQL COLLECTION data type belongs. |
| COLL_TYPE | VARCHAR2(18) | NO | The data type of the collection. |
| UPPER_BOUND | NUMBER(38) | NO | The upper boundary 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 element storage type. |
| NULLS_STORED | VARCHAR2(3) | NO | Indicates whether null values are stored. |
| CHAR_USED | VARCHAR2(1) | NO | Indicates whether character data types are used. |
| INDEX_BY | VARCHAR2(14) | NO | The indexing method. |
| ELEM_TYPE_MOD | VARCHAR2(7) | NO | The modifier of the element type. |
Sample query
View the general information about collection types defined in the packages owned by the current user.
obclient> SELECT * FROM SYS.USER_PLSQL_COLL_TYPES;
The query result is as follows:
+-----------+--------------+-----------+-------------+-----------------+----------------+-------------------+--------+-----------+-------+--------------------+--------------+--------------+-----------+----------+---------------+
| 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 |
+-----------+--------------+-----------+-------------+-----------------+----------------+-------------------+--------+-----------+-------+--------------------+--------------+--------------+-----------+----------+---------------+
| TY1 | PKG1 | TABLE | NULL | NULL | VARCHAR2 | NULL | 32767 | 2 | -1 | CHAR_CS | NULL | YES | B | NULL | NULL |
| TY2 | PKG1 | TABLE | NULL | NULL | NUMBER | NULL | -1 | -1 | -85 | CHAR_CS | NULL | YES | B | NULL | NULL |
+-----------+--------------+-----------+-------------+-----------------+----------------+-------------------+--------+-----------+-------+--------------------+--------------+--------------+-----------+----------+---------------+
2 rows in set
References
- View the information about collection types in the packages accessible to the current user: ALL_PLSQL_COLL_TYPES
- View the information about collection types in all packages in the current tenant: DBA_PLSQL_COLL_TYPES