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?

score 6 · Accepted Answer · answered Jan 15 '12 at 16:14

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.

@aretai and also, if you have the answer, could you provide it at your other question quite similar to this one: http://stackoverflow.com/q/8871447/452614 — Ludovic Kuty, Jan 17 '12 at 11:42

score 3 · Answer 2 · answered Oct 09 '20 at 11:46

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

score 2 · Answer 3 · answered Jan 15 '12 at 16:14

The parameter is in_employee but you're using in_employee_id in your update. Change to:

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;

score 0 · Answer 4 · answered Aug 26 '20 at 06:28

The parameter name "in_employee" is different while you are using different variable name "in_employee_id" in the query

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;

score 0 · Answer 5 · answered Jul 13 '21 at 12:54

In my case (Oracle SQL Developer 19.2, Oracle version 12c), I just had to save the procedure and the error was gone.

E.g., enter some key, delete it (the procedure wasn't changed, but now you can save it using Ctrl+s). After the save the error disappeared and I was able to run the procedure.

PL/SQL procedure - SQL statement ignored error

5 Answers5