CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
ziggy
ziggy

Reputation: 15876

Closing all cursors 'once' from a pl/sql exception block

Is there an easier way to close all open cursors from within a PL/SQL program (Oracle 10G).

I have a program which can generate a number of exceptions. To exit properly, i need to check whether there are any cursors open and close them. This is the kind of situation i am ending up with. 

Procedure test
is 
--
---
Begin
--
--
Exception
 when no_data_found then
    if cursorA%isopen close
    if cursorB%isopen close
    if cursorC%isopen close
 when invalid_date then
    if cursorA%isopen close
    if cursorB%isopen close
    if cursorC%isopen close
 when invalid_user then
    if cursorA%isopen close
    if cursorB%isopen close
    if cursorC%isopen close
 when others then
    if cursorA%isopen close
    if cursorB%isopen close
    if cursorC%isopen close 

End test;

Obviously the above is not ideal especially if there are many exception clauses. Instead of havin the same checks on each exception block, is there a quicker way to close all open cursor? Note: It needs to close only the cursors opened by the currently running pl/sql program because there could be other pl/sql program that could also open cursors.

Thanks in advance

Upvotes: 2

Views: 30811

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Are you sure that you need to use the explicit cursor syntax in the first place rather than using implicit cursors? If you use implicit cursors, Oracle takes care of opening and closing them automatically. You can declare the query inline or out of line as in the block below

DECLARE
  CURSOR cursor_a
      IS SELECT *
           FROM emp;
BEGIN
  FOR a IN cursor_a
  LOOP
    <<do something>>
  END LOOP;

  FOR b IN (SELECT * 
              FROM dept)
  LOOP
    <<do something else>>
  END LOOP;
END;

In either case, Oracle will automatically take care of closing the cursor when you exit the block.

If you do need to use explicit cursors for some reason, and assuming that you need to catch multiple different exceptions because you will handle those exceptions differently, you can create a nested block that closes the cursors and just call that from each exception handler

DECLARE
  CURSOR cursor_a
      IS SELECT *
           FROM emp;
  CURSOR cursor_b
      IS SELECT *
           FROM dept;
  PROCEDURE close_open_cursors
  AS
  BEGIN
    IF( cursor_a%isopen )
    THEN
      close cursor_a;
    END IF;
    IF( cursor_b%isopen )
    THEN
      close cursor_b;
    END IF;
  END;
BEGIN
  OPEN cursor_a;
  OPEN cursor_b;
  RAISE no_data_found;
EXCEPTION
  WHEN no_data_found
  THEN
    close_open_cursors;
    <<do something meaningful>>
  WHEN too_many_rows
  THEN
    close_open_cursors;
    <<do something meaningful>>
  WHEN others
  THEN
    close_open_cursors;
    raise;
END;

Upvotes: 12

Related Questions