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 DBA_PLSQL_COLL_TYPES view displays the overall information about the COLLECTION data types defined in packages in the current tenant.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | YES | The owner of the PL/SQL COLLECTION data type. |
| 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
You can view general information about collection types defined in the current tenant's package.
obclient> SELECT * FROM SYS.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
References
- View information about collection types in packages accessible to the current user: ALL_PLSQL_COLL_TYPES
- View information about collection types in packages owned by the current user: USER_PLSQL_COLL_TYPES