Re: compilation error in plsql

On Apr 7, 2:59 pm, chris <lazyboy..._at_yahoo.com> wrote:
> Hi,
>
> I'm a new learner of pl/sql programing & try to write a basic
> function; however, I can't figure out why my function is not compiling
> correctly.  Could someone pls let me know if you see where is the
> problem?  This is 10g & I'm writing it using iSQL*PLUS.
>
> CREATE OR REPLACE  FUNCTION check_sal (empno employees.employee_id
> %TYPE)
> RETURN boolean IS
>
>    dept_id   employees.department_id%TYPE;
>    sal         employees.salary%TYPE;
>    avg_sal   employees.salary%TYPE;
>
> BEGIN
>    SELECT salary, department_id, INTO sal, dept_id FROM employees
> WHERE employee_id = empno;
>    SELECT AVG(salary) INTO avg_sal FROM employees WHERE department_id
> = dept_id;
>
>    IF sal > avg_sal THEN
>       RETURN TRUE;
>    ELSE
>       RETURN FALSE;
>    END IF;
> END;
> /
> show errors
>
> Errors for FUNCTION CHECK_SAL:
>
> LINE/COL        ERROR
> 8/4     PL/SQL: SQL Statement ignored
> 8/34    PL/SQL: ORA-00936: missing expression
>
> TIA,
> -Chris

You have two errors: returning a boolean outside of a PL/SQL block and the extra comma after department_id in your select list. Fixing such issues gives you:

CREATE OR REPLACE FUNCTION check_sal (empno IN employees.employee_id %type)
RETURN number IS

   dept_id employees.department_id%TYPE;    sal employees.salary%TYPE;
   avg_sal employees.salary%TYPE;

BEGIN
   SELECT salary, department_id INTO sal, dept_id FROM employees    WHERE employee_id = empno;
   SELECT AVG(salary) INTO avg_sal FROM employees WHERE department_id

• dept_id;

   IF sal > avg_sal THEN

      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END;
/

And will produce the following output to a SQL*Plus prompt:

SQL> select check_sal(7743) from employees;

CHECK_SAL(7743)

              1
              1
              1
              1

SQL> Of course if you're planning on using this only within a PL/SQL block you can return a BOOLEAN and not produce an error.

David Fitzjarrell Received on Mon Apr 07 2008 - 15:28:52 CDT