23 February, 2012

Joke with Oracle

We always use to_char(date,'day') to get day name of in_date in oracle PLSQL.

In my work I want to do below task.

If day of given day is friday then don't execute procedure otherwise execute.
So I wrote below code, You can check it and expect result

DECLARE
   LC$CURR_DAY    VARCHAR2 (10);
   LD$CURR_DATE   DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday
BEGIN
   LC$CURR_DAY := TO_CHAR (LD$CURR_DATE, 'day');
   DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY);

   IF LC$CURR_DAY= 'friday'
   THEN
      DBMS_OUTPUT.PUT_LINE ('It is weekend !!!, Go to Bed');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('You should do your best at work');
   END IF;
END;


I expect the output result will be
friday  
It is weekend !!!, Go to Bed

Ooooops!! the output result was
friday  
You should do your best at work

So i began debugging  my code and I replace below statement
   DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY);
with
DBMS_OUTPUT.PUT_LINE ('*' || LC$CURR_DAY || '*');
an run code again the result was as below
*friday   *
You should do your best at work

I noticed that friday has empty spaces in end of word, so I used RTRIM function to clear this empty space as below
   LC$CURR_DAY := RTRIM (TO_CHAR (LD$CURR_DATE, 'day'));
Now The code must be like this
DECLARE
   LC$CURR_DAY    VARCHAR2 (10);
   LD$CURR_DATE   DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday
BEGIN
   LC$CURR_DAY := RTRIM (TO_CHAR (LD$CURR_DATE, 'day'));
   DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY );

   IF LC$CURR_DAY = 'friday'
   THEN
      DBMS_OUTPUT.PUT_LINE ('It is weekend !!!, Go to Bed');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('You should do your best at work');
   END IF;
END;
Now the result is (That what we expected)
friday
It is weekend !!!, Go to Bed

So at final I want to say
Not always your expectation is true, So always debug your code.

Mahmoud Ahmed El-Sayed
Recommended Post Slide Out For Blogger