compilation error in plsql - comp.databases.oracle.server

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

chris 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 Your variable declarations should be preceded by the "DECLARE" keyword. HTH. -- Jeroen

On Apr 7, 2:59=A0pm, chris <lazyboy...@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. =A0Could someone pls let me know if you see where is the > problem? =A0This is 10g & I'm writing it using iSQL*PLUS. > > CREATE OR REPLACE =A0FUNCTION check_sal (empno employees.employee_id > %TYPE) > RETURN boolean IS > > =A0 =A0dept_id =A0 employees.department_id%TYPE; > =A0 =A0sal =A0 =A0 =A0 =A0 employees.salary%TYPE; > =A0 =A0avg_sal =A0 employees.salary%TYPE; > > BEGIN > =A0 =A0SELECT salary, department_id, INTO sal, dept_id FROM employees > WHERE employee_id =3D empno; > =A0 =A0SELECT AVG(salary) INTO avg_sal FROM employees WHERE department_id > =3D dept_id; > > =A0 =A0IF sal > avg_sal THEN > =A0 =A0 =A0 RETURN TRUE; > =A0 =A0ELSE > =A0 =A0 =A0 RETURN FALSE; > =A0 =A0END IF; > END; > / > show errors > > Errors for FUNCTION CHECK_SAL: > > LINE/COL =A0 =A0 =A0 =A0ERROR > 8/4 =A0 =A0 PL/SQL: SQL Statement ignored > 8/34 =A0 =A0PL/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 =3D empno; SELECT AVG(salary) INTO avg_sal FROM employees WHERE department_id =3D 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

On Apr 7, 1:28 pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote: > On Apr 7, 2:59 pm, chris <lazyboy...@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.

On Apr 8, 2:10 am, chris <lazyboy...@yahoo.com> wrote: > On Apr 7, 1:28 pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote: > > > > > On Apr 7, 2:59 pm, chris <lazyboy...@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

"The Boss" <usenet@No.Spam.Please.invalid> schreef in bericht news:47fa80ee$0$14345$e4fe514c@news.xs4all.nl... > chris 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 > > Your variable declarations should be preceded by the "DECLARE" keyword. > > HTH. > > -- > Jeroen > No they don't. Not within a procedure, I think it won't even compile. Shakespeare

Shakespeare wrote: > "The Boss" <usenet@No.Spam.Please.invalid> schreef in bericht >> Your variable declarations should be preceded by the "DECLARE" keyword. >> >> HTH. >> >> -- >> Jeroen >> > > No they don't. Not within a procedure, I think it won't even compile. > > Shakespeare > > Page 7-9 of the Oracle 7 Server Application Developers Guide: "Unlike in an anonymous PL/SQL block, you do not use the keyword DECLARE before the declarations of variables, cursors and exceptions in a stored procedure. In fact, it is an error to use it." No mention of an error code, just the fact that it is "an error to use it" -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up

"Frank van Bortel" <frank.van.bortel@gmail.com> schreef in bericht news:494db$47fca5c0$524b5c40$29580@cache6.tilbu1.nb.home.nl... > Shakespeare wrote: >> "The Boss" <usenet@No.Spam.Please.invalid> schreef in bericht >>> Your variable declarations should be preceded by the "DECLARE" keyword. >>> >>> HTH. >>> >>> -- >>> Jeroen >>> >> >> No they don't. Not within a procedure, I think it won't even compile. >> >> Shakespeare > Page 7-9 of the Oracle 7 Server Application Developers Guide: > "Unlike in an anonymous PL/SQL block, you do not use the > keyword DECLARE before the declarations of variables, > cursors and exceptions in a stored procedure. > In fact, it is an error to use it." > > No mention of an error code, just the fact that > it is "an error to use it" > > -- > > Regards, > Frank van Bortel > > Top-posting in UseNet newsgroups is one way to shut me up But it will generate a PLS-00103 when trying to compile it. Shakespeare -- To declare or not to declare, that's the question --