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

RAISE_APPLICATION_ERROR's Forgotten Parameter

One of my current goals is to re-read the literature I have on PL/SQL and SQL. I'm going back to the documentation and my PL/SQL books to give myself a bit of a refresher course. One of the things I came across today is something I believe is not very well known.

The built in RAISE_APPLICATION_ERROR procedure has a little-known third parameter. 

RAISE_APPLICATION_ERROR ( 
   num            IN BINARY_INTEGER,
   msg            IN VARCHAR2,
   keeperrorstack IN BOOLEAN); 

The documentation states that for the keeperrorstack parameter "If you specify TRUE, PL/SQL puts error_code on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code."

Procedure Dependencies in a Package

I was reviewing some PL/SQL development work recently and I noticed that a developer had declared some procedures in a package specification that should not have been public. When I asked why this was done the reply was that the procedures had dependencies on one another and the package body would not compile otherwise.

Here's a quick demonstration of the issue the developer was encountering.

The Greatest Word In the English Language

The most amazing word in the English language is "Why". Why do I say that? Because it forces someone to back up their claims with evidence.

Do you see what I did there? ;)