How to write a calendar with pl/sql?

[Joe m]

Hi,everybody,I am trying to write a calendar with pl/sql and the program output like this
SUN MON TUE WED THU FRI SAT
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 31

but I don't konw how to start work,can you give me a useful instance?
Thanks.

[pragatiswain]

Hi,everybody,I am trying to write a calendar with pl/sql and the program output like this
SUN MON TUE WED THU FRI SAT
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 31

but I don't konw how to start work,can you give me a useful instance?
Thanks.

Hope the following will help you. I have not tested it. please correct the syntax incase of any syntax error.

DECLARE

MYDATE DATETIME;
MYDATEMAX datetime;

I_WEEK_DAY INTEGER; -- VARIES BETWEEN 1 - 7 (SUNDAY - SATURDAY)
C_DATE VARCHAR2;
I_MONTH INTEGER;
I_YEAR INTEGER;
CAL_STR1 VARCHAR2;
CAL_STR2 VARCHAR2;
CAL_STR3 VARCHAR2;
CAL_STR4 VARCHAR2;
CAL_STR5 VARCHAR2;
CAL_STR6 VARCHAR2;
CAL_STR7 VARCHAR2;

CAL_STR1 := 'SUN';
CAL_STR2 := 'MON';
CAL_STR3 := 'TUE';
CAL_STR4 := 'WED';
CAL_STR5 := 'THU';
CAL_STR6 := 'FRI';
CAL_STR7 := 'SAT';

SELECT SYSDATE INTO MYDATE FROM DUAL;

MYDATE := TO_DATE (TO_CHAR(MYDATE,'MM') ||'/01/' || TO_CHAR(MYDATE,'YYYY'), 'MM/DD/YYYY');
MYDATEMAX := TO_DATE (LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(MYDATE,'MM')) + 1),2,'0') ||'01' || TO_CHAR(MYDATE,'/YYYY'), 'MM/DD/YYYY');

WHILE (MYDATE < MYDATEMAX)
LOOP

SELECT TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'D')),
TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'DD'),
TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'MM')),
TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'YYYY'))
INTO I_WEEK_DAY, C_DATE, I_MONTH, I_YEAR FROM DUAL;

IF (I_WEEK_DAY = 1) THEN
CAL_STR1 := CAL_STR1 || ' ' || C_DATE;
ELSE IF (I_WEEK_DAY = 2) THEN
CAL_STR2 := CAL_STR2 || ' ' || C_DATE;
ELSE IF (I_WEEK_DAY = 3) THEN
CAL_STR3 := CAL_STR3 || ' ' || C_DATE;
ELSE IF (I_WEEK_DAY = 4) THEN
CAL_STR4 := CAL_STR4 || ' ' || C_DATE;
ELSE IF (I_WEEK_DAY = 5) THEN
CAL_STR5 := CAL_STR5 || ' ' || C_DATE;
ELSE IF (I_WEEK_DAY = 6) THEN
CAL_STR6 := CAL_STR6 || ' ' || C_DATE;
ELSE
CAL_STR7 := CAL_STR7 || ' ' || C_DATE;
END IF;

MYDATE := MYDATE + 1;

END LOOP;

Set the application font to Arial size 10 for proper alignment

[Joe m]

Thanks for your direction though I have a lot of unstudied question all the same

[balveer85y]

But while trying to compile it , it is showing error:

CAL_STR1 := 'SUN';
*

ERROR at line 18:
ORA-06550: line 18, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
The symbol "<an identifier>" was substituted for "=" to continue.
ORA-06550: line 19, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national chara
ORA-06550: line 20, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national chara
ORA-06550: line 21, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an iden




Please reply me

Thanks in advance
Balveer

[debasisdas]

try the following sample sql

with x
      as (
   select *
     from (
   select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
          to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
          to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
          to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
          to_char(sysdate,'mm') mth
    from dual
   connect by level <= 31
         )
   where curr_mth = mth
  )
  select max(case dw when 2 then dm end) Mon,
         max(case dw when 3 then dm end) Tue,
         max(case dw when 4 then dm end) Wed,
         max(case dw when 5 then dm end) Thu,
         max(case dw when 6 then dm end) Fri,
         max(case dw when 7 then dm end) Sat,
         max(case dw when 1 then dm end) Sun
    from x
   group by wk
   order by wk

[harikrishnad]

DECLARE
MYDATE DATE;
MYDATEMAX date;
I_WEEK_DAY number(15); -- VARIES BETWEEN 1 - 7 (SUNDAY - SATURDAY)
C_DATE VARCHAR2(50);
I_MONTH number(15);
I_YEAR number(15);
CAL_STR1 VARCHAR2(50);
CAL_STR2 VARCHAR2(50);
CAL_STR3 VARCHAR2(50);
CAL_STR4 VARCHAR2(50);
CAL_STR5 VARCHAR2(50);
CAL_STR6 VARCHAR2(50);
CAL_STR7 VARCHAR2(50);
begin
CAL_STR1 := 'SUN';
CAL_STR2 := 'MON';
CAL_STR3 := 'TUE';
CAL_STR4 := 'WED';
CAL_STR5 := 'THU';
CAL_STR6 := 'FRI';
CAL_STR7 := 'SAT';
SELECT SYSDATE INTO MYDATE FROM DUAL;
MYDATE := TO_DATE (TO_CHAR(MYDATE,'MM') ||'/01/' || TO_CHAR(MYDATE,'YYYY'), 'MM/DD/YYYY');
MYDATEMAX := TO_DATE (LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(MYDATE,'MM')) + 1),2,'0') ||'01' || TO_CHAR(MYDATE,'/YYYY'), 'MM/DD/YYYY');
WHILE (MYDATE < MYDATEMAX)
LOOP
SELECT TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'D')),
TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'DD'),
TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'MM')),
TO_NUMBER(TO_CHAR(TO_DATE(MYDATE,'MM/DD/YYYY'),'YYYY'))
INTO I_WEEK_DAY, C_DATE, I_MONTH, I_YEAR FROM DUAL;
IF (I_WEEK_DAY = 1) THEN
CAL_STR1 := CAL_STR1 || ' ' || C_DATE;
ELSIF (I_WEEK_DAY = 2) THEN
CAL_STR2 := CAL_STR2 || ' ' || C_DATE;
ELSIF (I_WEEK_DAY = 3) THEN
CAL_STR3 := CAL_STR3 || ' ' || C_DATE;
ELSIF (I_WEEK_DAY = 4) THEN
CAL_STR4 := CAL_STR4 || ' ' || C_DATE;
ELSIF (I_WEEK_DAY = 5) THEN
CAL_STR5 := CAL_STR5 || ' ' || C_DATE;
ELSIF (I_WEEK_DAY = 6) THEN
CAL_STR6 := CAL_STR6 || ' ' || C_DATE;
ELSE
CAL_STR7 := CAL_STR7 || ' ' || C_DATE;
END IF;
MYDATE := MYDATE + 1;
dbms_output.put_line(mydate);
END LOOP;
end;