Getting pl/sql statement ignored error when writing a procedure

Question

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

Note that your query will return zero rows if you call your procedure with default values. Where Department_ID = PARAM2 won't match '%'. Equality doesn't take wildcards — APC
– APC, Commented Feb 25, 2019 at 8:27

Kaushik Nayak · Accepted Answer · 2019-02-25 07:09:29Z

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

Collectives™ on Stack Overflow

Getting pl/sql statement ignored error when writing a procedure

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related