Re: Using Calandar

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Tue, 16 Nov 1999 20:57:21 -0800
Message-ID: <80t8vd$k3p$1@inet16.us.oracle.com>

You can use something like :

/**************

• getday.sql * **************/

/*

• SQL*Plus set commands */

SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF SELECT TO_CHAR(TO_DATE('&1-&2-&3', 'DD-MON-YYYY'), 'fmDay') FROM dual;

/*

• undo SQL*Plus set commands */ SET HEADING ON SET VERIFY ON SET FEEDBACK ON

/****************

• getmonth.sql * ****************/

/*

• SQL*Plus set commands */ SET HEADING OFF SET FEEDBACK OFF SET VERIFY OFF SET NUMWIDTH 3

/*

• create required tables */ CREATE TABLE calendar( sun NUMBER(2), mon NUMBER(2), tue NUMBER(2), wed NUMBER(2), thu NUMBER(2), fri NUMBER(2), sat NUMBER(2));

CREATE TABLE monthname(
name VARCHAR2(12));

/*

• PL/SQL block that populates the calendar table */ DECLARE TYPE DateTableType IS TABLE OF NUMBER(2) INDEX BY BINARY_INTEGER;

  week_tab   DateTableType;         /* table to store dates for a week */
  i          BINARY_INTEGER := 0;   /* index to table */
  first      VARCHAR2(12);          /* first day of the month */
  numericDay NUMBER(2);             /* numeric indicator of above */
  lastDay    NUMBER(2);             /* date of last day of the month */
  dayCount   NUMBER(2) := 1;        /* current date in the month */

BEGIN

/*

• find which day of the week the first of the month falls on */ SELECT TO_CHAR(TO_DATE('01-&&1-&&2', 'DD-YYYY-MON'), 'fmDay') INTO first FROM dual;

/*

• translate this into a number */

  SELECT DECODE(first, 'Sunday', 1, 'Monday', 2, 'Tuesday', 3, 'Wednesday', 4,

                'Thursday', 5, 'Friday', 6, 7)

    INTO numericDay
    FROM dual;

/*

• get the date of the last day of the month */ SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('01-&&1-&&2', 'DD-YYYY-MON')), 'DD')) INTO lastDay FROM dual;

/*

• title of display */ INSERT INTO monthname VALUES(UPPER(TO_CHAR(TO_DATE('01-&&1-&&2', 'DD-YYYY-MON'), 'fmMonth')));

/*

• insert first row */ FOR i IN 1..7 LOOP IF numericDay > i THEN week_tab(i) := NULL; ELSE week_tab(i) := dayCount; dayCount := dayCount + 1; END IF; END LOOP;

  INSERT INTO calendar VALUES(

    week_tab(1),
    week_tab(2),
    week_tab(3),
    week_tab(4),
    week_tab(5),
    week_tab(6),
    week_tab(7));

/*

• insert other rows */ LOOP EXIT WHEN dayCount > lastDay; FOR i IN 1..7 LOOP IF dayCount > lastDay THEN week_tab(i) := NULL; ELSE week_tab(i) := dayCount; END IF; dayCount := dayCount + 1; END LOOP;

    INSERT INTO calendar VALUES(

      week_tab(1),
      week_tab(2),
      week_tab(3),
      week_tab(4),
      week_tab(5),
      week_tab(6),
      week_tab(7));

  END LOOP;

/*

• blank line at the end */ INSERT INTO calendar VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

END;
/

/*

• display results */ SELECT * FROM monthname; SET HEADING ON SELECT * FROM calendar;

/*

• clean up */ DROP TABLE calendar; DROP TABLE monthname;

/*

• undo SQL*Plus set commands */ SET FEEDBACK ON SET VERIFY ON SET NUMWIDTH 10

/***************

• getyear.sql * ***************/

/* call getmonth.sql 12 times */

START getmonth &&1 jan
START getmonth &&1 feb
START getmonth &&1 mar
START getmonth &&1 apr
START getmonth &&1 may
START getmonth &&1 jun
START getmonth &&1 jul
START getmonth &&1 aug
START getmonth &&1 sep
START getmonth &&1 oct
START getmonth &&1 nov
START getmonth &&1 dec

"The Views expressed here are my own and not necessarily those of Oracle Corporation"
<ould_at_my-deja.com> wrote in message news:80riua$r4s$1_at_nnrp1.deja.com...

> Hello all Gurous,
>
> My question is just how I can create a calandar
> simular to that of SCOTT EMP table in WebDB.
> Or  how I can change that of SCOTT for my
> proper needs.
>
> I Creating the table below using standard
> SQL*PLUS,
> but it do not work as a SCOTT Calandar.
>
>
> January, 1999
> ---------------
> |   01 |   02 |  03 |......
> ................................
> ---------------
> Febriary, 1999
> ---------------
> |   01 |   02 |  03 |......
> ................................
> ---------------
> ...etc.
>
> with the possibility to add a field for comment
> in each day.
>
> Any attempt to modify Scoot Calandars
> to intoduce my proper fields and data fails
>
> Please escuse errors and english.
>
> Thank You.
>
> Ould
>
>
> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Tue Nov 16 1999 - 22:57:21 CST