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 A,
(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: Table or view does not exist
line:46
Text:from Dba_objects A,
Solutions
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