# 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](../course-plan.md) · Appendix entry: [NOCOPY](../appendix.md#nocopy) · Related: [ASSOCIATIVE ARRAY](associative-array.md), [COLLECTION METHODS](collection-methods.md), [EXCEPTION](exception.md)

---

## 1. What It Is and When

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.

## 2. Syntax

```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.

## 3. Examples on the Course Schema

### 3a. The typical use

```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;
/
```

### 3b. Measuring the difference

```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
```

### 3c. The semantic difference — what the caller sees after an exception

```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.

### 3d. Aliasing — two names for one variable

```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.

### 3e. Large results: OUT NOCOPY procedure instead of a function

```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.

## 4. What Breaks at Scale

- 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.

## 5. Pitfalls

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).

## 6. Related

- [ASSOCIATIVE ARRAY](associative-array.md) — what usually gets passed.
- [COLLECTION METHODS](collection-methods.md) — `DELETE` to release the memory afterwards.
- [EXCEPTION](exception.md) — designing handlers with partial-update semantics in mind.