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://*.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle