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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create or replace package body employee_api as
   function department_salary (in_dept_id in department.department_id%type)
      return number is
      cursor department_salary_cur(p_dept_id in department.department_id%type) is 
         select sum(salary) as sum_salary
           from employee
          where department_id = p_dept_id;
      r_department_salary department_salary_cur%rowtype;
   begin
      open department_salary_cur(p_dept_id => in_dept_id);
      fetch department_salary_cur into r_department_salary;

      return r_department_salary.sum_salary;
   end department_salary;
end employee_api;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create or replace package body employee_api as
   function department_salary (in_dept_id in department.department_id%type)
      return number is
      cursor department_salary_cur(p_dept_id in department.department_id%type) is 
         select sum(salary) as sum_salary
           from employee
          where department_id = p_dept_id;
      r_department_salary department_salary_cur%rowtype;
   begin
      open department_salary_cur(p_dept_id => in_dept_id);
      fetch department_salary_cur into r_department_salary;
      close department_salary_cur;
      return r_department_salary.sum_salary;
   end department_salary;
end employee_api;
/