Display Calender in SQL and PLSQL
Today I will present how to display calendar specific month from oracle SQL or PLSQL like below image
So I will present the solution to do this
First I will create Object type to handle week days( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
I will create collection to handle table of type WEEK_DAY because calendar view is table of week days.
I created GET_CLAENDAR function which takes two parameters (Month and year) and it reruns collection of WEEK_DAYS then you can use it to display calendar in SQL or PLSQL.
Now after finishing creating needed objects let's test the function and see output.
The output in DBMS Output Console
Thanks
So I will present the solution to do this
First I will create Object type to handle week days( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
CREATE OR REPLACE TYPE WEEK_DAY AS OBJECT
(SUN NUMBER (2),
MON NUMBER (2),
TUE NUMBER (2),
WED NUMBER (2),
THU NUMBER (2),
FRI NUMBER (2),
SAT NUMBER (2));
I will create collection to handle table of type WEEK_DAY because calendar view is table of week days.
CREATE OR REPLACE TYPE WEEK_DAYS AS TABLE OF WEEK_DAY;
I created GET_CLAENDAR function which takes two parameters (Month and year) and it reruns collection of WEEK_DAYS then you can use it to display calendar in SQL or PLSQL.
create or replace FUNCTION GET_CLAENDAR (IN_MONTH INTEGER, IN_YEAR INTEGER)
RETURN WEEK_DAYS
IS
LD$MONTH DATE;
LN$INDEX PLS_INTEGER := 1;
LT$WEEK_DAYS WEEK_DAYS;
CURSOR LCUR$DAYS (IN_DATE DATE)
IS
SELECT SUN,
MON,
TUE,
WED,
THU,
FRI,
SAT
FROM ( SELECT TO_CHAR (DT + 1, 'iw') WEEK,
MAX (
DECODE (TO_CHAR (DT, 'd'), '1', TO_CHAR (DT, 'fmdd')))
SUN,
MAX (
DECODE (TO_CHAR (DT, 'd'), '2', TO_CHAR (DT, 'fmdd')))
MON,
MAX (
DECODE (TO_CHAR (DT, 'd'), '3', TO_CHAR (DT, 'fmdd')))
TUE,
MAX (
DECODE (TO_CHAR (DT, 'd'), '4', TO_CHAR (DT, 'fmdd')))
WED,
MAX (
DECODE (TO_CHAR (DT, 'd'), '5', TO_CHAR (DT, 'fmdd')))
THU,
MAX (
DECODE (TO_CHAR (DT, 'd'), '6', TO_CHAR (DT, 'fmdd')))
FRI,
MAX (
DECODE (TO_CHAR (DT, 'd'), '7', TO_CHAR (DT, 'fmdd')))
SAT
FROM ( SELECT IN_DATE - 1 + ROWNUM DT
FROM DUAL
CONNECT BY LEVEL <= LAST_DAY (IN_DATE) - IN_DATE + 1)
GROUP BY TO_CHAR (DT + 1, 'iw'))
ORDER BY TO_NUMBER (WEEK);
BEGIN
LT$WEEK_DAYS := WEEK_DAYS ();
LD$MONTH := TO_DATE (IN_MONTH || '-' || IN_YEAR, 'MM-RRRR');
FOR LREC$DAYS IN LCUR$DAYS (LD$MONTH)
LOOP
LT$WEEK_DAYS.EXTEND;
LT$WEEK_DAYS (LN$INDEX) :=
WEEK_DAY(LREC$DAYS.SUN,
LREC$DAYS.MON,
LREC$DAYS.TUE,
LREC$DAYS.WED,
LREC$DAYS.THU,
LREC$DAYS.FRI,
LREC$DAYS.SAT);
LN$INDEX := LN$INDEX + 1;
END LOOP;
RETURN LT$WEEK_DAYS;
END GET_CLAENDAR;
Now after finishing creating needed objects let's test the function and see output.
Test in Oracle SQL
I will display calendar of June 2012 SELECT * FROM TABLE (GET_CLAENDAR (6, 2012));
Test in Oracle PLSQL
DECLARE
LT$WEEK_DAYS WEEK_DAYS;
BEGIN
LT$WEEK_DAYS := GET_CLAENDAR (6, 2012);
DBMS_OUTPUT.
PUT_LINE (
'Sun'
|| CHR (9)
|| 'Mon'
|| CHR (9)
|| 'Tue'
|| CHR (9)
|| 'Wed'
|| CHR (9)
|| 'Thu'
|| CHR (9)
|| 'Fri'
|| CHR (9)
|| 'Sat');
FOR I IN LT$WEEK_DAYS.FIRST .. LT$WEEK_DAYS.LAST
LOOP
DBMS_OUTPUT.
PUT_LINE (
RPAD (LT$WEEK_DAYS (I).SUN, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).MON, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).TUE, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).WED, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).THU, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).FRI, 3, ' ')
|| CHR (9)
|| RPAD (LT$WEEK_DAYS (I).SAT, 3, ' '));
END LOOP;
END;
The output in DBMS Output Console
Thanks