Thanks for the question, Anastasia.

Asked: January 17, 2017 - 12:01 pm UTC

Last updated: April 02, 2019 - 6:29 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hello, Tom.
Hope you're doing well :)
My question is - is it bad practice to let the 'main' procedure of the package to close all open cursors?
Here is the code I want to use:

create or replace PACKAGE body pck_test
AS
-- Global package cursor. I have to use them, because I need several loops within them without re-
-- opening and, hence, releasing the locks (I use for update cursors in my real code). Not defined in the spec

CURSOR cur_1
IS
SELECT sysdate FROM dual;

-- Procedure, that actually works with data from the cursor. This one can face an error and raise an expection to the
-- calling block. Not defined in the spec

PROCEDURE p_1(p_in_num NUMBER)
IS
v NUMBER:=0;
BEGIN
OPEN cur_1;
v:=10/p_in_num;

END p_1;

-- The 'main' procedure. Calls p_1 and always gets control back and finishes the transaction.
-- Defined in the spec.

PROCEDURE p_2
IS
BEGIN

p_1(0);

IF cur_1%ISOPEN THEN
CLOSE cur_1;
END IF;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
IF cur_1%ISOPEN THEN
CLOSE cur_1;
END IF;

ROLLBACK;
END p_2;

END;

What risks are to let the calling procedure close all open global cursors of the package, if any?
I'm being persuading that the only reasonable way is to close cur_1 in the end of p_1 and in its exception block.
In the real code, I have several cursors and several p_1-like procedures.
So, closing all open cursors in the main block would decrease the number of code lines, which I really like to. If there are some serious disadvantages, though, of course, I'd better write those lines. But I cannot see any - please, advice.

Thanks in advance.

* I read about dbms_session.reinitialize, but not only it closes cursors - all the variables are reset as well, which is to early to do in the end of p_1.

and Connor said...

"What risks are to let the calling procedure close all open global cursors of the package, if any?"

Same as with any approach - you should only close cursors that you (ie, your proc) explicitly opened, because otherwise you are creating risk for whoever called you. Modules should not create unintended consequences for those programs that called them.

If a program opened a cursor, and you called an "unrelated" program, then it should leave anything not related to it untouched.

In my experience, its typically rare to see cursors at package body level - this might suggest your procedures are too granular ? or perhaps worth another look.

And if you are going to have 'exception when others' then make sure you re-raise those exceptions !

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PL/SQL scope for open cursors

Rajeshwaran, Jeyabal, January 18, 2017 - 11:51 am UTC

PL/SQL has the ability to close the (locally declared) open cursors, once they go out of referencing PL/SQL unit -not sure when this feature got introduced in PL/SQL.

demo@ORA12C> create or replace package demo_pkg
  2  as
  3     procedure p;
  4  end;
  5  /

Package created.

demo@ORA12C> create or replace package body demo_pkg
  2  as
  3     cursor c is select * from dual;
  4     r c%rowtype;
  5     procedure p1 as
  6     begin
  7             open c;
  8             fetch c into r;
  9     end p1;
 10
 11     procedure p as
 12     begin
 13             for i in 1..5000
 14             loop
 15                     p1;
 16             end loop;
 17     end;
 18  end;
 19  /

Package body created.

demo@ORA12C>
demo@ORA12C> exec demo_pkg.p;
BEGIN demo_pkg.p; END;

*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "DEMO.DEMO_PKG", line 3
ORA-06512: at "DEMO.DEMO_PKG", line 7
ORA-06512: at "DEMO.DEMO_PKG", line 15
ORA-06512: at line 1

With a slight tweek in the package body, it still get this error.

demo@ORA12C> create or replace package body demo_pkg
  2  as
  3     cursor c is select * from dual;
  4     r c%rowtype;
  5     procedure p as
  6     begin
  7             open c;
  8             fetch c into r;
  9     end;
 10  end;
 11  /

Package body created.

demo@ORA12C> begin
  2     for i in 1..5000
  3     loop
  4             demo_pkg.p;
  5     end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "DEMO.DEMO_PKG", line 3
ORA-06512: at "DEMO.DEMO_PKG", line 7
ORA-06512: at line 4

But when the cursor declaration is brought with the declaration block of the referencing unit rather than global declaration, things change like this.

demo@ORA12C> create or replace package body demo_pkg
  2  as
  3     procedure p as
  4             cursor c is select * from dual;
  5             r c%rowtype;
  6     begin
  7             open c;
  8             fetch c into r;
  9     end;
 10  end;
 11  /

Package body created.

demo@ORA12C> select s.name,m.value
  2  from v$statname s,
  3       v$mystat m
  4  where m.statistic# = s.statistic#
  5  and s.name ='opened cursors current';

NAME                                                    VALUE
-------------------------------------------------- ----------
opened cursors current                                      7

1 row selected.

demo@ORA12C> begin
  2     for i in 1..5000
  3     loop
  4             demo_pkg.p;
  5     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@ORA12C> select s.name,m.value
  2  from v$statname s,
  3       v$mystat m
  4  where m.statistic# = s.statistic#
  5  and s.name ='opened cursors current';

NAME                                                    VALUE
-------------------------------------------------- ----------
opened cursors current                                      7

1 row selected.

demo@ORA12C> show parameter open_cursor

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- ----------------
open_cursors                                  integer     300
demo@ORA12C>

Anastasia, January 18, 2017 - 3:38 pm UTC

Thank you.
In my experience, its typically rare to see cursors at package body level - this might suggest your procedures are too granular ? or perhaps worth another look.
-- It could be, but it's a choice between having them too granular and have a huge CASE in them.

And if you are going to have 'exception when others' then make sure you re-raise those exceptions !
-- Sure

* Decided on closing all global cursors in one procedure, which does nothing else for now.

Cursor already open error

Durga, March 29, 2019 - 11:58 am UTC

Hi Connor,
I am not very clear "what needs to be done and why" if we have global cursors in package body which are explicitly opened and closed inside local procedures in the package body.

In our package, we have almost all cursors declared at package body level.
These cursors are opened inside procedures and then they are closed inside the procedure and at the same time exception is raised.

for eg:

OPEN find_cpn_loks (...cursor parameters...);
--this is a global cursor defined in pkg body
FETCH find_cpn_loks INTO v_cpn_loks;

OPEN find_cpn_loks_inactive(...cursor parameters....);
--this is a global cursor defined in pkg body
FETCH find_cpn_loks_inactive INTO v_cpn_loks_inactive;

.... some code processing here.............
IF find_cpn_loks%FOUND and find_cpn_loks_inactive%NOTFOUND
THEN
.... some code processing here.............
CLOSE find_cpn_loks;
CLOSE find_cpn_loks_inactive;
RAISE already_exists;
END IF;

We end up getting "Cursor already open" error in this package.

I am not clear how to explain the cause of this error - is it correct to get this error?
How to resolve this error? - (only by making the global cursors local ie moving cursor declarations inside procedures?)

Please help me understand.

Thank you for your patience.

Best Regards,
Durga

April 02, 2019 - 6:29 am UTC

"Cursor already open" means you did:

open C;

somewhere, and the followed it with

open C;

again before you closed it. If thats a package level cursor, then of course any of the subordinate procedures could have created that scenario, eg, one opens it, the other tries to etc.

That normally indicates some sort of logic flow error which should be investigated, but you can workaround by replacing:

open C;

with

if not c%isopen then open C; end if;

but I stress, that might just be hiding a logic error.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL scope for open cursors

Cursor already open error

More to Explore

PL/SQL demos

PL/SQL docs

When it's time to close cursors

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL scope for open cursors

Cursor already open error

More to Explore

PL/SQL demos

PL/SQL docs