Home
FETCH ... BULK COLLECT INTO should be used
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;