NOT NULL Parameters

Here's a couple of ways to ensure that a parameter being passed into a procedure is NOT NULL.

Method 1: The "IF THEN" Approach
Here we simply check the value of the parameter with an IF statement and raise an error if the value is NULL.

SQL> CREATE OR REPLACE PACKAGE p AS
  2    PROCEDURE p_param_cant_be_null(p_in IN VARCHAR2);
  3  END p;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY p AS
  2    PROCEDURE p_param_cant_be_null(p_in IN VARCHAR2) IS
  3    BEGIN
  4      IF p_in IS NULL THEN
  5        RAISE_APPLICATION_ERROR(-20000,'Parameter p_in cannot be NULL');
  6      END IF;
  7
  8      DBMS_OUTPUT.PUT_LINE('Hello World');
  9    END p_param_cant_be_null;
 10  END;
 11  /

Package body created.

SQL> EXEC p.p_param_cant_be_null(NULL);
BEGIN p.p_param_cant_be_null(NULL); END;

*
ERROR at line 1:
ORA-20000: Parameter p_in cannot be NULL
ORA-06512: at "CHRIS.P", line 5
ORA-06512: at line 1

Method 2: The SUBTYPE Approach
With this method we us the ability to create user-defined SUBTYPES in PL/SQL. By creating a package to manage all of our types we have but a single source which developers need to modify and use. Personally I prefer this method. You declare the type once and use it again and again. It's much less coding and seems a bit more elegant to me.

SQL> CREATE OR REPLACE PACKAGE pkg_types AS
  2    SUBTYPE varchar2_nn IS VARCHAR2 NOT NULL;
  3  END pkg_types;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE p AS
  2    PROCEDURE p_param_cant_be_null(p_in IN pkg_types.varchar2_nn);
  3  END;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY p AS
  2    PROCEDURE p_param_cant_be_null(p_in IN pkg_types.varchar2_nn) IS
  3    BEGIN
  4      DBMS_OUTPUT.PUT_LINE('Hello World');
  5    END p_param_cant_be_null;
  6  END;
  7  /

Package body created.

SQL> EXEC p.p_param_cant_be_null(NULL);
BEGIN p.p_param_cant_be_null(NULL); END;

                             *
ERROR at line 1:
ORA-06550: line 1, column 30:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored