CODEWITHSUNDEEP
sqloracle-databaseoracle11g
mps88
mps88

Reputation: 33

Compilation error in Stored Procedure (Oracle SQL)

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.

Upvotes: 0

Views: 563

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

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>

Upvotes: 2

Related Questions