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