G-4130: Always close locally opened cursors.

Major

Efficiency, Reliability

Reason

Any cursors left open can consume additional memory space (i.e. SGA) within the database instance, potentially in both the shared and private SQL pools. Furthermore, failure to explicitly close cursors may also cause the owning session to exceed its maximum limit of open cursors (as specified by the OPEN_CURSORS database initialization parameter), potentially resulting in the Oracle error of “ORA-01000: maximum open cursors exceeded”.

Example (bad)

CREATE OR REPLACE PACKAGE BODY employee_api AS
   FUNCTION department_salary (in_dept_id IN departments.department_id%TYPE)
      RETURN NUMBER IS
      CURSOR c_department_salary(p_dept_id IN departments.department_id%TYPE) IS 
         SELECT sum(salary) AS sum_salary
           FROM employees
          WHERE department_id = p_dept_id;
      r_department_salary c_department_salary%rowtype;
   BEGIN
      OPEN c_department_salary(p_dept_id => in_dept_id);
      FETCH c_department_salary INTO r_department_salary;

      RETURN r_department_salary.sum_salary;
   END department_salary;
END employee_api;
/

Example (good)

CREATE OR REPLACE PACKAGE BODY employee_api AS
   FUNCTION department_salary (in_dept_id IN departments.department_id%TYPE)
      RETURN NUMBER IS
      CURSOR c_department_salary(p_dept_id IN departments.department_id%TYPE) IS 
         SELECT SUM(salary) AS sum_salary
           FROM employees
          WHERE department_id = p_dept_id;
      r_department_salary c_department_salary%rowtype;
   BEGIN
      OPEN c_department_salary(p_dept_id => in_dept_id);
      FETCH c_department_salary INTO r_department_salary;
      CLOSE c_department_salary;
      RETURN r_department_salary.sum_salary;
   END department_salary;
END employee_api;
/