Similar to finally Block (JAVA) in Oracle PL/SQL Block

Question

As in Java there is finally block which executed in all conditions.

Is there any similar function in Oracle PL/SQL which will be executed whenever procedure completes its execution even a return statement is used?

Answer 1

There is no equivalent of FINALLY but you can simulate it using nested PL/SQL blocks;

DECLARE
  -- Your variables.
  return_early BOOLEAN := FALSE;
BEGIN
  -- Do something

  DECLARE
    -- Local variables in "try" block
  BEGIN 
    -- Equivalent of "try" block
    -- Do something that may raise an exception
    IF some_condition THEN
      return_early := TRUE;
      -- you could also use "GOTO end_try;" rather than surrounding the
      -- following statements in an "ELSE" statement
    ELSE
      -- Do something else that may raise an exception
    END IF;
  EXCEPTION
    WHEN your_exception THEN
      -- Equivalent of "catch" block
  END;
  <<end_try>>
  -- Handle "finally" here, after end of nested block.
  -- Note: you can only see variables declared in this outer block
  --       not variables local to the nested PL/SQL block.
  IF return_early THEN
    RETURN;
  END IF;

  -- Continue and do more stuff.
END;
/

Answer 2

Another thread on Stackoverflow can help out here : Exception handling in pl/sql

Where Tony says :

"You can created nested blocks:"

create or replace procedure Trial
    is 
Begin
  begin
    ---Block A--
  EXCEPTION
    when others then
      insert into error_log values('error');
  end;
  begin
    --Block B ----
  end;
end;

Answer 3

You can create a custom Exception and then Raise it at the end of your other exceptions, this custom exception must be on an outside block:

BEGIN
    BEGIN
     --DO SOME CODE HERE
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        --HANDLE EXCEPTION
        RAISE CUSTOM_EXCEPTION;
    END;
EXCEPTION
    WHEN CUSTOM_EXCEPTION THEN
      --HANDLE THE FINALLY CODE HERE
END;

Answer 4

The solutions provided in other answers does not implement exact try-catch-finally logic. E.g. the finally should be executed even if exception is reraised and even if in did not handled at all. In other words, the finally block must be called always.

The most similar behavior to Java's finally is the following. Unfortunately, here we have to wrap finally block into a procedure. We have no choice in PL/SQL.

declare
begin
  
  declare
    EXPECTED_EXCEPTION_RECOVERABLE exception;
    EXPECTED_EXCEPTION_FATAL exception;
    EXPECTED_EXCEPTION_UNHANDLED exception;
    procedure finally is
    begin
      dbms_output.put_line('FINALLY section executed');
    end;
  begin
  
    dbms_output.put_line('Trying dangerous section...');
    /* uncomment to try different behavior */
    --raise EXPECTED_EXCEPTION_RECOVERABLE;
    --raise EXPECTED_EXCEPTION_FATAL;
    --raise EXPECTED_EXCEPTION_UNHANDLED;
    dbms_output.put_line('Dangerous section is executed successfully');
    FINALLY();

  exception
    when EXPECTED_EXCEPTION_RECOVERABLE then 
      dbms_output.put_line('Recoverable exception handled.');
      FINALLY();
    when EXPECTED_EXCEPTION_FATAL then 
      dbms_output.put_line('Fatal exception handled and will be reraised');
      FINALLY();
      RAISE;
    when OTHERS then
      dbms_output.put_line('Unhandled exception is just reraised after finally section');
      FINALLY();
      RAISE;
  end;
  
end;
/

It seems, this solution looks enough readable;