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 acts as the previous one, but the array parameter is defined as NOCOPY:

   CREATE OR REPLACE FUNCTION MY_VALUE_NOCOPY(
    ATABLE IN OUT NOCOPY TAB_NUMBERS,
    AIND IN NUMBER) RETURN NUMBER
   IS
     L_VALUE NUMBER := 0;
   BEGIN
     L_VALUE := ATABLE(AIND)...