Re: compilation error in plsql

On Apr 8, 2:10 am, chris <lazyboy..._at_yahoo.com> wrote:
> 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.

And one more note: when returning booleans, it's shorter and cleaner to do it like this:

RETURN (sal > avg_sal);

Here you evaluate the condition and return the result of the evaluation in one statement. For numeric output, your return statement could be this:

RETURN CASE WHEN sal > avg_sal THEN 1 ELSE 0 END;

Actually, it's a matter of personal preference and coding style, but less statements usually produce more maintainable and readable code (and save you a few keystrokes and a few bytes of storage.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Apr 08 2008 - 06:30:14 CDT