Rule description

• FETCH ... BULK COLLECT INTO should be used

Non-compliant Code Example

DECLARE
CURSOR cur_dept (departmentId INTEGER) RETURN departments%ROWTYPE IS  -- Define cur_dept,
    SELECT * FROM departments              -- repeating return type
    WHERE department_id = departmentId;	
	
BEGIN
  OPEN cur_dept;        
  FETCH cur_dept INTO temp1;        --Non compliant code (FETCH clause used without BULK COLLECT)
    IF NOT cur_dept%FOUND THEN 
        DBMS_OUTPUT.PUT_LINE('Not Found !!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Department Found: ' || temp1);
    END IF;
END;

Compliant Code Example

DECLARE
limit PLS_INTEGER := 100;
CURSOR cur_dept (departmentId INTEGER) RETURN departments%ROWTYPE IS  -- Define cur_dept,
    SELECT * FROM departments              -- repeating return type
    WHERE department_id = departmentId;	
	
BEGIN
  OPEN cur_dept;
  FETCH cur_dept                --Compliant code (FETCH clause used with BULK COLLECT)
  BULK COLLECT INTO temp1
  LIMIT limit;
  IF NOT cur_dept%FOUND THEN  
    DBMS_OUTPUT.PUT_LINE('Not Found !!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Department Found: ' || temp1);
  END IF;
  CLOSE cur_dept;
END;