CODEWITHSUNDEEP
oracle-databasefunctiondatecompiler-errorssubtraction
MaterialGirl
MaterialGirl

Reputation: 393

Oracle - PLS-00382: expression is of wrong type in subtracting dates

I have troubles compiling the following function in Oracle

CREATE OR REPLACE FUNCTION fn1
return binary_double
as
Fecha  DATE;
Dias binary_double;
begin
dbms_output.put_line(CAST(TRUNC(CURRENT_DATE,'DD') AS DATE) - cast(current_timestamp as date));
return Dias;
end;

It results in Error

Error(8,27): PLS-00382: expression is of wrong type

However running the same expression outside of function body 

select CAST(TRUNC(CURRENT_DATE,'DD') AS DATE) - cast(current_timestamp as date) from dual

gives the expected result - number of days between two dates

-0.0607060185185185185185185185185185185185

Is there any ideas on what could be wrong about the function?

Upvotes: 1

Views: 875

Answers (2)

Husqvik
Husqvik

Reputation: 5809

It seems that PL/SQL has problem with a type casted to the same type.

CURRENT_DATE returns DATE data type so you cast it to the same type. It works in SQL but not in PL/SQL. Just remove the casting:

CREATE OR REPLACE FUNCTION fn1
return binary_double
as
Fecha  DATE;
Dias binary_double;
begin
dbms_output.put_line(TRUNC(CURRENT_DATE,'DD') - cast(current_timestamp as date));
return Dias;
end;

Upvotes: 2

krokodilko
krokodilko

Reputation: 36087

Use:

begin
  dbms_output.put_line(TRUNC(CURRENT_DATE,'DD')  - cast( current_timestamp as date));
end;
/

Current_date returns a value of datatype DATE
Trunct also returns DATE.
So You are trying to cast DATE type to DATE type.

Upvotes: 2

Related Questions