Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between

Question

I am working on a simple PL/SQL block that asks the user their Date Of Birth and calculates their age. I've used the months_between divided by 12 conversion but I am having trouble with the date conversion and date input from the user for the substitution variable. Please ignore the v_birthday_year as that is another part of the code I have to integrate in later.

I'm not sure the format that the user has to type in, ie 05-07-1980, or 06 Mar 1978 that would be acceptable, and how to calculate their age from what they put in.

My desired program is to write a PL/SQL program to accept the user's birthdate in this format DD-MON-YYYY, and calculates the Age in with 1 Decimal place. So I need it calculated out to one decimal place too.

Here is what I have, but I'm not sure what format to type in the DOB at the substitution variable input and the actual calculation with one decimal place. I'm just trying to see where I'm going wrong and how to correct it.

SET SERVEROUTPUT ON
DECLARE
  --v_birthday_year         NUMBER(4)    := &v_birthday_year;
  v_dob                   DATE      := &v_dob 
  v_your_age              NUMBER(3, 1);
BEGIN
  v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;  
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/

Answer 1

You may use any of these (pure sql)

SELECT months_between(sysdate, user_birth_date) /12 FROM dual;

or

SELECT floor(months_between(sysdate, user_birth_date) /12) FROM  dual;

or

SELECT EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM user_birth_date) FROM dual

or

SELECT (sysdate - user_birth_date) / 365.242199 FROM dual

Hope this will help

Answer 2

All SQL examples from others and me are probably better thing to use unless you are learning something about PL/SQL. Here's what I came up with:

 DECLARE
  v_dob        DATE:= to_date('&v_dob', 'MM/DD/YYYY'); -- copy this line and you'll be fine
  v_your_age   NUMBER(3, 1);
BEGIN
  v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;  
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/

I passed 01/01/2010. Got this in output: 

 Your age is 3.1

Another SQL example:

SELECT empno, ename, hiredate
    , TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service  
 FROM scott.emp
/