Thursday, 23 August 2007

A native list of months

A while ago I blogged about making a list of months in an (APEX) application, for which I used a query with connect by. I got some feedback on that, and Gunnar Andersson informed me that such lists are actually provided with APEX as views. For example:

Select * from WWV_FLOW_MONTHS_MON;

-------------------- ----------------------
Jan 1
Feb 2
Mrt 3
Apr 4
Mei 5
Jun 6
Jul 7
Aug 8
Sep 9
Okt 10
Nov 11
Dec 12

12 rows selected

The same goes for
I was curious as to how Oracle defined these views, if they used the same connect by "trick", so I drilled down the DDL.

select  dbms_metadata.get_ddl( 'VIEW', 
                             ) as the_ddl
from    dual;

from   wwv_flow_months_mon_temp 
where  month_value < 13;

Seems I'll have to dig a little deeper:

select  dbms_metadata.get_ddl( 'VIEW', 
                             ) as the_ddl
from    dual;

select to_char(to_date(to_char(rownum,'00'),'MM'),'Mon') month, 
       rownum value 
from   wwv_flow_dual100
where  rownum < 13
union all
select '                    ', 
from   dual;

I don't really get why the "union all" is there, but anyway: like its name suggests, WWV_FLOW_DUAL100 is actually a table with one dummy column and a 100 rows with numbers for values. No fancy SQL, and probably more efficient.

No comments: