Deep Dive — NOCOPY

OUT and IN OUT parameters are copied in and copied out. For a 100,000-row collection that is two full copies per call — unless you ask for a reference.

Used in
Chapter 4 — In-Memory Lookup
Appendix entry
NOCOPY
Related
ASSOCIATIVE ARRAY COLLECTION METHODS EXCEPTION

By default PL/SQL passes IN parameters by reference and OUT / IN OUT parameters by value: the actual is copied into a local on entry, and the local is copied back on normal exit. That copy-out is what gives OUT parameters their exception safety — if the procedure raises, the caller's variable is untouched.

NOCOPY asks the compiler to pass an OUT/IN OUT parameter by reference instead: no copies, and the procedure works directly on the caller's variable. It is a hint — the compiler may ignore it — and it changes exception semantics.

Use it on OUT/IN OUT parameters that are large collections or records passed through several layers. Do not use it on IN parameters (already by reference), or on parameters where "partially modified on failure" would be a bug.

SQL 
PROCEDURE <name> (<param> { OUT | IN OUT } NOCOPY <type>);
FUNCTION  <name> (<param> { OUT | IN OUT } NOCOPY <type>) RETURN <type>;

The compiler ignores NOCOPY (and silently passes by value) when, among others:

  • the actual parameter is an element of an associative array (passing the whole array is fine);
  • the actual is constrained (NOT NULL, or a NUMBER(p,s) / VARCHAR2(n) with a different constraint than the formal) so an implicit conversion or check is needed;
  • the actual and formal types differ and require implicit conversion;
  • the call goes through a database link or is an external procedure;
  • the actual is a record with a %ROWTYPE/%TYPE declaration whose constraints differ from the formal's.

SQL 
CREATE OR REPLACE PACKAGE BODY reprice_pkg AS
  TYPE t_rows IS TABLE OF orders%ROWTYPE;

  PROCEDURE apply_rules (p_rows IN OUT NOCOPY t_rows) IS
  BEGIN
    FOR i IN 1 .. p_rows.COUNT LOOP
      p_rows(i).amount := pricing_pkg.reprice(p_rows(i).order_id, p_rows(i).amount);
    END LOOP;
  END;

  PROCEDURE run IS
    CURSOR c IS SELECT * FROM orders WHERE status = 'NEW';
    l_rows t_rows;
  BEGIN
    OPEN c;
    LOOP
      FETCH c BULK COLLECT INTO l_rows LIMIT 50000;
      EXIT WHEN l_rows.COUNT = 0;
      apply_rules(l_rows);                       -- no 50,000-row copy in, none out
      FORALL i IN 1 .. l_rows.COUNT
        UPDATE orders SET amount = l_rows(i).amount WHERE order_id = l_rows(i).order_id;
      COMMIT;
    END LOOP;
    CLOSE c;
  END;
END;
/

SQL 
DECLARE
  TYPE t_nums IS TABLE OF NUMBER;
  l t_nums := t_nums();
  t0 PLS_INTEGER;

  PROCEDURE by_value (p IN OUT t_nums) IS BEGIN p(1) := p(1) + 1; END;
  PROCEDURE by_ref   (p IN OUT NOCOPY t_nums) IS BEGIN p(1) := p(1) + 1; END;
BEGIN
  l.EXTEND(1000000); FOR i IN 1 .. 1000000 LOOP l(i) := i; END LOOP;

  t0 := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 100 LOOP by_value(l); END LOOP;
  DBMS_OUTPUT.PUT_LINE('by value : ' || (DBMS_UTILITY.GET_TIME - t0) / 100 || 's');

  t0 := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 100 LOOP by_ref(l); END LOOP;
  DBMS_OUTPUT.PUT_LINE('NOCOPY   : ' || (DBMS_UTILITY.GET_TIME - t0) / 100 || 's');
END;
/
-- typical: by value ~4–8 s (100 × two 1M-element copies), NOCOPY ~0.01 s

SQL 
DECLARE
  TYPE t_nums IS TABLE OF NUMBER;
  l t_nums := t_nums(1, 2, 3);

  PROCEDURE p_value (p IN OUT t_nums) IS
  BEGIN
    p(1) := 100;
    RAISE_APPLICATION_ERROR(-20001, 'boom');
  END;
  PROCEDURE p_nocopy (p IN OUT NOCOPY t_nums) IS
  BEGIN
    p(1) := 100;
    RAISE_APPLICATION_ERROR(-20001, 'boom');
  END;
BEGIN
  BEGIN p_value(l);  EXCEPTION WHEN OTHERS THEN NULL; END;
  DBMS_OUTPUT.PUT_LINE('after by-value failure : l(1) = ' || l(1));   -- 1   (copy-out never happened)
  BEGIN p_nocopy(l); EXCEPTION WHEN OTHERS THEN NULL; END;
  DBMS_OUTPUT.PUT_LINE('after NOCOPY failure   : l(1) = ' || l(1));   -- 100 (modified in place)
END;
/

If the caller relies on "either fully updated or untouched", NOCOPY breaks that contract. Decide per parameter.

SQL 
DECLARE
  TYPE t_nums IS TABLE OF NUMBER;
  l t_nums := t_nums(1, 2, 3);
  PROCEDURE p (a IN t_nums, b IN OUT NOCOPY t_nums) IS
  BEGIN
    b(1) := 99;
    DBMS_OUTPUT.PUT_LINE('a(1) inside = ' || a(1));    -- 99: a and b are the same memory
  END;
BEGIN
  p(l, l);
END;
/

With by-value b, a(1) would still print 1 inside p. Passing the same variable twice with NOCOPY is legal and surprising — avoid it.

SQL 
-- a function RETURNs by value: the whole collection is copied to the caller
FUNCTION get_vip_ids RETURN t_ids IS ...

-- the procedure form hands back the caller's variable, filled in place
PROCEDURE get_vip_ids (p_ids OUT NOCOPY t_ids) IS
BEGIN
  SELECT customer_id BULK COLLECT INTO p_ids FROM customers WHERE tier = 'VIP';
END;

For a 500K-element result, the function form costs a copy per call; the procedure form does not.

  • Every layer that passes a big collection IN OUT without NOCOPY copies it twice. Three layers deep = six copies of 50K rows per batch. That can exceed the time of the FORALL itself.
  • PGA: a by-value IN OUT parameter temporarily doubles the collection's memory (original + local copy). With 100 MB batches that is 200 MB per call, per session.
  • Records with many columns (%ROWTYPE of a wide table) in IN OUT collections magnify both effects.

  1. NOCOPY on IN — meaningless (already by reference) and a compile error in most versions.
  2. Relying on NOCOPY for correctness — it is a hint; in the cases listed in §2 the compiler copies anyway and your program must still be correct.
  3. Forgetting the exception semantics (3c): partial updates survive a failure.
  4. Aliasing (3d): the same variable passed as two parameters.
  5. NOCOPY on scalars — harmless but pointless; the win is on collections and large records.
  6. Assuming a RETURN of a collection is by reference — it is not; use OUT NOCOPY for large results (3e).