I am trying to calculate difference between two dates and display the difference as days in the output. But I keep getting this error in Oracle Live sql:
ORA-06550: line 11, column 12:
PLS-00382: expression is of wrong type
Here is the code:
create table borrower(
roll_no number,
date_of_issue date,
name_of_book varchar(20),
status varchar(10)
);
insert into borrower values(1,to_date('02-JAN-2022'),'dbms','issued');
insert into borrower values(2,to_date('10-JAN-2022'),'cns','issued');
insert into borrower values(3,to_date('17-JAN-2022'),'spos','issued');
insert into borrower values(4,to_date('26-JAN-2022'),'toc','issued');
create table fine(
roll_no number,
current_date date,
amount number
);
insert into fine values(1,to_date('14-FEB-2022'),null);
insert into fine values(2,to_date('14-FEB-2022'),null);
insert into fine values(3,to_date('14-FEB-2022'),null);
insert into fine values(4,to_date('14-FEB-2022'),null);
DECLARE
roll_counter number:=1;
initial_date date;
final_date date;
date_calc date;
BEGIN
loop
select date_of_issue into initial_date from borrower where roll_no=roll_counter;
select current_date into final_date from fine where roll_no=roll_counter;
date_calc:=final_date-initial_date;
dbms_output.put_line(date_calc);
roll_counter:=roll_counter 1;
exit when roll_counter>4;
end loop;
END;
/
drop table borrower;
drop table fine;
Can someone help me sort out this error. Thank you in advance.
CodePudding user response:
Line 11 is this:
date_calc:=final_date-initial_date;
All three expressions in this formulas are declared as data type date
.
Now do you see the problem? If you don't, think about this: what date
is to_date('17-JAN-2022')
minus to_date('11-DEC-2021')
?
Look again at your problem description:
display the difference as days in the output.
What does that mean? Difference as days? What data type should that be?