Create type date_obj as object (
Month varchar2 (20 ),
Sun char (2 ),
Mon char (2 ),
Tue char (2 ),
Wed char (2 ),
Thu char (2 ),
Fri char (2 ),
Sat char (2)
);
/
Create type date_obj_tab as table of date_obj;
/
Create or replace function get_date_str (in_date in date default sysdate)
Return date_obj_tab
Pipelined is
Date_o date_obj: = date_obj (to_char (in_date, 'month '),
1,
1,
1,
1,
1,
1,
1 );
Cursor C1 is
Select sum (Case
When date_str = 'sun' then
Date_d
End) sun,
Sum (Case
When date_str = 'mon' then
Date_d
End) Mon,
Sum (Case
When date_str = 'tue 'then
Date_d
End) Tue,
Sum (Case
When date_str = 'wed' then
Date_d
End) wed,
Sum (Case
When date_str = 'thu' then
Date_d
End) Thu,
Sum (Case
When date_str = 'fri' then
Date_d
End) Fri,
Sum (Case
When date_str = 'sat 'then
Date_d
End) sat
From (select to_number (to_char (date_d, 'dd') date_d,
To_char (date_d, 'dy ') date_str,
To_number (to_char (date_d, 'ddd '))-
To_number (to_char (date_d, 'D') date_w
From (select trunc (date_d) + rownum-1 date_d
From dba_objects,
(Select trunc (in_date, 'mm') date_d from dual) B
Where rownum <=
To_number (to_char (last_day (date_d), 'dd '))))
Group by date_w;
Begin
For rs in C1 Loop
Date_o.sun: = Rs. Sun;
Date_o.mon: = Rs. Mon;
Date_o.tue: = Rs. Tue;
Date_o.wed: = Rs. Wed;
Date_o.thu: = Rs. Thu;
Date_o.fri: = Rs. Fri;
Date_o.sat: = Rs. Sat;
Pipe row (date_o );
End loop;
Return;
End;
Compilation errors for function landuser. get_date_str
Error: PL/SQL: ORA-00942: The table or view does not exist
Line: 46
Text: From dba_objects,
Solution
Connect sys/As sysdba;
Grant select on dba_objects to landuser;
Select * from table (get_date_str (sysdate-10 ));
Month su Mo tu we th fr SA
----------------------------------
March 1 2 3 4 5
March 6 7 8 9 10 11 12
March 13 14 15 16 17 18 19
March 20 21 22 23 24 25 26
March 27 28 29 30 31