With below three situation, we can use the pseudocolumn column_value to refer the column value.

• an XMLTABLE construct without the columns clause
• TABLE function to refer to a scalar nested table type
• virtual table with a single column returned by system

1. In the context of XMLTable, the value returned is of datatype XMLType. 

SELECT * FROM XMLTABLE(‘<a>123</a>‘);COLUMN_VALUE---------------------------------------<a>123</a>SELECT COLUMN_VALUE FROM (XMLTable(‘<a>123</a>‘));COLUMN_VALUE----------------------------------------<a>123</a>

2. for the table function with collection type,you can use column_value , the returned column_value type is same as the element type.

CREATE TYPE phone AS TABLE OF NUMBER;   /CREATE TYPE phone_list AS TABLE OF phone;/

SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t;COLUMN_VALUE------------          1          2          3

In a nested type, you can use the COLUMN_VALUE pseudocolumn in both the select list and the TABLE function:

SELECT t.COLUMN_VALUE FROM    TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;COLUMN_VALUE------------           1           2           3

The keyword COLUMN_VALUE is also the name that Oracle Database generates for the scalar value of an inner nested table without a column or attribute name,

as shown in the example that follows. In this context,column_value  is not a pseudocolumn, but an actual column name.

CREATE TABLE my_customers (   cust_id       NUMBER,   name          VARCHAR2(25),   phone_numbers phone_list,   credit_limit  NUMBER)   NESTED TABLE phone_numbers STORE AS outer_ntab   (NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);

Below is an example for build-in list odinumberlist 

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))

refer:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns004.htm

http://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle