COLUMN_VALUE is a pseudocolumn used in a TABLE expression to retrieve data from a collection such as a nested table. The database returns a virtual table with a single column named COLUMN_VALUE, which contains the elements of the collection.
Supported clauses
Use COLUMN_VALUE in any of the following clauses within a TABLE expression:
| Clause | Usage |
|---|---|
SELECT list | Reference the column value in the output |
WHERE | Filter rows by element value |
HAVING | Filter groups by element value |
ORDER BY | Sort results by element value |
GROUP BY | Aggregate elements by value |
Examples
Retrieve all elements from a nested table
Define a nested table type and query its elements using COLUMN_VALUE.
CREATE TYPE user_names IS TABLE OF varchar2(100);
SELECT column_value FROM TABLE(user_names('Alice', 'Bob', 'Charlie'));Result:
column_value
--------------
Alice
Bob
Charlie
(3 rows)Filter elements with WHERE
Use COLUMN_VALUE in a WHERE clause to return only matching elements.
CREATE TYPE user_names IS TABLE OF varchar2(100);
SELECT column_value
FROM TABLE(user_names('Alice', 'Bob', 'Charlie', 'Alice', 'Alice', 'Bob', 'Charlie'))
WHERE column_value = 'Alice';Result:
column_value
--------------
Alice
Alice
Alice
(3 rows)Group, filter, and sort elements
Combine GROUP BY, HAVING, and ORDER BY to aggregate and sort collection elements.
SELECT column_value
FROM TABLE(user_names('Alice', 'Bob', 'Charlie', 'Alice', 'Alice', 'Bob', 'Charlie'))
GROUP BY column_value
HAVING column_value = 'Alice'
ORDER BY column_value;Result:
column_value
--------------
Alice
(1 row)