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;