pl sql how to get the day of the week for oracle db date

The Oracle function for this is TO_DATE with the 'D' format model:

SQL> select to_char (date '2011-07-24', 'D') d from dual;

D
-
7

As you can see, this returns 7 for Sunday, not 1, when I run it. The value returned varies according to your NLS settings. If necessary you could do this to get what you want:

SQL> select to_char (date '2011-07-24'+1, 'D') d from dual;

D
-
1

More details about Oracle's date format models can be found here

just you have to write to_char(your_date_column_name,'D') it will give the same answer what you have asked

just click here for more details