This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,738 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

add FINALLY key word

TPD-Opitz
TPD-Opitz Dipl.-Ing, Dipl.-InfGermanyMember Posts: 2,465 Silver Trophy
edited Apr 28, 2016 4:12AM in Database Ideas - Ideas

I often face the problem that I Open a cursor and this could fail (e.g. with no rows returned)

But I'd lke the close of this cursor to be executed any way.

Currently I have to write the close statement twice: once in the "happy path" and again in the EXCEPTION block. This also implies that I need an exception bblock just for closing the cursor.

Please add the FINALLY key word.

Allow the FINALLY key word in front of a single statement to ensure that this statement is executed even when an exception occured before within this procedure/function. This may be coupled with the CLOSE key word.

Multiple FINALY (CLOSE) statements should be allowed within a block.

Alternatively add the FINALLY keyword as a part of an PL/SQL block just like EXCEPTION where I can place statements that should be executed even if an exception has been raised

bye

TPD

TPD-OpitzWilliam RobertsonApexBineMichaelAtOTNFatMartinRMatthiasRogelStew AshtonulohmannLukas EderAhmad AbutalibJeffrey KempPeter HraškoDanilo Piazzalungauser1609428MarwimEvandro Lima-OraclesdstuberUser_G3UV3
28 votes

Active · Last Updated

«13

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Here is an example of a basic explicit cursor PL/SQL.

    What would your suggestion involve changing from this example?

    SET SERVEROUTPUT ON SIZE 1000000

    DECLARE

      b1 NUMBER := 10000;

      CURSOR c1 IS SELECT * FROM HR.EMPLOYEES WHERE salary > b1;

      emp_rec c1%ROWTYPE;

    BEGIN

      OPEN c1;

      LOOP

        FETCH c1 INTO emp_rec;

        EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id||' '||emp_rec.salary);

      END LOOP;

      CLOSE c1;

    END;

    /

  • Chris Hunt
    Chris Hunt Freelance Oracle Consultant Leicester, UKMember Posts: 2,066 Gold Trophy
    edited Apr 28, 2016 5:43AM

    Cursors don't fail when no rows are returned:

    DECLARE
      CURSOR c1 IS
         SELECT 99
         FROM dual
         WHERE 1=2;
    
       x NUMBER;
    
    BEGIN
    
      OPEN c1;
      FETCH c1 INTO x;
      -- We get to here even though no value was returned from c1, c1%FOUND will be false though
      CLOSE c1;
    
    END;
    
    PL/SQL procedure successfully completed.
    
    

    If you have a situation where fetching from a cursor does cause an error, it's easily trapped by an exception:

    DECLARE
      CURSOR c1 IS
         SELECT 1/0
         FROM dual;
    
        x NUMBER;
    
       div_by_zero EXCEPTION;
       PRAGMA EXCEPTION_INIT (div_by_zero,-1476);
    
    BEGIN
    
      OPEN c1;
    
      BEGIN
         FETCH c1 INTO x;
      EXCEPTION
         WHEN div_by_zero THEN
            NULL; -- Do things here
      END;
    
      CLOSE c1;
    
    END;
    
    PL/SQL procedure successfully completed.
    
    

    It's also not at all clear what the syntax of your FINALLY statement shoud work, or what it would bring to the party that current syntax doesn't.

  • TPD-Opitz
    TPD-Opitz Dipl.-Ing, Dipl.-Inf GermanyMember Posts: 2,465 Silver Trophy

    Cursors don't fail when no rows are returned:

    DECLARE
      CURSOR c1 IS
         SELECT 99
         FROM dual
         WHERE 1=2;
    
       x NUMBER;
    
    BEGIN
    
      OPEN c1;
      FETCH c1 INTO x;
      -- We get to here even though no value was returned from c1, c1%FOUND will be false though
      CLOSE c1;
    
    END;
    
    PL/SQL procedure successfully completed.
    
    

    If you have a situation where fetching from a cursor does cause an error, it's easily trapped by an exception:

    DECLARE
      CURSOR c1 IS
         SELECT 1/0
         FROM dual;
    
        x NUMBER;
    
       div_by_zero EXCEPTION;
       PRAGMA EXCEPTION_INIT (div_by_zero,-1476);
    
    BEGIN
    
      OPEN c1;
    
      BEGIN
         FETCH c1 INTO x;
      EXCEPTION
         WHEN div_by_zero THEN
            NULL; -- Do things here
      END;
    
      CLOSE c1;
    
    END;
    
    PL/SQL procedure successfully completed.
    
    

    It's also not at all clear what the syntax of your FINALLY statement shoud work, or what it would bring to the party that current syntax doesn't.

    Chris Hunt wrote:
    It's also not at all clear what the syntax of your FINALLY statement shoud work, or what it would bring to the party that current syntax doesn't.
    

    At least it will improve readability which IMHO is a value "as such":

    DECLARE

      CURSOR c1 IS

         SELECT 1/0

         FROM dual;

        x NUMBER;

       div_by_zero EXCEPTION;

       PRAGMA EXCEPTION_INIT (div_by_zero,-1476);

    BEGIN

      OPEN c1;

      FETCH c1 INTO x;

      FINALLY  CLOSE c1;

    END;

    bye

    TPD

  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown
    edited May 4, 2016 2:59AM
    Chris Hunt wrote:
    It's also not at all clear what the syntax of your FINALLY statement shoud work, or what it would bring to the party that current syntax doesn't.
    

    At least it will improve readability which IMHO is a value "as such":

    DECLARE

      CURSOR c1 IS

         SELECT 1/0

         FROM dual;

        x NUMBER;

       div_by_zero EXCEPTION;

       PRAGMA EXCEPTION_INIT (div_by_zero,-1476);

    BEGIN

      OPEN c1;

      FETCH c1 INTO x;

      FINALLY  CLOSE c1;

    END;

    bye

    TPD

    Except it would be a block, like DECLARE or EXCEPTION, so naturally you'd format it something like:

    begin
        open c;
        loop
            ...
        end loop;
    exception
        when blah then
            do_something(); finally
        close c;
    end;
    ApexBinePeter Hraško
  • TPD-Opitz
    TPD-Opitz Dipl.-Ing, Dipl.-Inf GermanyMember Posts: 2,465 Silver Trophy

    Except it would be a block, like DECLARE or EXCEPTION, so naturally you'd format it something like:

    begin
        open c;
        loop
            ...
        end loop;
    exception
        when blah then
            do_something(); finally
        close c;
    end;

    In that case I would ommit line 6.

    bye

    TPD

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    In that case I would ommit line 6.

    bye

    TPD

    You are relying on the developer to add the 'finally close' command - and it could be mistakenly left out.


    Would it be better for Oracle to have an implicit cursor close, rather than you requiring a 'finally close' command?

    TPD-OpitzPeter Hraško
  • Chris Hunt
    Chris Hunt Freelance Oracle Consultant Leicester, UKMember Posts: 2,066 Gold Trophy

    Except it would be a block, like DECLARE or EXCEPTION, so naturally you'd format it something like:

    begin
        open c;
        loop
            ...
        end loop;
    exception
        when blah then
            do_something(); finally
        close c;
    end;

    Can the FINALLY block only contain CLOSE statements? Assuming the "blah" exception is raised in William's example, when is the close command executed, before or after do_something()? What happens if something in the FINALLY block raises an exception itself? Couldn't you nest blocks in such a way as to get the same result from existing structures?

    Personally, I always take care to close any cursor I open - a habit ingrained over thirty years of programming. But my understanding is that Oracle will close cursors automatically once they go out of scope. So this suggestion appears doubly unnecessary.

    GregVWilliam Robertson
  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    In that case I would ommit line 6.

    bye

    TPD

    @TPD - good point. I've edited my example and removed the 'close' statement that was after 'end loop'.

  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    Can the FINALLY block only contain CLOSE statements? Assuming the "blah" exception is raised in William's example, when is the close command executed, before or after do_something()? What happens if something in the FINALLY block raises an exception itself? Couldn't you nest blocks in such a way as to get the same result from existing structures?

    Personally, I always take care to close any cursor I open - a habit ingrained over thirty years of programming. But my understanding is that Oracle will close cursors automatically once they go out of scope. So this suggestion appears doubly unnecessary.

    Good question. Possibly the FINALLY block should be executed before exceptions, in which case the sample above is in the wrong order.

    Any valid PL/SQL would go in it. Perhaps the example of closing an explicit cursor was a distraction from the main point of the suggestion.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Good question. Possibly the FINALLY block should be executed before exceptions, in which case the sample above is in the wrong order.

    Any valid PL/SQL would go in it. Perhaps the example of closing an explicit cursor was a distraction from the main point of the suggestion.

    So what would your example code look like now?