Re: compilation error in plsql

From: chris <lazyboy_2k_at_yahoo.com>
Date: Mon, 7 Apr 2008 15:10:13 -0700 (PDT)
Message-ID: <5bcbe6e5-39fb-414a-ab78-b1baf311f173@1g2000prg.googlegroups.com>


On Apr 7, 1:28 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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

Thanks a lot David & Jeroen for a quick response. David, you are right on the spot. After I remove "additional comma" in my 1st sql select statement, it's working. Thanks again. Received on Mon Apr 07 2008 - 17:10:13 CDT

Original text of this message