Home

Description

The rule "Explicitly opened cursors should be closed" states that any cursors that have been explicitly opened in a PL/SQL block must be closed before the block is exited. This is important because it ensures that any resources associated with the cursor are released and that the cursor is no longer available for use. Failure to close an explicitly opened cursor can lead to unexpected behavior and errors when the block is executed. It is also important to note that cursors that are opened implicitly (i.e. not explicitly opened) do not need to be closed.

Key Benefits

• Prevent Memory Leaks : Cursors that are not explicitly closed can cause memory leaks, as they will remain open and consume resources until the connection is closed.
• Reduce Risk of Data Corruption : If a cursor is not explicitly closed, it can remain open and cause data corruption if the same cursor is used in multiple operations.
• Improve Performance : Keeping cursors open for long periods of time can negatively impact performance, as the database engine must keep track of the open cursor and its associated resources.

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;        --Non compliant code (Explicit open cursor is not closed)
  FETCH cur_dept INTO temp1;
    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
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;        --Compliant code (Explicit open cursor is closed at the end)
  FETCH cur_dept 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;