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