Can OUT parameter values still be written even if a PL/SQL procedure raises an error?

Question

I have a PL/SQL procedure with an OUT parameter. How can I force the OUT parameter values to be written, even if an error is raised? Is there another mechanism for returning values when an error is raised?

Answer 1

To expand on @APC's answer, the documentation says:

If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters).

Here are some examples that might help illustrate:

SQL> set serveroutput on
SQL> REM Example 1 -- OUT parameter value is unchanged if an exception was raised after value was assigned
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      end p;
  9  begin
 10      p(x);
 11  exception
 12      when program_error then
 13          dbms_output.put_line
 14          (
 15              case
 16                  when x is null then 'null'
 17                  else to_char(x)
 18              end
 19          );
 20  end;
 21  /
1                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 2 -- OUT parameter value is unchanged if value was set in exception handler and exception was then re-raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      exception
  9          when program_error then
 10              x := 3;
 11              raise;
 12      end p;
 13  begin
 14      p(x);
 15  exception
 16      when program_error then
 17          dbms_output.put_line
 18          (
 19              case
 20                  when x is null then 'null'
 21                  else to_char(x)
 22              end
 23          );
 24  end;
 25  /
1                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 3 -- OUT parameter value is changed if value was set in exception handler and exception was not re-raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      exception
  9          when program_error then
 10              x := 3;
 11      end p;
 12  begin
 13      p(x);
 14      dbms_output.put_line
 15      (
 16          case
 17              when x is null then 'null'
 18              else to_char(x)
 19          end
 20      );
 21  end;
 22  /
3                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 4 -- OUT NOCOPY parameter value is changed if an exception was raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out nocopy number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      end p;
  9  begin
 10      p(x);
 11  exception
 12      when program_error then
 13          dbms_output.put_line
 14          (
 15              case
 16                  when x is null then 'null'
 17                  else to_char(x)
 18              end
 19          );
 20  end;
 21  /
2                                                                               

PL/SQL procedure successfully completed.

Answer 2

When a PL/SQL program raises an exception Oracle does not assign values to OUT parameters. If This is a safety feature, because it gives us all the parameters in a known and consistent state

However, sometimes that is not what we want. For instance, we might want to use an OUT parameter to pass an error message or other helpful information to the calling program. We can set OUT parameters in the EXCEPTIONS section of the program. Those values are passed out (unless the handler itself raises an exception).