Cal Command Using PL/SQL

In this blog entry I like share a little script for generate a unix style cal command. It is coded using PL/SQL.


REM =================================================================
REM
REM Cal command unix style.
REM
REM USAGE: STA CAL <MONTH> <YEAR&>
REM EX:    STA CAL 12 2245
REM
REM CREATED : 22/10/2015
REM AUTHOR  : Antonio NAVARRO
REM
REM =================================================================
SET SERVEROUTPUT ON SIZE 100000
SET VER OFF
DECLARE
/*** Create a varray to containts the Months ***/
TYPE list_of_months IS VARRAY (12) OF VARCHAR2 (100);
Months list_of_months := list_of_months ('January', 'February', 'March','April', 'May', 'June','July', 'August', 'September','October', 'November', 'December');

/*** Create a varray to containts the numbers of each month ***/
TYPE list_of_numberofdays IS VARRAY (12) OF NUMBER (2);
Days list_of_numberofdays := list_of_numberofdays (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);

i        PLS_INTEGER;
d        PLS_INTEGER;
LineCal  VARCHAR2 (100);

FUNCTION DAY
(
  Month Number,
  Year  Number
) RETURN NUMBER IS
DayString    VARCHAR2 (10); -- WITH THIS FORMAT DD/MM/YYYY
DayOfTheWeek NUMBER;
BEGIN
  DayString := '01/' || Month || '/' || Year;
  SELECT TO_NUMBER (to_char(to_date(DayString,'dd/mm/yyyy'), 'D')) INTO DayOfTheWeek FROM DUAL;
  RETURN DayOfTheWeek;
END;

FUNCTION IsLeapYear
(
  year NUMBER
) RETURN BOOLEAN IS
BEGIN  
  IF (( MOD (year,4) = 0) AND ( MOD (year,100) != 0)) THEN RETURN TRUE; END IF;
  IF ( MOD (year,400) = 0) THEN  RETURN TRUE; END IF;
  RETURN FALSE;
END; /*** IsLeapYear ***/

BEGIN
  -- Check for leap year
  IF ((&1 = 2) AND (isLeapYear(&2))) THEN Days (2) := 29; END IF;

  -- Print header
  DBMS_OUTPUT.PUT_LINE ('.    ' ||  Months (&1) || ' ' || &2);
  DBMS_OUTPUT.PUT_LINE ('. S M Tu W Th F S');
  DBMS_OUTPUT.PUT_LINE ('. ');

  d := day (&1, &2);
  LineCal := '.';

  -- Print the calendar
  -- The first line
  i := 0;
  WHILE i < d LOOP
    i := i+1;
    LineCal := LineCal || ' ' ;
  END LOOP;

  -- The rest of lines
  i := 0;
  WHILE i <= days (&1) LOOP
    i := i + 1;    

    IF (i < 10) THEN -- add one more space if it is one digit
      LineCal := LineCal || ' ' || i;
    ELSE
      LineCal := LineCal || ' ' || i;
    END IF ;

    IF ((MOD ((i+d),7) = 0) OR (i = days (&1))) THEN 
      dbms_output.put_line (LineCal); 
      LineCal := '.';
    END IF;

  END LOOP;

 END; /*** PRINCIPAL ***/
 /

An example;

 
BBDD*ANTO> sta cal 11 2017
.   November 2017
.   S   M  Tu   W  Th   F   S
.
.               1   2   3   4
.   5   6   7   8   9  10  11
.  12  13  14  15  16  17  18
.  19  20  21  22  23  24  25
.  26  27  28  29  30

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s