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”.

  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 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; / 
  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 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; /