Differences between "in"/"out" and "nocopy"

By default, PL/SQL passes OUT and IN OUT subprogram parameters by value. Before running the subprogram, PL/SQL copies each OUT and IN OUT parameter to a temporary variable, which holds the value of the parameter during subprogram execution. If the subprogram is exited normally, then PL/SQL copies the value of the temporary variable to the corresponding actual parameter. If the subprogram is exited with an unhandled exception, then PL/SQL does not change the value of the actual parameter.

When OUT or IN OUT parameters represent large data structures such as collections, records, and instances of ADTs, copying them slows execution and increases memory use—especially for an instance of an ADT.

For each invocation of an ADT method, PL/SQL copies every attribute of the ADT. If the method is exited normally, then PL/SQL applies any changes that the method made to the attributes. If the method is exited with an unhandled exception, then PL/SQL does not change the attributes.

Above are the describe in Oracle standard document. Now we use the below code to check this points:

declare  v_value1 number;  procedure add1(p_v1 in out number) as    v_t number := 1;  begin    p_v1 := p_v1 + 1;      v_t := v_t / 0;--throw out an exception  end;  procedure add2(p_v1 in out nocopy number) as    v_t number := 1;  begin    p_v1 := p_v1 + 1;      v_t := v_t / 0;--throw out an exception  end;begin  v_value1 := 1;  dbms_output.put_line('check "in"/"out": ' || v_value1);  begin    add2(v_value1);      dbms_output.put_line(v_value1);  exception    when others then          dbms_output.put_line('error:' || v_value1);      end;    v_value1 := 1;  dbms_output.put_line('check "nocopy": ' || v_value1);  begin    add1(v_value1);      dbms_output.put_line(v_value1);  exception    when others then          dbms_output.put_line('error:' || v_value1);  end;end;

Output:

check "in"/"out": 1error:2check "nocopy": 1error:1

This feature is more like the differences between pass a variable by reference and by value in C.

The "nocopy" parameters use less memory and not need to copy the values when invoked, which is useful for performance tuning.