Home
FETCH ... BULK COLLECT INTO should not be used without a LIMIT clause
Rule description
- FETCH ... BULK COLLECT INTO should not be used without a LIMIT clause
Non-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 --Non compliant code (FETCH clause used without LIMIT clause in BULK COLLECT) BULK COLLECT INTO temp1; 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;
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 LIMIT clause in 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;