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;

MONTH_DISPLAY        MONTH_VALUE
-------------------- ----------------------
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
WWV_FLOW_HOURS_12
WWV_FLOW_HOURS_24
WWV_FLOW_MINUTES
WWV_FLOW_MINUTES_5
...
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', 
                               'WWV_FLOW_MONTHS_MON', 
                               'FLOWS_030000'
                             ) as the_ddl
from    dual;

THE_DDL
----------------------
select "MONTH_DISPLAY","MONTH_VALUE" 
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', 
                               'WWV_FLOW_MONTHS_MON_TEMP', 
                               'FLOWS_030000'
                             ) as the_ddl
from    dual;

THE_DDL
----------------------
select to_char(to_date(to_char(rownum,'00'),'MM'),'Mon') month, 
       rownum value 
from   wwv_flow_dual100
where  rownum < 13
union all
select '                    ', 
       20 
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: