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.

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

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

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

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