G-4130
🆓Error
Always close locally opened cursors.
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
Non-Compliant Example
create or replace package body employee_api as
function department_salary(in_dept_id in integer) -- NOSONAR G-7460: non-deterministic
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;
co_dept_id constant departments.department_id%type := in_dept_id;
begin
open c_department_salary(p_dept_id => co_dept_id);
fetch c_department_salary into r_department_salary;
return r_department_salary.sum_salary;
end department_salary;
end employee_api;
/Issues
| Line | Column | Message |
|---|---|---|
| 11 | 12 | Cursor c_department_salary is not closed. |
Compliant Solution - ★★★★★
create or replace package body employee_api as
function department_salary(in_dept_id in integer) -- NOSONAR G-7460: non-deterministic
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;
co_dept_id constant departments.department_id%type := in_dept_id;
begin
open c_department_salary(p_dept_id => co_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;
/References
- same as Trivadis G-4130
- same as plsql:S1573