TO_CHAR(..., 'D')

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 ... ?

With to_char()

alter session set nls_territory=germany;
select to_char(sysdate,'DAY D') from dual;
TUESDAY 2

With decode()

select decode(to_char(sysdate, 'FMDAY', 'NLS_DATE_LANGUAGE=american'),'MONDAY', '1', 'TUESDAY', '2', '...')) from dual;

With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date '1000-01-01',7)+1 from dual;
2

How do I trunc date to current monday?
trunc(date, 'D') or here is with my solution with 1000-01-01:
select trunc((sysdate-date '1000-01-01')/7)*7+date '1000-01-01' from dual;

thanks to jan-marcel idea, I found that one
trunc(date,'IW') for current monday and date-trunc(date,'IW')+1 for day number

deterministic

if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic.

f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always.

f(y) = sysdate+y is non-deterministic. For a given y, the return values may vary.

OK, we cannot use sysdate in mviews. What else should I say?

Well, some are less evident to find out!

Some limit cases:
to_date('2000','YYYY') is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date '2000-01-01','DAY') is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date '2000-01-01','DAY','NLS_DATE_LANGUAGE=american') is deterministic
to_char(date '2000-01-01','D') is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp '2005-10-30 02:30:00 Europe/Zurich' is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.

I can always define a function as deterministic, at my own risk...

For exemple if I have t(id,gender) {1,male;2,female}, I could have a function

f(id)=select gender from t where t.id=f.id;

and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.

How old are you?

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age

trunc((to_char(sysdate,'YYYYMMDD')-to_char(birthdate,'YYYYMMDD'))/10000)

it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) to be 18 if I were born Feb 28th, 1990.