Examine the following code:
The above code generates an error on execution.
What must you do to ensure that the code executes successfully?
A. Use the TO_DATE function in line 2.
B. Use the TO_DATE function in line 7.
C. Use the TO_NUMBER function in line 6.
D. Use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6.
Confirming correct answer is A
Original code is failing in line 2:
set serveroutput on
declare
date1 date:=’January 10, 2008′;
date2 date:=SYSDATE;
date_diff number;
begin
date_diff:=date2-date1;
dbms_output.put_line(‘Difference in dates is ‘|| date_diff);
end;
/
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 2
After adding TO_DATE function, code executes successfulyy.
set serveroutput on
declare
date1 date:=to_date(‘January 10, 2008′,’Month DD, YYYY’);
date2 date:=SYSDATE;
date_diff number;
begin
date_diff:=date2-date1;
dbms_output.put_line(‘Difference in dates is ‘|| date_diff);
end;
/
Difference in dates is 4157.364664351851851851851851851851851852
PL/SQL procedure successfully completed.
TO_NUMBER function is not required as result of two dates subtraction is a number.