CODEWITHSUNDEEP
oracle-databaseplsql
Kedar Vaze
Kedar Vaze

Reputation: 119

Getting pl/sql statement ignored error when writing a procedure

create or replace PROCEDURE CREATE_DEL_DEPT <br>
(
  PARAM1 IN VARCHAR2 Default '%',
  PARAM2 IN VARCHAR2 Default '%',
  PARAM3 IN BOOLEAN Default True
) AS

PRAGMA AUTONOMOUS_TRANSACTION; 
CURSOR Employees IS
    Select Employee_ID, First_name, Last_Name
    From HR.employees Where Department_ID = PARAM2;
BEGIN
    For Employee_ID in Employees LOOP
        if Department_ID = 20 then
            DBMS_OUTPUT.PUT_LINE ('test');
        elsif Department_ID = 30 then
            DBMS_OUTPUT.PUT_LINE ('test1');
        else
            DBMS_OUTPUT.PUT_LINE ('else test');
        end if;
    END LOOP;

END;

I'm getting following error. Line 14 is where starting of 'If' statement is

Error(14,9): PL/SQL: Statement ignored

Upvotes: 1

Views: 53

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You need to refer to department_id when selecting from the table in the cursor

CREATE OR REPLACE PROCEDURE create_del_dept 
(
  PARAM1 IN VARCHAR2 Default '%',
  PARAM2 IN VARCHAR2 Default '%',
  PARAM3 IN BOOLEAN Default True
) AS

PRAGMA AUTONOMOUS_TRANSACTION; 
CURSOR Employees IS
    Select Employee_ID, First_name, Last_Name,department_id
       From HR.employees Where Department_ID = PARAM2; 
    BEGIN
     for rec in employees LOOP
          IF
               rec.department_id = 20
          THEN
               dbms_output.put_line('test');
          ELSIF rec.department_id = 30 THEN
               dbms_output.put_line('test1');
          ELSE
               dbms_output.put_line('else test');
          END IF;
     END LOOP;
END;
/

By the way, you may simplify your code avoiding a cursor definition by using implicit cursor loop.

for rec in ( Select Employee_ID, First_name, Last_Name,department_id
              From HR.employees Where Department_ID = PARAM2 
       ) loop

Upvotes: 1

Related Questions