Passing values with NOCOPY (or not)

In programming languages, we can pass parameters by reference and by value to a function. In this recipe, we will see how to make out this difference in PL/SQL functions and procedures.

How to do it...

The following steps will demonstrate passing parameters to functions:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create the type TAB_NUMBERS, which is a table of numbers:
    CREATE OR REPLACE TYPE sh.TAB_NUMBERS AS TABLE OF NUMBER;
    
  3. Create a function called MY_VALUE, which returns an element of an array:
    CREATE OR REPLACE FUNCTION MY_VALUE(ATABLE IN OUT TAB_NUMBERS,
    AIND IN NUMBER) RETURN NUMBER
    IS
    L_VALUE NUMBER := 0;
    BEGIN
    L_VALUE := ATABLE(AIND);
    RETURN L_VALUE;
    END;
    /
    
  4. Create the function MY_VALUE_NOCOPY, which ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.