I am creating a stored procedure that calculates the age of an employee when given the SSN. The table employee_age has 4 columns.(ssn,bdate,age,date_calculated) I am very close. My SP compiles but the age I am calculating is not going into the age column of the employee_age table and neither is the sysdate which is suppose to go into the date_calculated column. Any ideas why they aren't being put into the employee_age table? BTW the employee-age table already has SSN and bdate values. The age and date_calculated columns are empty. CREATE OR REPLACE PROCEDURE agecalc ( p_empSSN IN employee_Age.ssn%type) AS v_empbdate employee_Age.bdate%type; v_empdatec employee_age.date_calculated%type; v_empage employee_Age.age%type; BEGIN SELECT bdate into v_empbdate from employee_age WHERE ssn = p_empSSN; v_empdatec := TRUNC(SYSDATE); v_empage := Floor(( TRUNC(SYSDATE) - TRUNC(v_empbdate) )/365.25); COMMIT; END agecalc; / show errors exec agecalc ('123456789')
My syntax might be wrong - I'm not an Oracle developer - but you need an UPDATE statement in your proc, after you calculate v_empage. UPDATE employee_age SET age = v_empage, date_calculated = TRUNC(SYSDATE) WHERE ssn = p_empSSN; But why not do it all in one go? Something like this? CREATE OR REPLACE PROCEDURE agecalc ( p_empSSN IN employee_Age.ssn%type) AS BEGIN UPDATE employee_age SET age = Floor(( TRUNC(SYSDATE) - TRUNC(v_empbdate) )/365.25) date_calculated = TRUNC(SYSDATE) WHERE ssn = p_empSSN; COMMIT; END agecalc Take it for what it is - an attempt from someone who uses SQL every day but haven't used Oracle since a very basic database course 15 years ago.