add FINALLY key word

TPD-Opitz · Apr 28, 2016 4:12AM

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

top.gun · Apr 28, 2016 5:30AM

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 · Apr 28, 2016 5:32AM

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 · Apr 28, 2016 6:42AM

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 · Apr 28, 2016 2:30PM

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;

TPD-Opitz · Apr 28, 2016 5:06PM

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 · Apr 29, 2016 12:50AM

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?

Chris Hunt · Apr 29, 2016 6:45AM

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.

William Robertson · Apr 29, 2016 6:49AM

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 · Apr 29, 2016 9:47AM

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 · May 4, 2016 1:42AM

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?

Forum Stats

Quick Links

Categories

add FINALLY key word

Active · Last Updated Apr 28, 2016 4:12AM

Comments

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

Forum Stats

Categories

add FINALLY key word

Active · Last Updated Apr 28, 2016 4:12AM

Comments