To display a list of values as a table column

A number of system-defined types are defined by Oracle and need to be created by running the catodci.sql catalog script.

Below 2 types we can use for our purpose for displaying/using a list of varchars or numbers
as a table column

1). ODCIVarchar2List
Stores varrays of VARCHAR2s

select distinct column_value from table (sys.ODCIvarchar2list('hi','hello','hey','ha','haha'));

o/p:
COLUMN_VALUE
---------------
hi
hey
haha
hello
ha

2). ODCINumberList
Stores varrays of NUMBERs.

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

o/p:

COLUMN_VALUE

------------

           1

           2

           4

           5

           3

We can use these as temporary tables in a sql query, for example comparision purpose

e.g: 

select distinct column_value from table (sys.ODCIvarchar2list('hi','hello','hey','ha','haha',1))

intersect

select distinct to_char(column_value) from table (sys.ODCINumberList(1,2,3,4,5));

o/p:

COLUMN_VALUE

------------

           1