24 June, 2015

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)
 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

2 comments:

  1. thank your valuable content.we are very thankful to you.one of the recommanded blog.which is very useful to new learners and professionals.content is very useful for hadoop learners


    Best Spring Classroom Training Institute
    Best Devops Classroom Training Institute
    Best Corejava Classroom Training Institute
    Best Oracle Classroom Training Institute

    ReplyDelete
  2. Thanks For Sharing The Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The article Oracle Online Course

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...