Oracle PL/SQL - Comparing Varray and Nested Table Variables to NULL

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

Introduction

You can compare varray and nested table variables to the value NULL with the "IS [NOT] NULL Operator".

You cannot not use the relational operators equal (=) and not equal (<>, !=, ~=, or ^=).

The following code compares a varray variable and a nested table variable to NULL correctly.

Demo

SQL>
SQL> DECLARE-- www.j a v  a  2s .  c o  m
  2    TYPE StringList IS VARRAY(4) OF VARCHAR2(15);
  3    team StringList;          
  4
  5    TYPE StringList IS TABLE OF VARCHAR2(15);     
  6    names StringList := StringList('A', 'B'); 
  7
  8  BEGIN
  9    IF team IS NULL THEN
 10      DBMS_OUTPUT.PUT_LINE('team IS NULL');
 11    ELSE
 12      DBMS_OUTPUT.PUT_LINE('team IS NOT NULL');
 13    END IF;
 14
 15    IF names IS NOT NULL THEN
 16      DBMS_OUTPUT.PUT_LINE('names IS NOT NULL');
 17    ELSE
 18      DBMS_OUTPUT.PUT_LINE('names IS NULL');
 19    END IF;
 20  END;
 21  /
team IS NULL
names IS NOT NULL

PL/SQL procedure successfully completed.

SQL>

Related Topic