PL/SQL Code Issue with Records and Date Calculation Not Functioning Properly

Data and Databases
1

I’m working on a PL/SQL script that finds the employee with the longest service time from a staff table. The program should calculate their years of service and display all their details using records and composite data types.

My code runs without errors but the service calculation part returns empty. I’m trying to use MONTHS_BETWEEN function to find the difference between current date and hire date. The logic seems right but something is wrong with how I’m handling the variables in my record structure.

The expected output should show something like:

Service Time: 9113.5 days or 303.8 months

Here’s my current code:

SET SERVEROUTPUT ON
DECLARE
  TYPE staff_record IS RECORD
    (
    service_years       NUMBER(4, 1),
    start_date          staff.start_date%TYPE,
    staff_row           staff%ROWTYPE
    );
    my_staff_rec        staff_record;

BEGIN

SELECT *  INTO my_staff_rec.staff_row
 FROM
 (
     SELECT * 
     FROM  staff
     ORDER BY start_date
 )
 WHERE rownum = 1;

my_staff_rec.service_years := 
    TRUNC(MONTHS_BETWEEN(SYSDATE, my_staff_rec.start_date), 1);

DBMS_OUTPUT.PUT_LINE('Senior employee details: '
|| my_staff_rec.staff_row.emp_id
|| '  ' || my_staff_rec.staff_row.fname 
||  '  ' || my_staff_rec.staff_row.lname );
DBMS_OUTPUT.PUT_LINE('Started on:  ' || my_staff_rec.staff_row.start_date);
DBMS_OUTPUT.PUT_LINE('Email:  ' || my_staff_rec.staff_row.email_addr);
DBMS_OUTPUT.PUT_LINE('Contact:  ' || my_staff_rec.staff_row.contact_num);
DBMS_OUTPUT.PUT_LINE('Service time: ' || my_staff_rec.service_years );
END;
/

The output shows all employee info correctly but the service time field is blank. What am I missing in my variable assignment or calculation?

2

you’re referencing the wrong field in your months_between calc. you populated staff_row.start_date but then used just start_date which is null. should be my_staff_rec.staff_row.start_date in the calculation, not my_staff_rec.start_date.

3

Ah, I see the issue! you’re using my_staff_rec.start_date but you never populated that field - you only filled my_staff_rec.staff_row.start_date. Try MONTHS_BETWEEN(SYSDATE, my_staff_rec.staff_row.start_date) instead. What value does my_staff_rec.start_date show when you debug it?

4

You’ve got a variable reference error in your service calculation. You’re trying to calculate months between SYSDATE and my_staff_rec.start_date, but that field never gets initialized. Your SELECT statement only fills the staff_row part of your record - it doesn’t touch the standalone start_date field. Fix it by changing your calculation to reference the right field: my_staff_rec.service_years := TRUNC(MONTHS_BETWEEN(SYSDATE, my_staff_rec.staff_row.start_date), 1);. This way you’re actually using the hire date from your database query instead of an empty variable.