Returning Multiple Hard-Coded Rows in Oracle Database

 Sometimes dummy and hard-coded vales might be needed for testing or some other purposes. We can return single rows easily with "select ...... from dual". But if we want several rows, special functions might be useful for it since "unioning" rows are not really useful and simple.

Simple example is below:

select column_value "mail" from table(sys.odcivarchar2list('alex@mymail.com','sally@mymail.com','campbell@mymail.com');

This will return a column with three rows as intended.