This is my query.
ALTER TABLE My_employees ADD (Age number(3) GENERATED ALWAYS AS ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)/12)+23 VIRTUAL)
Ok - and that is supposed to compute 'Age'?Is there anything wrong in my query.
Yes
1. How can 'age' be based on someone's 'hire_date'?
2. How can 'age' be computed accurately by adding '23' to the result?
That 'correct' answer may appear to work fine since it can give the 'correct' answer a lot of the time. After all, even if SYSDATE changes during the execution of the query you are only concerned with MONTHS and the month of sysdate is rarely going to change during the execution of one query.
The sysdate 'month' might change if the query runs overnight. Even then your computed 'age' would only change by one month.
But it can also change if any CACHING occurs such as result set caching or function caching and 24x7 systems could cache data for a LONG time.
As others have stated specifying DETERMINISTIC for a function that is NOT poses a risk. It is your database so you are free to take whatever level of risk you desire just as folks that do NOT using archiving have decided to take risks.