Rule description

• Explicitly opened cursors should be closed

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  --Non compliant code (Cursor is using "NOT cursor % FOUND" instead of "cursor % NOTFOUND" )
        DBMS_OUTPUT.PUT_LINE('Not Found !!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Department Found: ' || temp1);
    END IF;
CLOSE cur_dept;
END;