Home

Description

The FETCH ... BULK COLLECT INTO rule states that when retrieving data from a database, the FETCH statement should be used in conjunction with the BULK COLLECT INTO clause. This allows for the retrieval of multiple rows of data in a single operation, which can improve performance and reduce the amount of coding needed. The BULK COLLECT INTO clause allows for the retrieval of data into a collection of records, which can then be looped through and processed. This rule is especially useful when dealing with large datasets, as it can significantly reduce the amount of time needed to process the data.

Key Benefits

• Faster Execution: Using FETCH BULK COLLECT INTO can significantly reduce the execution time of a query.
• Reduced Network Traffic: By using FETCH BULK COLLECT INTO, the amount of data sent over the network is reduced.
• Better Performance: FETCH BULK COLLECT INTO can improve the performance of a query by reducing the number of trips to the database.

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;