Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
add FINALLY key word

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
Comments
-
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;
/
-
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.
-
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
-
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; -
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
-
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?
-
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.
-
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'.
-
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.
-
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?