> For the complete documentation index, see [llms.txt](https://docs.tibero.com/tibero-manuals_en/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tibero.com/tibero-manuals_en/7.2.5.manuals_en/tibero-application-developers-guide/collection-type.md). # Collection Type Overview A collection type is a type of a user-defined type that can be defined to hold values of the same type. \ It exists in one of the following two types. * A variable array is a collection of elements of the same type that are ordered. * A nest table is a collection of elements of the same type that are unordered and unlimited in number. {% hint style="info" %} **Note** Currently, nested tables can be used only within PSM. {% endhint %} ### Creating a Collection Type Use the CREATE TYPE statement to create a collection type. To create a variable array type, specify an AS VARRAY as follows: \ In the parentheses following the VARRAY, enter the maximum number of elements that this variable array can contain. The element type following OF can specify the name of the built-in type or user-defined type. **\[Example 1] Creating a Collection Type** ``` REATE TYPE str_varr_type AS VARRAY(10000) OF VARCHAR(100); / ``` As with creating an object type, collection type generation does not allocate space for storing the actual collection, and only describes the appearance of the collection. LOB types and variable arrays for XML types cannot be created. A collection type can be used as the column type of a table, the property type of an object type, the variables and parameters in a PSM, and the type of the return value. However, it cannot be used as an object table type. ### Creating a Collection To create a collection, list the elements of the collection type inside parentheses after the name of the collection type. **\[Example 2] Creating a Collection** ``` str_varr_type('ABC', 'DEFG', 'HH') str_varr_type() ``` When the user creates a collection without element values inside parentheses, the user is creating what is called an empty collection. An empty collection is not NULL; it just does not contain elements that participate in a collection. ### Multi-layered Collection Type If the type of an element of the collection type is another collection type, or if the element type is an object type and one of the elements of this object type is a collection type, this is called a multi-layer collection type. In the current SQL query, it is possible to construct a multilayer collection type only as a variable array, and it is not possible to construct a multilayer collection type by mixing a nested table and a variable array. Multi-layer collection types are also available where collection types are available. When the user creates a multi-tiered collection, the user specifies the elements in the same way as a regular collection, which ultimately specifies the nested collection type names. **\[Example 3] Multi-layered Collection Type** ``` CREATE OR REPLACE TYPE str_varr_coll_type AS VARRAY(1000) OF str_varr_type; / CREATE TABLE nested_coll_tbl (id number, coll_val str_varr_coll_type); INSERT INTO nested_coll_tbl VALUES (1, str_varr_coll_type(str_varr_type('AB', 'CD'), str_varr_type())); INSERT INTO nested_coll_tbl VALUES (2, str_varr_coll_type(str_varr_type(), str_varr_type('EF', 'GH'))); ``` In Queries Currently, the function for passing the contents of a collection to the client as a collection of SELECT columns is not implemented. However, you can retrieve the contents of a collection from the SELECT query by unpacking the collection using the TABLE () expression. The TABLE() expression uses the FROM clause to allow the user to use each element in the collection as if it were a table with rows. **\[Example 4] Using a Collection Type in a Query** ``` CREATE TABLE coll_tbl (id number, coll_val str_varr_type); INSERT INTO coll_tbl VALUES (1, str_varr_type('AB', 'CD')); INSERT INTO coll_tbl VALUES (2, str_varr_type('EF', 'GH')); INSERT INTO coll_tbl VALUES (3, str_varr_type()); SQL> SELECT id, d.* FROM coll_tbl c, TABLE(c.coll_val) d; ID COLUMN_VALUE ----------- -------------- 1 AB 1 CD 2 EF 2 GH 4 rows selected. ``` A column supplied as an argument to the TABLE () expression can specify a column of the collection type among the columns of the table to its left in the FROM clause. As shown previously, for collections of built-in types or collections, the table created by the TABLE () expression has only one column called COLUMN\_VALUE. For a TABLE () expression, an outer join can be performed by specifying the outer join operator (+) as follows. **\[Example 5] Selecting an OuterJoin of Collection Type** ``` SQL> SELECT id, d.* FROM coll_tbl c, TABLE(c.coll_val)(+) d; ID COLUMN_VALUE ----------- -------------- 1 AB 1 CD 2 EF 2 GH 3 5 rows selected. ``` If no outer join is performed, rows corresponding to the empty collection are not selected. However, if the user performs an outer join, then the rows corresponding to the empty collection are also outputted. If the result of a subquery is a scalar subquery that returns a collection value, the user can use this subquery as an argument to the TABLE () expression. In other words, a subquery is allowed only for scalar subqueries that can come up with regular value expressions. **\[Example 6] When the Result of a Subquery is a Collection** ``` SQL> SELECT * FROM TABLE(SELECT coll_val FROM coll_tbl WHERE id = 1); COLUMN_VALUE -------------- AB CD 2 rows selected. ``` If the WHERE clause does not exist in the subquery in the previous example and the number of result rows in the subquery is more than 2, the previous TABLE query will not execute normally and will cause a runtime error. For multi-layer collections, it is possible to extract elements in any layer of the multi-layered collection by using the TABLE () expression repeatedly. A table alias is required to specify the collection column created by the table () expression of the upper layer in the table () expression of the next layer. **\[Example 7] Multi-layered Collection** ``` SQL> SELECT id, z.* FROM nested_coll_tbl x, TABLE(x.coll_val) y, TABLE(y.COLUMN_VALUE) z; ID COLUMN_VALUE ----------- -------------- 1 AB 1 CD 2 EF 2 GH 4 rows selected. ``` \ If the collection is a collection of objects, the table returned by the TABLE () expression becomes the object table and can use any expression that can be used in the object table. As well, this table also does not have a COLUMN\_VALUE column and has columns with the same name as the property name of the underlying object type in the object table. **\[Example 8] Collection that is a Collection of Objects** ``` /CREATE TYPE customer_type2 AS OBJECT ( custno NUMBER, name VARCHAR2(40), phone VARCHAR2(20), MEMBER FUNCTION tostring RETURN VARCHAR); / CREATE TYPE BODY customer_type2 AS MEMBER FUNCTION tostring RETURN VARCHAR IS BEGIN RETURN custno || ':' || name || ':' || phone; END; END; / CREATE OR REPLACE TYPE cust_coll_type AS VARRAY(400) OF customer_type2; / CREATE TABLE cust_coll_tab ( id NUMBER, coll_val cust_coll_type); INSERT INTO cust_coll_tab VALUES (1, cust_coll_type(customer_type2(1, 'Bob', '222-333-4444'), customer_type2(2, 'Alice', '444-555-6666'))); SQL> SELECT id, d.*, VALUE(d).tostring() str FROM cust_coll_tab c, TABLE(c.coll_val) d; ID CUSTNO NAME PHONE STR ---------- ------- ------ ------------ ---------------------- 1 1 Bob 222-333-4444 1:Bob:222-333-4444 1 2 Alice 444-555-6666 2:Alice:444-555-6666 2 rows selected. ``` ### In DML Currently, DML is not supported for nested tables. For variable arrays, DML is supported for inserting or updating via variable array values. Manipulating elements of a variable array within a query is not supported. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.tibero.com/tibero-manuals_en/7.2.5.manuals_en/tibero-application-developers-guide/collection-type.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.