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

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-- from   w w w .j  a  v a 2  s.  com
  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