Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Unanswered: Need to Close Cursor in the Exception part?

    Hi, everyone, thank you in advance!

    Now I am studying how to create procedure in Oracle 10g in PL/SQL. I have read some examples. I was confused whether the exception part need close cursor or not, when a cursor is declared and opened. One of the examples:
    1. Close Cursor in the exception Part,
    Create or Replace Procedure a() Is
    Cursor c_a Is
    Select ID From Test;
    Begin
    Open c_a;
    If c_a%NOTFOUND THEN
    Insert into Error_Log Values(1,2);
    End if;
    Close c_a;
    Exception
    When OTHERS THEN
    IF c_a%ISOPEN THEN
    Close c_a;
    End If;
    End a;


    2. Not Close Cursor in the exception Part,
    Create or Replace Procedure a() Is
    Cursor c_a Is
    Select ID From Test;
    Begin
    Open c_a;
    If c_a%NOTFOUND THEN
    Insert into Error_Log Values(1,2);
    End if;
    Close c_a;
    Exception
    When OTHERS THEN
    NULL;
    End a;

    My Question Is:
    In the above examples if there is some error when the data is been inserting in the table here is need to close the cursor in the exception part or not.

    Thank you very much!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, I'd rather rewrite it and use cursor FOR loop instead. Such a principle has some advantages before your approach as:
    - there's no need to declare a cursor nor cursor variable
    - no need to open a cursor
    - no need to explicitly fetch from a cursor
    - no need to check whether there's been the end of record set
    - no need to worry about closing a cursor.

    Your code would then look like this:
    Code:
    CREATE OR REPLACE PROCEDURE a
    IS
    BEGIN
      FOR cur_r IN (SELECT id FROM test)
      LOOP
        INSERT INTO error_log VALUE (1, 2);
      END LOOP;
    END;
    I've seen you use an exception handler section. However, I'd suggest you NOT to do it that way - coding the WHEN OTHERS exception is a bad habbit as it really does nothing, but successfully hides real cause of an exception and - if something unexpected happens - you won't even know it happened (... THEN NULL), what happened (there will be no ORA-xxxxx message) and where (no code line number). Shortly, you'll be blind.

    By the way, your procedure will never raise an error trying to insert values into the 'error_log' table as c_a%NOTFOUND will never happen as you've only opened a cursor, but never FETCHED anything from it.

    But, theoretically, yes - what is opened, should be closed.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Littlefoot
    But, theoretically, yes - what is opened, should be closed.
    In the case of a local cursor in a procedure, it isn't strictly necessary since the cursor doesn't exists outside the scope of the procedure. It would matter if the cursor was defined outside the procedure and opened in it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •