Understanding How PL/SQL Passes Parameters
When you call a PL/SQL procedure or function, the runtime engine must decide how to pass parameter values between the caller and the callee. For IN parameters, Oracle passes by reference by default, which is efficient because it simply shares a pointer to the original data. However, for OUT and IN OUT parameters, Oracle uses pass by value semantics. This means it creates a complete copy of the parameter value before the subprogram begins executing.
For scalar types like NUMBER or VARCHAR2, this copy operation is negligible. But when your parameters are large collections, records, or objects containing thousands of elements or megabytes of data, the copy operation can consume significant memory and CPU time. Even worse, if the subprogram raises an unhandled exception, Oracle must restore the original values from those copies, which adds further overhead.
The NOCOPY Hint
The NOCOPY compiler hint tells Oracle to attempt pass by reference semantics for OUT and IN OUT parameters instead of creating copies. Here is how you use it:
PROCEDURE process_large_dataset(
p_records IN OUT NOCOPY t_record_collection
)
IS
BEGIN
FOR i IN 1 .. p_records.COUNT LOOP
p_records(i).processed_flag := 'Y';
p_records(i).process_date := SYSDATE;
END LOOP;
END process_large_dataset;Without NOCOPY, if t_record_collection contains 100,000 records, Oracle would copy all 100,000 records before the procedure starts and potentially copy them back if an exception occurs. With NOCOPY, no copies are made, and the procedure operates directly on the caller’s data.
When to Use NOCOPY
The NOCOPY hint is most beneficial when passing large collections (nested tables, VARRAYs, or associative arrays with many elements), large records or object types, or any parameter where the cost of copying is measurable. In batch processing scenarios where procedures pass collections of thousands of rows between subprograms, adding NOCOPY can reduce elapsed time by 30% or more.
The Trade Off You Need to Understand
Because NOCOPY passes by reference, any changes your subprogram makes to the parameter are immediately visible to the caller, even if the subprogram subsequently raises an exception. Without NOCOPY, Oracle would roll back those changes and restore the original values. With NOCOPY, partial modifications persist. This means you must design your error handling carefully if you use NOCOPY on parameters that should remain consistent in the face of exceptions.
-- Demonstration of the NOCOPY trade off
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
l_data t_numbers := t_numbers(1, 2, 3, 4, 5);
PROCEDURE modify_data(p_data IN OUT NOCOPY t_numbers) IS
BEGIN
p_data(1) := 999;
p_data(2) := 888;
-- Simulate an error after partial modification
RAISE_APPLICATION_ERROR(-20001, 'Something went wrong');
END;
BEGIN
BEGIN
modify_data(l_data);
EXCEPTION
WHEN OTHERS THEN
-- With NOCOPY, l_data(1) is now 999 and l_data(2) is 888
-- Without NOCOPY, l_data would still be (1, 2, 3, 4, 5)
DBMS_OUTPUT.PUT_LINE('First element: ' || l_data(1));
END;
END;
/Important Caveats
NOCOPY is a compiler hint, not a directive. Oracle may ignore it in certain situations, such as when the actual and formal parameters are subject to implicit datatype conversion, when the parameter has a NOT NULL constraint, or when the subprogram is called through a database link. In practice, Oracle honors NOCOPY in the vast majority of local calls, but you should not rely on it for correctness. Your code should produce correct results regardless of whether Oracle applies the hint.
Version Behavior
NOCOPY has been available since Oracle 8i and its behavior has remained consistent through Oracle 19c, 21c, and 23ai. The compiler has become better at automatically optimizing parameter passing in newer versions, but explicit NOCOPY still provides the most reliable performance improvement for large parameters. There is no downside to applying it liberally to OUT and IN OUT parameters that carry large payloads, as long as you understand and accept the exception handling trade off.