Home
FETCH ... BULK COLLECT INTO should not be used without a LIMIT clause
Description
The FETCH ... BULK COLLECT INTO code rule states that the FETCH ... BULK COLLECT INTO statement should not be used without a LIMIT clause. This is because the FETCH ... BULK COLLECT INTO statement can be used to retrieve a large number of rows from a database table, and without a LIMIT clause, it could retrieve an excessive amount of data, which could cause performance issues. Therefore, it is important to always include a LIMIT clause when using the FETCH ... BULK COLLECT INTO statement to ensure that only the necessary amount of data is retrieved.
Key Benefits
- Reduced resource usage: Using the FETCH ... BULK COLLECT INTO should not be used without a LIMIT clause rule helps to reduce the amount of resources used by the database.
- Improved performance: By limiting the number of rows retrieved, this rule helps to improve the performance of queries.
- Preventing data overload: By setting a limit on the number of rows retrieved, this rule helps to prevent the database from being overwhelmed with too much data.
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;