Re: What happens to this code

From: <fitzjarrell_at_cox.net>
Date: 26 Feb 2007 08:23:58 -0800
Message-ID: <1172507038.372566.318490@a75g2000cwd.googlegroups.com>

On Feb 25, 11:51 am, surajkum..._at_gmail.com wrote:
> Examine this code:
> CREATE OR REPLACE STORED FUNCTION get_sal
> (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)
> RETURN NUMBER
> 1z0-147
> Leading the way in IT testing and certification tools,www.testking.com
> - 37 -
> IS
> v_salary NUMBER;
> v_raise NUMBER(8,2);
> BEGIN
> SELECT salary
> INTO v_salary
> FROM employees
> WHERE employee_id = p_employee_id;
> v_raise := p_raise_amt * v_salary;
> RETURN v_raise;
> End;
>
> What happens after execution of this code.

SQL> CREATE OR REPLACE STORED FUNCTION get_sal   2 (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)   3 RETURN NUMBER
  4 IS
  5 v_salary NUMBER;
  6 v_raise NUMBER(8,2);
  7 BEGIN
  8 SELECT salary
  9 INTO v_salary
 10 FROM employees
 11 WHERE employee_id = p_employee_id;
 12 v_raise := p_raise_amt * v_salary;
 13 RETURN v_raise;
 14 End;
 15 /

CREATE OR REPLACE STORED FUNCTION get_sal

                  *

ERROR at line 1:
ORA-00922: missing or invalid option

SP2-0734: unknown command beginning "v_raise NU..." - rest of line ignored.
WHERE employee_id = p_employee_id;

                    *

ERROR at line 5:
ORA-06550: line 5, column 21:
PL/SQL: ORA-00904: "P_EMPLOYEE_ID": invalid identifier ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored

ORA-06550: line 6, column 1:
PLS-00201: identifier 'V_RAISE' must be declared
ORA-06550: line 6, column 1:

PL/SQL: Statement ignored
ORA-06550: line 7, column 1:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored

Remove 'STORED' and your function creates. After that, what happens? I suggest you run it and find out.

David Fitzjarrell Received on Mon Feb 26 2007 - 10:23:58 CST