rem static lists

rem alphabet

with alphabet as
( select chr(64+rownum) letter
  from   dual
  connect
  by     rownum < 27
)
select letter
from   alphabet


rem months

with months as
( select 0 + rownum month_num
  ,      to_char( to_date('01-'||rownum||'-2010', 'DD-MM-YYYY'), 'MONTH') month_name
  from   dual
  connect 
  by     level < 13
) 
select months.month_name
from   months

rem days of week

with days as
( select to_char( to_date(rownum||'-01-2010', 'DD-MM-YYYY'), 'DAY') day_name
  from   dual
  connect 
  by     level < 8
) 
select day_name
from   days


rem numbers 1..10 (or any other range), also 

with range as
( select 5  lowend
  ,      19 highend
  from   dual
)  
, numbers as
( select lowend+  (level-1) num
  from   range
  connect
  by     level <= (highend - lowend + 1)
)
select num
from   numbers


rem  spell out

with range as
( select 5  lowend
  ,      19 highend
  from   dual
)  
, numbers as
( select lowend+  (level-1) num
  from   range
  connect
  by     level <= (highend - lowend + 1)
)
select to_char( to_date( num,'J' ), 'JSP')
from   numbers

rem any domain set

create type string_table as table of varchar2(1000)
/

with domain as
( select column_value value
  from   table( string_table('MALE', 'FEMALE', 'UNKNOWN'))
)
select value
from   domain



rem  internationalize to_char(date, 'DAY', 'nls_date_language=german')

with languages as
( select column_value language
  from   table( string_table('dutch', 'english', 'german', 'italian', 'spanish'))
)
, range as
( select level month_num
  from   dual
  connect
  by     level < 13
)  
, months as
( select month_num
  ,      to_char( to_date('01-'||month_num||'-2010', 'DD-MM-YYYY'), 'MONTH', 'nls_date_language='||language) month_name
  ,      language
  from   range
         cross join
		 languages
) 
select months.month_name
,      language
from   months

rem days of week

with languages as
( select column_value language
  from   table( string_table('dutch', 'english', 'german', 'italian', 'spanish'))
)
, range as
( select level day_num
  from   dual
  connect
  by     level < 8
)  
, days as
( select day_num
  ,      to_char( to_date(day_num||'-01-2010', 'DD-MM-YYYY'), 'DAY', 'nls_date_language='||language) day_name
  ,      language
  from   range
         cross join
		     languages
) 
select days.day_name
,      language
from   days



rem  frequently with partition outer join