create or replace function getAvg(id1 IN number, id2 IN number) return number as
sal1 number;
sal2 number;
avg number;
BEGIN
select esal into sal1 from employees where eno = id1;
select esal into sal2 from employees where eno = id2;
avg := (sal1+sal2)/2;
return avg;
END;
/
When I try to compile the above code, I get compilation errors with following message:
Warning: Function created with compilation errors.
But when I replace avg after return with (sal1+sal2)/2 it compiles successfully.
>Solution :
That’s bad habits: never name your own objects, variables, whatever using reserved words or keywords. avg is a built-in function; rename the variable:
SQL> create or replace function getAvg(id1 IN number, id2 IN number)
2 return number
3 as
4 sal1 number;
5 sal2 number;
6 l_avg number;
7 BEGIN
8 select esal into sal1 from employees where eno = id1;
9 select esal into sal2 from employees where eno = id2;
10 l_avg := (sal1+sal2)/2;
11 return l_avg;
12 END;
13 /
Function created.
SQL> select * from employees;
ENO ESAL
---------- ----------
1 100
2 200
SQL> select getavg(1, 2) from dual;
GETAVG(1,2)
-----------
150
SQL>