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

9 comments:

  1. It is really bug.
    thnx for sharing this issue

    ReplyDelete
  2. welcom Mahmoud, thank you for your issues

    ReplyDelete
  3. Thank you for this helpful info.

    ReplyDelete
  4. Thank you for this helpful info.

    ReplyDelete
  5. and if the nls_lang of the client not AMERICAN the result well be :

    الجمعة

    so it's much better to solve it with specifying the NLS_LANG Addition to TO_CHAR Function .


    Regards
    Ibrahim Bakhsh

    ReplyDelete
    Replies
    1. To guarantee that to_char will return day in English, you should use below syntax
      TO_CHAR (LD$CURR_DATE, 'day', 'nls_date_language=english')

      Delete
  6. I would probably use TO_DATE ('17-02-2012', 'D')

    It gives the day of the week , an int value between 1 and 7.

    Always better to compare int values than strings :-)

    Anyway, the extra space is an issue ...

    regards,

    ReplyDelete
    Replies
    1. totally true , using number in comparing is faster than strings.

      But what I want to mention here is extra spaces in string value

      Delete
  7. THANK YOU FOR THIS HELP FULL INFO.

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