CODEWITHSUNDEEP
sqloracle-databaseplsqlprocedure
Wesley van S
Wesley van S

Reputation: 127

Procedure invalid identifier when trying to use in SQL / PLSQL

i'm trying to make a very basic procedure on PLSQL but when i try to use it in SQL it returns invalid identifier. 

create or replace PROCEDURE YEARS_BETWEEN(date1 IN date , date2 IN date, p_result out number)
IS
    v_months number;
BEGIN
    v_months := months_between(date1, date2);

    p_result := TRUNC(v_months / 12, 0);
END years_between;

Can anyone tell me whats wrong?

SQL IS 

select YEARS_BETWEEN(GBDATUM, SYSDATE) as leeftijd  FROM medewerkers;

Upvotes: 1

Views: 265

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You need a function not a procedure if you want to call it in a select:

create or replace function years_between (in_date1 in date , in_date2 in date)
return number as
    v_months number;
begin
    v_months := months_between(date1, date2);

    return(trunc(v_months / 12, 0));
end;  --  years_between

Upvotes: 4

Related Questions