Oracle PL/SQL - Assigning Null Values to Varray or Nested Table Variables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Varrays (Variable-Size Arrays)

Introduction

To a varray or nested table variable, you can assign the value NULL or a null collection of the same data type.

The assignment makes the variable null.

Demo

SQL>
SQL> DECLARE-- from  w ww. ja v a2 s .c o m
  2    TYPE StringTable IS TABLE OF VARCHAR2(30);
  3
  4    dept_names StringTable := StringTable('A','B','C','D');  -- Initialized to non-null value
  5
  6    empty_set StringTable;  -- Not initialized, null
  7
  8    PROCEDURE print_dept_names_status IS
  9    BEGIN
 10      IF dept_names IS NULL THEN
 11        DBMS_OUTPUT.PUT_LINE('dept_names is null.');
 12      ELSE
 13        DBMS_OUTPUT.PUT_LINE('dept_names is not null.');
 14      END IF;
 15    END  print_dept_names_status;
 16
 17  BEGIN
 18    print_dept_names_status;
 19    dept_names := empty_set;  -- Assign null collection to dept_names.
 20    print_dept_names_status;
 21    dept_names := StringTable ('AA','BB','CC','DD');  -- Re-initialize dept_names
 22    print_dept_names_status;
 23  END;
 24  /
dept_names is not null.
dept_names is null.
dept_names is not null.

PL/SQL procedure successfully completed.

SQL>

Related Topic