CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
aretai
aretai

Reputation: 1641

PL/SQL procedure - SQL statement ignored error

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?

Upvotes: 7

Views: 103024

Answers (5)

ZygD
ZygD

Reputation: 24386

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.

Upvotes: 0

0xdb
0xdb

Reputation: 3697

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

Upvotes: 3

Sachin Patidar
Sachin Patidar

Reputation: 21

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;

Upvotes: 0

John Doyle
John Doyle

Reputation: 7793

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;

Upvotes: 2

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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.

Upvotes: 6

Related Questions