Accessing a Collection Through a Virtual Table

When you use the collection expression format of the collection-derived table segment to access the elements of a collection, you can select elements of the collection directly through a virtual table. You can use this format in the FROM clause of a SELECT statement. The FROM clause can be in either a query or a subquery.

With this format you can use joins, aggregates, the WHERE clause, expressions, the ORDER BY clause, and other operations that are not available when you use the collection-variable format. This format reduces the need for multiple cursors and temporary tables.

Examples of possible collection expressions include column references, scalar subquery, dotted expression, functions, operators (through overloading), collection subqueries, literal collections, collection constructors, cast functions, and so on.

3The following example uses a SELECT statement in the FROM 3clause whose result set defines a virtual table consisting of the fifty-first 3through seventieth qualifying rows, ordered by the employee_id column value.

SELECT * FROM TABLE(MULTISET(SELECT SKIP 50 FIRST 20 * FROM employees 
3   ORDER BY employee_id)) vt(x,y), tab2 WHERE tab2.id = vt.x;

3The following example uses a join query to create a virtual 3table of no more than twenty rows (beginning with the 41st row), ordered by 3value in the salary column of the collection-derived 3table:

            SELECT emp_id, emp_name, emp_salary
3            FROM  TABLE(MULTISET(SELECT SKIP 40 LIMIT 20 id, name, salary
3                                             FROM e1, e2 
3                                             WHERE e1.id = e2.id ORDER BY salary )) 
3                AS etab(emp_id, emp_name, emp_salary);