Calculating and store derived attribute in Oracle Sql Developer

Question

I am just trying to calculate and store age attribute derivedly from dob(date of birth). How can ı do that in Oracle SQL Developer?

CREATE TABLE Patient (
dob DATE,
age NUMBER(3));

I mean when de dob inserted like that 01/01/2000 the age will automatically calculated to 21.

Answer 1

No, you're planning to do it in a wrong way. Why would you so badly want to do that?

It's the same as people - who fill their profile - say "I've got 5 years of experience with Oracle". That was probably true at the moment of writing that text, but - is it still valid now? Two years later it should be "I've got 7 years of experience".

So, what kind of information would be storing fixed age into that column? That person was 21 years old at the moment of insert, but they won't stay forever young. Believe me, I know.

---

You were suggested to create a virtual column. Well, you can't. Why? Because - in order to calculate someone's age - you have to refer to today's date. Function that returns it is SYSDATE. Bad luck - it is not deterministic, which means that it doesn't return the same value each time you call it with the same parameters. It doesn't accept any, that's true, but it doesn't matter. If you try it, it'll fail:

SQL> create table patient
  2    (id             number constraint pk_pat primary key,
  3     date_of_birth  date not null,
  4     age            number generated always as
  5                      (round(months_between(sysdate, date_of_birth)/12, 0))
  6                      virtual
  7    );
                    (round(months_between(sysdate, date_of_birth)/12, 0))
                                          *
ERROR at line 5:
ORA-54002: only pure functions can be specified in a virtual column expression


SQL>

---

So, what can you do? You can create a view.

SQL> create table patient
  2    (id             number constraint pk_pat primary key,
  3     date_of_birth  date not null
  4    );

Table created.

SQL> create or replace view v_patient as
  2    select id,
  3           date_of_birth,
  4           round(months_between(sysdate, date_of_birth)/12, 0) as age
  5    from patient;

View created.

SQL>

Let's test it.

SQL> insert into patient (id, date_of_birth) values (1, date '2000-01-01');

1 row created.

SQL> select * from v_patient;

        ID DATE_OF_BI        AGE
---------- ---------- ----------
         1 01.01.2000         21

SQL>

Looks OK to me.