Oracle PL/SQL - Variables Initialized to NULL Values

Introduction

PL/SQL treats any zero-length string as a NULL value.

This includes values returned by character functions and BOOLEAN expressions.

In the following code, all variables are initialized to NULL.

Demo

SQL>
SQL> DECLARE-- from w  w w.ja  v a 2 s.  c  om
  2    null_string  VARCHAR2(80) := TO_CHAR('');
  3    address      VARCHAR2(80);
  4    zip_code     VARCHAR2(80) := SUBSTR(address, 25, 0);
  5    name         VARCHAR2(80);
  6    valid        BOOLEAN      := (name != '');
  7  BEGIN
  8    DBMS_OUTPUT.PUT_LINE(null_string);
  9    DBMS_OUTPUT.PUT_LINE(address);
 10    DBMS_OUTPUT.PUT_LINE(zip_code);
 11    DBMS_OUTPUT.PUT_LINE(name);
 12    DBMS_OUTPUT.PUT_LINE(valid);
 13    NULL;
 14  END;
 15  /
  DBMS_OUTPUT.PUT_LINE(valid);
  *
ERROR at line 12:
ORA-06550: line 12, column 3:
PLS-00306: wrong number or types of arguments in
call to 'PUT_LINE'
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored


SQL>

To test for a NULL value, use the "IS [NOT] NULL Operator".

Related Topic