G-7250: Always use NOCOPY when appropriate

Minor

Efficiency

Reason

When we pass OUT or IN OUT parameters in PL/SQL the Oracle Database supports two methods of passing data: By Value and By Reference.

The default, By Value, will copy all the data passed into a temporary buffer. This buffer is passed to the procedure and used during the life of the procedure. Then when processing is complete, the data in the buffer is copied to the original variable.

Passing By Reference is achieved by the NOCOPY hint, and, in contrast, it will pass a reference to the variable's data. Think of a pointer in the C language. This means that no temporary buffer is required. When passing significant amounts of data, the effects of passing values by reference can be significant.

Example (bad)

procedure add_message(
     p_msg          in out message_tbl_type
   , p_message_text in varchar2
   , p_severity     in varchar2 default 'E'
)
is
  l_index  pls_integer;
begin

  l_index := p_msg.count + 1;
  p_msg(l_index).message_text := p_message_text;
  p_msg(l_index).severity := p_severity;

end add_message;

Example (good)

procedure add_message(
     p_msg          in out nocopy message_tbl_type
   , p_message_text in varchar2
   , p_severity     in varchar2 default 'E'
)
is
  l_index  pls_integer;
begin

  l_index := p_msg.count + 1;
  p_msg(l_index).message_text := p_message_text;
  p_msg(l_index).severity := p_severity;

end add_message;