NOCOPY Hint in Oracle

NOCOPY in Oracle

In Oracle PL/SQL, NOCOPY is a compiler directive that direct or guide the PL/SQL compiler to pass OUT and IN OUT parameters by reference, instead of passing by value, which is the default.

Let me explain Pass by values and Pass by Reference.

In Pass by value (default in Oracle PL/SQL) - when we pass a parameter to an Oracle stored program (Function, Procedure etc.), a copy of the IN and IN OUT parameter is passed to the called program, which means actual parameters copy in to formal parameters and after execution the formal parameters copied back in to actual parameters if the type of parameters passed is OUT or IN OUT. But copying large parameters, such as records, collections, and objects requires both time and require additional apace – that is costly.

In Pass by reference - when we pass a parameter to an Oracle stored program (Function, Procedure etc.), a reference of IN or IN OUT parameter is passed to called module, which means formal parameter also refer to actual parameters, so changes made to the formal parameters reflect to actual parameter because both the formal and actual referrer the same memory area, so because of this no need of copying values from actual to formal parameters and vice vrasa.

With the NOCOPY hint the parameters are passed by reference. In case of large objects pass by reference increase the performance. Let's have an example to compare the performance of both the methods. In the following example we are passing a collection for populating data.

SQL> SET SERVEROUTPUT ON
SQL>DECLARE
TYPE nested_table IS TABLE OF VARCHAR2(32767);
nt_usedata nested_table := nested_table();
l_start NUMBER;

PROCEDURE in_out (p_tab IN OUT nested_table) IS
BEGIN

SELECT first_name || ' ' || last_name BULK COLLECT INTO p_tab

FROM employees

WHERE job_id = 'SA_REP';

END;

PROCEDURE in_out_nocopy (p_tab IN OUT NOCOPY nested_table) IS
BEGIN
SELECT first_name || ' ' || last_name BULK COLLECT INTO p_tab

FROM employees

WHERE job_id = 'SA_REP';
END;
BEGIN
nt_usedata.extend;
nt_usedata(1) := '1234567890123456789012345678901234567890';
nt_usedata.extend(999999, 1); -- Copy element 1 into 2..1000000

-- Time of IN OUT with out NOCOPY
l_start := DBMS_UTILITY.get_time;

in_out(nt_usedata);

DBMS_OUTPUT.put_line('IN OUT : ' ||
(DBMS_UTILITY.get_time - l_start));

-- Time of IN OUT with NOCOPY
l_start := DBMS_UTILITY.get_time;

in_out_nocopy(nt_usedata);

DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/

SQL>IN OUT : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

SQL>

As per the output, it's very clear the performance improvements possible when using the NOCOPY hint.