oracle.cal

Type created.

1* create or replace type ton is table of number;
Function created.

1 create or replace function seq(c number:=365) return ton pipelined as begin
2 for i in 0..c-1 loop pipe row(i); end loop;
3* end;
Function created.

1 create or replace function caseday(i number, d date) return varchar2 as begin
2 return case to_char(d,'d') when i then to_char(d,'dd') end;
3* end;

WK	MON	TUE	WED	THR	FRI	SAT	SUN
09/01-01				01	02	03	04
09/01-02	05	06	07	08	09	10	11
09/01-03	12	13	14	15	16	17	18
09/01-04	19	20	21	22	23	24	25
09/01-05	26	27	28	29	30	31
09/02-05							01
09/02-06	02	03	04	05	06	07	08
09/02-07	09	10	11	12	13	14	15
09/02-08	16	17	18	19	20	21	22
09/02-09	23	24	25	26	27	28
09/03-09							01
09/03-10	02	03	04	05	06	07	08
09/03-11	09	10	11	12	13	14	15
09/03-12	16	17	18	19	20	21	22
09/03-13	23	24	25	26	27	28	29
09/03-14	30	31
09/04-14			01	02	03	04	05
09/04-15	06	07	08	09	10	11	12
09/04-16	13	14	15	16	17	18	19
09/04-17	20	21	22	23	24	25	26
09/04-18	27	28	29	30
09/05-18					01	02	03
09/05-19	04	05	06	07	08	09	10
09/05-20	11	12	13	14	15	16	17
09/05-21	18	19	20	21	22	23	24
09/05-22	25	26	27	28	29	30	31
09/06-23	01	02	03	04	05	06	07
09/06-24	08	09	10	11	12	13	14
09/06-25	15	16	17	18	19	20	21
09/06-26	22	23	24	25	26	27	28
09/06-27	29	30
09/07-27			01	02	03	04	05
09/07-28	06	07	08	09	10	11	12
09/07-29	13	14	15	16	17	18	19
09/07-30	20	21	22	23	24	25	26
09/07-31	27	28	29	30	31
09/08-31						01	02
09/08-32	03	04	05	06	07	08	09
09/08-33	10	11	12	13	14	15	16
09/08-34	17	18	19	20	21	22	23
09/08-35	24	25	26	27	28	29	30
09/08-36	31
09/09-36		01	02	03	04	05	06
09/09-37	07	08	09	10	11	12	13
09/09-38	14	15	16	17	18	19	20
09/09-39	21	22	23	24	25	26	27
09/09-40	28	29	30
09/10-40				01	02	03	04
09/10-41	05	06	07	08	09	10	11
09/10-42	12	13	14	15	16	17	18
09/10-43	19	20	21	22	23	24	25
09/10-44	26	27	28	29	30	31
09/11-44							01
09/11-45	02	03	04	05	06	07	08
09/11-46	09	10	11	12	13	14	15
09/11-47	16	17	18	19	20	21	22
09/11-48	23	24	25	26	27	28	29
09/11-49	30
09/12-49		01	02	03	04	05	06
09/12-50	07	08	09	10	11	12	13
09/12-51	14	15	16	17	18	19	20
09/12-52	21	22	23	24	25	26	27
09/12-53	28	29	30	31

63 rows selected.

1 set pagesize 1000
2 @type
3 list
4 @seq
5 list
6 @caseday
7 list
8 with range as (select column_value+trunc(sysdate,'mm') d from table(seq))
9 select to_char(d,'yy/mm-iw') wk,
10 max(caseday(2,d)) mon, max(caseday(3,d)) tue,
11 max(caseday(4,d)) wed, max(caseday(5,d)) thr,
12 max(caseday(6,d)) fri, max(caseday(7,d)) sat, max(caseday(1,d)) sun
13 from range group by to_char(d,'yy/mm-iw')
14 order by 1
15 /
16 get secal
17* exit