What is pass by reference in oracle?

To better understand, let's review how PL/SQL handles parameters in two ways:

By Reference
In this case, a pointer to the actual parameter is passed to the corresponding formal parameter. Both actual and formal parameters point to the same location in memory that holds the value of the parameter.

By Value
In this case, the value of the actual parameter is copied to the corresponding formal parameter. If the program then terminates without an exception, the formal parameter value is copied back to the actual parameter. If an error occurs, the changed values are not copied back to the actual parameter.

By default OUT and IN OUT parameters are passed by value and IN parameters are passed by reference. When an OUT or IN OUT parameter is modified inside the procedure the procedure actually only modifies a copy of the parameter value. Only when the procedure has finished without exception is the result value copied back to the formal parameter.

procedure add_numbers(
first_val in number,
second_val in out number
)
BEGIN
second_val := first_val + second_val;
--some other statements

END;
/

TEST:

BEGIN
par_in = 124;
par_inout = 76;
add_numbers(par_in, par_inout);
DBMS_OUTPUT.PUT_LINE(par_inout);
END;
/

The example above, will print 200 on the screen. There are two parameters passed to add_numbers procedure:

1. The first one is par_in which is of the mode IN. This parameter is passed by reference. Hence, in add_numbers procedure, no other copy of this parameter will be created. Rather, the same copy will be used. If you are allowed to modify the value first_val inside add_numbers procedure (but you are not), the value will reflect directly to par_in. Because simply both first_val and par_in are two names for the same memory location.
2. The second one is par_inout which is of the mode IN OUT. This parameter is passed by value by default. Hence, in add_numbers procedure, new (memory location) will be allocated to store the another copy of this parameter. Hence, par_inout is a name to different location in the memory than second_val. They two names to two different locations.

The implication of the second parameter is that, after the line second_val := first_val + second_val; is executed, the value of second_val is different to the value of par_inout until the end of the procedure. Only at the end of the procedure, the value of second_val will be passed to par_inout.

Now, if you pass a large collection as an OUT or an IN OUT parameter then it will be passed by value, in other words the entire collection will be copied to the formal parameter when entering the procedure and back again when exiting the procedure. If the collection is large this can lead to unnecessary CPU and memory consumption.

The NOCOPY Parameter Mode Hint alleviates this problem because you can use it to instruct the runtime engine to try to pass OUT or IN OUT parameters by reference instead of by value

The hint requests PL/SQL runtime engine to pass an IN OUT argument by reference rather than by value. This can speed up the performance of your programs, because by-reference arguments are not copied within the program unit. When you pass large, complex structures like collections, records, or objects, this copy step can be expensive.