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

error-handling, oracle, plsql

Solution

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.

Problem

Original source