PL/SQL procedure - SQL statement ignored error

Question

I'm doing tutorial from website http://www.plsqltutorial.com/plsql-procedure/. I have run the code on apex:

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
    UPDATE EMPLOYEES
    SET salary = salary + salary * in_percent / 100
    WHERE employee_id = in_employee_id;
END;

but I got error:

Error at line 6: PL/SQL: SQL Statement ignored

4. ) IS
5. BEGIN
6.  UPDATE EMPLOYEES
7.  SET salary = salary + salary * in_percent / 100
8.  WHERE employee_id = in_employee_id;

I have checked and table employees is there. What is the problem and how to fix it?

Answer 1

WHERE employee_id = in_employee_id;

in_employee_id is not declared, neither is it a parameter. The function definition says the parameter is in_employee so your code block should be

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
    UPDATE EMPLOYEES
    SET salary = salary + salary * in_percent / 100
    WHERE employee_id = in_employee;
END;

---

Looking at the article, I see that you've made a typo while creating the function, the function declaration as per the article is

 CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,

So, if you change your code to the above, no changes are required to the update statement.

Answer 2

To avoid such typos, it is better to use Dot Notation (or namespaces) instead of the prefixes. In the context of a procedure, this is the name of the procedure.

Check out the following code:

create or replace procedure adjust_salary(
    employee_id hr.employees.employee_id%type, percent number) is
begin
    update hr.employees set 
        salary = salary + salary * percent / 100
    where employee_id = adjust_salary.employee_id;
end;
/
Procedure ADJUST_SALARY compiled